面试宝典:介绍下Oracle数据库动态性能视图 V$SQL_CS_STATISTICS

Oracle 19C V$SQL_CS_STATISTICS 动态性能视图全面详解

1. 视图概述与核心作用

V$SQL_CS_STATISTICS 是 Oracle 19C 中用于监控自适应游标共享（Adaptive Cursor Sharing, ACS）统计信息的动态性能视图。它提供了SQL语句在执行过程中收集的详细统计信息，这些信息被优化器用来支持自适应游标共享功能，特别是用于决定是否为不同的绑定变量值选择不同的执行计划。

核心作用：

• 存储和管理自适应游标共享的运行时统计信息
• 提供SQL语句执行过程中的详细性能指标
• 支持优化器进行执行计划选择决策
• 帮助诊断执行计划性能问题
• 提供绑定变量感知的性能统计

2. 主要使用场景

1. ACS性能分析：分析自适应游标共享的统计信息和性能特征
2. 执行计划诊断：诊断不同执行计划的性能差异
3. 绑定变量分析：分析绑定变量值对执行性能的影响
4. 性能优化：基于统计信息优化SQL性能
5. 系统监控：监控ACS功能的运行状况和效果
6. 问题排查：排查执行计划选择相关的性能问题

3. 字段详解

以下是 V$SQL_CS_STATISTICS 视图的主要字段及其详细说明：

字段名	数据类型	含义说明	重要程度
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符	高
CHILD_NUMBER	NUMBER	子游标编号	高
BIND_SET_HASH_VALUE	NUMBER	绑定变量集的哈希值	高
EXECUTIONS	NUMBER	执行次数	高
ROWS_PROCESSED	NUMBER	处理的行数	高
BUFFER_GETS	NUMBER	缓冲区获取次数	高
CPU_TIME	NUMBER	CPU时间（微秒）	高
ELAPSED_TIME	NUMBER	总耗时（微秒）	高
DISK_READS	NUMBER	磁盘读取次数	高
DIRECT_WRITES	NUMBER	直接写入次数	中
APPLICATION_WAIT_TIME	NUMBER	应用等待时间	中
CONCURRENCY_WAIT_TIME	NUMBER	并发等待时间	中
CLUSTER_WAIT_TIME	NUMBER	集群等待时间	中
USER_IO_WAIT_TIME	NUMBER	用户I/O等待时间	中
PLSQL_EXEC_TIME	NUMBER	PL/SQL执行时间	中
JAVA_EXEC_TIME	NUMBER	Java执行时间	中
END_OF_FETCH_COUNT	NUMBER	完成获取的次数	中
LAST_ACTIVE_TIME	DATE	最后活动时间	高
CON_ID	NUMBER	容器ID（多租户环境）	中

4. 相关视图与基表

相关视图：

1. V$SQL_CS_HISTOGRAM：ACS执行统计直方图信息
2. V$SQL_CS_SELECTIVITY：ACS选择性信息
3. V$SQL：SQL执行统计信息
4. V$SQL_BIND_CAPTURE：绑定变量捕获信息
5. V$SQLAREA：SQL区域的共享游标统计信息
6. V$SQL_PLAN：SQL执行计划信息
7. GV$SQL_CS_STATISTICS：集群环境下所有实例的ACS统计信息

基表：

V$SQL_CS_STATISTICS 基于内存中的X$表实现，主要是：

• X$KQLFSQCS：SQL执行统计的内部表
• X$KGLCURSOR：游标信息的内部表
• X$KQLFSPC：SQL性能统计的内部表

这些X$表是Oracle内部数据结构的外部化表示，存储在SGA的共享池中。

5. 底层原理与内部机制

自适应游标共享统计原理：

1. 统计收集：在SQL执行过程中收集详细的性能统计信息
2. 绑定变量关联：将统计信息与特定的绑定变量集关联
3. 决策支持：为优化器提供数据支持执行计划选择决策
4. 性能分析：基于统计信息分析不同执行计划的性能特征

统计收集机制：

1. 执行时收集：在SQL语句执行过程中实时收集统计信息
2. 绑定变量感知：统计信息与特定的绑定变量值关联
3. 内存存储：统计信息存储在SGA的共享池中
4. 实时更新：每次执行后更新相关统计信息

数据管理机制：

1. 哈希标识：使用BIND_SET_HASH_VALUE唯一标识绑定变量集
2. 统计聚合：对相同绑定变量集的执行统计进行聚合
3. 内存优化：使用优化的存储格式减少内存开销
4. 老化机制：统计信息随游标的老化而从共享池中清除

ACS决策过程：

1. 统计比较：比较不同绑定变量集的执行统计信息
2. 计划选择：基于统计信息选择最合适的执行计划
3. 性能评估：评估不同执行计划的性能特征
4. 自适应调整：根据统计反馈自适应调整执行计划选择

6. 常用查询SQL

查询1：查看SQL语句的ACS统计信息

SELECT sql_id, child_number, bind_set_hash_value,
       executions, rows_processed, buffer_gets,
       cpu_time/1000000 cpu_seconds,
       elapsed_time/1000000 elapsed_seconds
FROM v$sql_cs_statistics
WHERE sql_id = '&sql_id'
ORDER BY bind_set_hash_value;

查询2：分析不同绑定变量集的性能差异

SELECT sql_id, bind_set_hash_value,
       SUM(executions) total_executions,
       SUM(rows_processed) total_rows,
       SUM(buffer_gets) total_buffer_gets,
       SUM(cpu_time)/1000000 total_cpu_seconds,
       SUM(elapsed_time)/1000000 total_elapsed_seconds
FROM v$sql_cs_statistics
WHERE sql_id = '&sql_id'
GROUP BY sql_id, bind_set_hash_value
ORDER BY total_elapsed_seconds DESC;

查询3：查找性能差异较大的绑定变量集

SELECT sql_id,
       MIN(elapsed_time/executions/1000) min_avg_ms,
       MAX(elapsed_time/executions/1000) max_avg_ms,
       MAX(elapsed_time/executions/1000) - MIN(elapsed_time/executions/1000) range_ms
FROM v$sql_cs_statistics
WHERE executions > 0
GROUP BY sql_id
HAVING MAX(elapsed_time/executions/1000) > 2 * MIN(elapsed_time/executions/1000)
ORDER BY range_ms DESC;

查询4：监控ACS统计信息的变化

SELECT sql_id, bind_set_hash_value,
       TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI') last_active,
       executions, rows_processed,
       ROUND(buffer_gets/NULLIF(executions,0)) avg_buffer_gets,
       ROUND(elapsed_time/executions/1000) avg_elapsed_ms
FROM v$sql_cs_statistics
WHERE last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC;

查询5：分析资源消耗模式

SELECT sql_id, bind_set_hash_value,
       ROUND(buffer_gets/NULLIF(rows_processed,0)) buffer_per_row,
       ROUND(cpu_time/NULLIF(rows_processed,0)/1000) cpu_per_row,
       ROUND(elapsed_time/NULLIF(rows_processed,0)/1000) elapsed_per_row
FROM v$sql_cs_statistics
WHERE rows_processed > 1000
ORDER BY buffer_per_row DESC;

查询6：多租户环境下的ACS统计分析

SELECT con_id, sql_id, bind_set_hash_value,
       SUM(executions) total_executions,
       SUM(elapsed_time)/1000000 total_elapsed_seconds,
       SUM(buffer_gets) total_buffer_gets
FROM gv$sql_cs_statistics
WHERE con_id = &container_id
GROUP BY con_id, sql_id, bind_set_hash_value
ORDER BY total_elapsed_seconds DESC;

查询7：关联统计信息与绑定变量值

SELECT s.sql_id, s.bind_set_hash_value,
       b.bind_name, b.value_string,
       s.executions, s.rows_processed,
       s.elapsed_time/s.executions/1000 avg_elapsed_ms
FROM v$sql_cs_statistics s
JOIN v$sql_bind_capture b ON s.sql_id = b.sql_id
WHERE s.sql_id = '&sql_id'
AND s.executions > 0
ORDER BY s.bind_set_hash_value, b.position;

查询8：查找需要关注的性能模式

SELECT sql_id, bind_set_hash_value,
       executions,
       ROUND(buffer_gets/executions) avg_buffer_gets,
       ROUND(rows_processed/executions) avg_rows,
       ROUND(elapsed_time/executions/1000) avg_elapsed_ms,
       CASE 
           WHEN elapsed_time/executions/1000 > 1000 THEN 'CRITICAL'
           WHEN elapsed_time/executions/1000 > 100 THEN 'WARNING'
           ELSE 'NORMAL'
       END as performance_level
FROM v$sql_cs_statistics
WHERE executions > 10
ORDER BY avg_elapsed_ms DESC;

7. 关键知识点与注意事项

重要知识点：

1. ACS统计：提供自适应游标共享的详细执行统计信息
2. 绑定变量关联：统计信息与特定的绑定变量集关联
3. 性能分析：基于统计信息分析不同执行计划的性能特征
4. 决策支持：为优化器提供执行计划选择的数据支持
5. 多维度统计：提供CPU、I/O、时间等多维度性能指标

诊断技巧：

1. 性能差异分析：分析不同绑定变量集的性能差异
2. 资源消耗分析：分析SQL语句的资源消耗模式
3. 趋势分析：监控统计信息随时间的变化趋势
4. 关联分析：将统计信息与绑定变量值关联分析
5. 异常检测：检测性能异常的执行模式

最佳实践：

1. 定期监控：定期检查重要SQL的ACS统计信息
2. 性能基线：为关键SQL建立性能基线
3. 异常预警：设置性能异常的预警阈值
4. 关联分析：结合其他ACS视图进行综合分析
5. 优化决策：基于统计信息优化SQL性能

注意事项：

1. 数据生命周期：统计信息随游标老化而消失
2. 统计精度：统计信息是近似值，可能有轻微误差
3. 权限要求：需要SELECT ANY DICTIONARY或相应权限
4. 性能影响：频繁查询可能对系统性能产生影响
5. 版本差异：统计信息和行为可能在不同Oracle版本中有差异

相关初始化参数：

1. OPTIMIZER_FEATURES_ENABLE：控制优化器特性
2. _OPTIMIZER_EXTENDED_CURSOR_SHARING：扩展游标共享
3. _OPTIMIZER_ADAPTIVE_CURSOR_SHARING：自适应游标共享
4. CURSOR_SHARING：游标共享行为控制

通过深入理解和使用 V$SQL_CS_STATISTICS 视图，DBA和开发人员可以更好地分析自适应游标共享的行为，诊断执行计划性能问题，优化SQL性能，提高数据库系统的稳定性和性能表现。

欢迎关注我的公众号《IT小Chen》