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

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 584 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

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

1. 视图概述与核心作用

V$SQL_CS_HISTOGRAM 是 Oracle 19C 中用于监控自适应游标共享（Adaptive Cursor Sharing, ACS）行为的动态性能视图。它提供了关于SQL语句执行计划选择情况的统计信息，特别是对于使用绑定变量的SQL语句，Oracle会记录不同执行计划的执行次数分布情况。

核心作用：

监控自适应游标共享（ACS）的行为和效果
显示每个SQL语句不同执行计划的选择分布情况
帮助诊断因绑定变量值变化导致的执行计划不稳定问题
提供SQL语句执行计划的统计直方图信息
支持绑定变量感知的性能分析和优化

2. 主要使用场景

自适应游标共享监控：监控ACS功能的行为和效果
执行计划分析：分析SQL语句不同执行计划的使用分布
性能问题诊断：诊断因执行计划变化导致的性能问题
绑定变量分析：分析绑定变量值对执行计划选择的影响
系统调优：评估和优化ACS相关的参数设置
容量规划：分析执行计划选择的分布特征

3. 字段详解

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

字段名	数据类型	含义说明	重要程度
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符	高
BUCKET_ID	NUMBER	直方图桶的ID（0-2）	高
COUNT	NUMBER	该桶中的执行次数统计	高
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值	高
CON_ID	NUMBER	容器ID（多租户环境）	中

4. 相关视图与基表

基表：

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

X$KQLFSQCE：SQL执行统计直方图的内部表
X$KGLCURSOR：游标信息的内部表
X$KQLFSQC：SQL执行统计的内部表

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

5. 底层原理与内部机制

自适应游标共享（ACS）原理：

绑定变量感知：ACS使优化器能够感知绑定变量值的分布特征
多计划支持：为同一SQL语句维护多个执行计划
选择性切换：根据绑定变量值选择最合适的执行计划
统计收集：记录不同执行计划的使用情况

直方图桶含义：

BUCKET_ID 的三个值有特殊含义：

0：表示最优执行计划（选择性最好的计划）
1：表示单次执行计划（只执行过一次的计划）
2：表示非最优执行计划（选择性较差的计划）

数据收集机制：

执行统计：每次SQL执行时，Oracle记录执行计划的选择情况
桶分类：根据执行计划的选择性将执行次数分类到不同的桶中
内存存储：统计信息存储在SGA的共享池中
实时更新：执行统计信息实时更新

内存管理：

共享池存储：直方图统计信息存储在SGA的共享池中
游标关联：统计信息与特定SQL游标关联
内存优化：使用优化的存储格式减少内存开销
数据持久性：统计信息随游标的老化而从共享池中清除

6. 常用查询SQL

查询1：查看SQL语句的ACS直方图信息

SELECT sql_id, bucket_id, count, plan_hash_value
FROM v$sql_cs_histogram
WHERE sql_id = '&sql_id'
ORDER BY bucket_id;

查询2：分析ACS行为统计

SELECT sql_id,
       SUM(CASE WHEN bucket_id = 0 THEN count ELSE 0 END) optimal_plan,
       SUM(CASE WHEN bucket_id = 1 THEN count ELSE 0 END) single_exec,
       SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) non_optimal,
       SUM(count) total_executions
FROM v$sql_cs_histogram
GROUP BY sql_id
HAVING SUM(count) > 100
ORDER BY non_optimal DESC;

查询3：查找可能有问题的SQL语句

SELECT sql_id,
       SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) non_optimal_count,
       SUM(count) total_executions,
       ROUND(SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) * 100 / SUM(count), 2) non_optimal_pct
FROM v$sql_cs_histogram
GROUP BY sql_id
HAVING SUM(count) > 50
   AND SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) > SUM(count) * 0.1
ORDER BY non_optimal_pct DESC;

查询4：监控ACS效果

SELECT sql_id, plan_hash_value,
       SUM(CASE WHEN bucket_id = 0 THEN count ELSE 0 END) optimal_execs,
       SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) non_optimal_execs
FROM v$sql_cs_histogram
GROUP BY sql_id, plan_hash_value
HAVING SUM(CASE WHEN bucket_id = 2 THEN count ELSE 0 END) > 0
ORDER BY non_optimal_execs DESC;

查询5：分析执行计划分布

SELECT h.sql_id, h.plan_hash_value,
       s.sql_text,
       SUM(CASE WHEN h.bucket_id = 0 THEN h.count ELSE 0 END) optimal_count,
       SUM(CASE WHEN h.bucket_id = 2 THEN h.count ELSE 0 END) non_optimal_count
FROM v$sql_cs_histogram h
JOIN v$sql s ON h.sql_id = s.sql_id
WHERE ROWNUM <= 10
GROUP BY h.sql_id, h.plan_hash_value, s.sql_text
ORDER BY non_optimal_count DESC;

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

SELECT con_id, sql_id, bucket_id, 
       SUM(count) total_count,
       COUNT(DISTINCT plan_hash_value) plan_count
FROM gv$sql_cs_histogram
WHERE con_id = &container_id
GROUP BY con_id, sql_id, bucket_id
ORDER BY total_count DESC;

查询7：查找需要多个执行计划的SQL

SELECT sql_id,
       COUNT(DISTINCT plan_hash_value) distinct_plans,
       SUM(count) total_executions,
       MIN(bucket_id) min_bucket,
       MAX(bucket_id) max_bucket
FROM v$sql_cs_histogram
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
   AND SUM(count) > 100
ORDER BY distinct_plans DESC, total_executions DESC;