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

Oracle 19c V$ALL_SQL_PLAN 动态性能视图详解

核心作用

V$ALL_SQL_PLAN 是 Oracle 数据库 SQL 性能诊断的关键视图，主要用于：

1. 执行计划分析：提供 SQL 语句的实际执行计划信息
2. 跨实例对比：在 RAC 环境中展示所有实例的执行计划
3. 性能诊断：识别执行计划中的低效操作
4. 优化器验证：比较优化器估算与实际执行差异
5. 历史计划存储：保留已执行 SQL 的计划信息

关键特性

• 全局视图：RAC 环境下聚合所有实例的计划数据
• 完整计划树：展示执行计划的层次结构
• 详细操作信息：包含每个计划步骤的元数据
• 估算与实际对比：显示优化器估算和运行时统计
• 持久存储：计划信息保留到游标失效

---

字段详解（Oracle 19c）

基础标识字段

字段名	数据类型	描述
INST_ID	NUMBER	RAC 实例 ID (仅 GV$SQL_PLAN 有效)
ADDRESS	RAW(8)	父游标地址
HASH_VALUE	NUMBER	SQL 语句的哈希值
SQL_ID	VARCHAR2(13)	SQL 语句的唯一 ID
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值
CHILD_ADDRESS	RAW(8)	子游标地址
CHILD_NUMBER	NUMBER	子游标编号
ID	NUMBER	计划步骤的唯一标识符
PARENT_ID	NUMBER	父步骤 ID
DEPTH	NUMBER	步骤在计划树中的深度
POSITION	NUMBER	兄弟步骤中的执行顺序

操作信息字段

字段名	数据类型	描述
OPERATION	VARCHAR2(30)	执行操作类型 (如: TABLE ACCESS, INDEX SCAN)
OPTIONS	VARCHAR2(30)	操作选项 (如: FULL, RANGE SCAN)
OBJECT_OWNER	VARCHAR2(30)	对象所有者
OBJECT_NAME	VARCHAR2(30)	对象名称
OBJECT_TYPE	VARCHAR2(20)	对象类型 (如: TABLE, INDEX)
OBJECT_ALIAS	VARCHAR2(65)	对象别名
OPTIMIZER	VARCHAR2(20)	优化器模式 (如: ALL_ROWS, FIRST_ROWS)

统计估算字段

字段名	数据类型	描述
CARDINALITY	NUMBER	优化器估算的返回行数
BYTES	NUMBER	优化器估算的字节数
COST	NUMBER	优化器估算的成本值
CPU_COST	NUMBER	优化器估算的 CPU 成本
IO_COST	NUMBER	优化器估算的 I/O 成本
TIME	NUMBER	优化器估算的执行时间 (秒)

分区与访问字段

字段名	数据类型	描述
PARTITION_START	VARCHAR2(64)	分区扫描起始位置
PARTITION_STOP	VARCHAR2(64)	分区扫描结束位置
PARTITION_ID	NUMBER	分区 ID
ACCESS_PREDICATES	VARCHAR2(4000)	访问谓词 (索引/分区访问条件)
FILTER_PREDICATES	VARCHAR2(4000)	过滤谓词 (结果集过滤条件)

其他关键字段

字段名	数据类型	描述
PROJECTION	VARCHAR2(4000)	该步骤返回的列
OTHER	VARCHAR2(4000)	其他执行信息
OTHER_XML	CLOB	XML 格式的附加信息
REMARKS	VARCHAR2(4000)	备注信息
TIMESTAMP	DATE	计划生成时间

---

基表与底层原理

底层结构：X$KGLCURSOR_CHILD_SQLID（共享游标内部表）

数据来源：

1. 共享池中的游标信息
2. 优化器生成的执行计划
3. SQL 执行时的运行时统计

工作原理：

1. SQL 解析时生成执行计划
2. 计划信息存储在共享池的游标中
3. 执行时收集实际运行统计
4. V$SQL_PLAN 提供内存中计划的访问
5. GV$SQL_PLAN 聚合 RAC 所有实例的计划
6. 计划信息保留到游标被 aged out

计划生命周期：

---

核心使用场景

1. 执行计划查看

SELECT * 
FROM gv$sql_plan 
WHERE sql_id = 'g8x9j5s2a1b3c';

2. 计划对比分析

SELECT id, operation, options, object_name
FROM gv$sql_plan
WHERE sql_id = 'g8x9j5s2a1b3c'
AND inst_id = 1
MINUS
SELECT id, operation, options, object_name
FROM gv$sql_plan
WHERE sql_id = 'g8x9j5s2a1b3c'
AND inst_id = 2;

3. 成本估算验证

SELECT operation, cardinality AS estimated, 
       (SELECT output_rows 
        FROM v$sql_plan_statistics_all s 
        WHERE s.sql_id = p.sql_id 
          AND s.child_number = p.child_number 
          AND s.id = p.id) AS actual
FROM v$sql_plan p
WHERE sql_id = 'g8x9j5s2a1b3c';

4. 全表扫描识别

SELECT sql_id, object_name
FROM gv$sql_plan
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL';

5. 索引使用分析

SELECT sql_id, object_name
FROM gv$sql_plan
WHERE operation LIKE '%INDEX%';

---

常用查询 SQL 示例

1. 完整执行计划树

SELECT 
  inst_id,
  id,
  LPAD(' ', depth*2) || operation || ' ' || options AS operation,
  object_name,
  cardinality,
  bytes,
  cost
FROM gv$sql_plan
WHERE sql_id = '&sql_id'
  AND child_number = &child_num
CONNECT BY PRIOR id = parent_id
START WITH id = 0
ORDER BY id;

2. 计划步骤详情

SELECT 
  id,
  operation,
  options,
  object_name,
  cardinality AS est_rows,
  bytes AS est_bytes,
  cost AS est_cost,
  access_predicates,
  filter_predicates
FROM gv$sql_plan
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND inst_id = 1
ORDER BY id;

3. RAC 计划差异检测

SELECT 
  p1.inst_id AS inst1,
  p1.operation || ' ' || p1.options AS plan1,
  p2.inst_id AS inst2,
  p2.operation || ' ' || p2.options AS plan2
FROM gv$sql_plan p1
JOIN gv$sql_plan p2 
  ON p1.sql_id = p2.sql_id 
  AND p1.id = p2.id
WHERE p1.sql_id = 'g8x9j5s2a1b3c'
  AND p1.inst_id = 1
  AND p2.inst_id = 2
  AND (p1.operation <> p2.operation OR p1.options <> p2.options);

4. 高成本操作识别

SELECT 
  sql_id,
  operation,
  options,
  object_name,
  cost,
  cardinality
FROM gv$sql_plan
WHERE cost > 10000
ORDER BY cost DESC
FETCH FIRST 10 ROWS ONLY;

5. 分区访问分析

SELECT 
  sql_id,
  operation,
  object_name,
  partition_start,
  partition_stop,
  partition_id
FROM gv$sql_plan
WHERE partition_start IS NOT NULL;

6. 谓词条件分析

SELECT 
  sql_id,
  id,
  operation,
  access_predicates,
  filter_predicates
FROM gv$sql_plan
WHERE access_predicates IS NOT NULL 
   OR filter_predicates IS NOT NULL;

---

执行计划操作类型参考

常见操作类型

操作类型	描述
SELECT STATEMENT	SQL 语句的根操作
TABLE ACCESS	表访问 (FULL, BY ROWID 等)
INDEX	索引操作 (SCAN, RANGE SCAN)
NESTED LOOPS	嵌套循环连接
HASH JOIN	哈希连接
SORT	排序操作 (ORDER BY, GROUP BY)
VIEW	视图访问
UNION	联合操作

特殊操作类型

操作类型	描述
PX	并行执行操作
MAT_VIEW	物化视图访问
WINDOW	窗口函数操作
CONNECT BY	层次查询操作

---

执行计划分析技巧

1. 识别全表扫描

SELECT sql_id, object_name
FROM gv$sql_plan
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_owner NOT IN ('SYS','SYSTEM');

2. 检测缺失索引

SELECT 
  sql_id,
  object_name,
  filter_predicates
FROM gv$sql_plan
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND filter_predicates LIKE '%= %';

3. 分析连接顺序

SELECT 
  id,
  LPAD(' ', depth) || operation AS operation,
  object_name
FROM v$sql_plan
WHERE sql_id = 'g8x9j5s2a1b3c'
ORDER BY id;

4. 验证分区修剪

SELECT 
  partition_start, 
  partition_stop,
  COUNT(*) AS plan_count
FROM gv$sql_plan
WHERE object_name = 'SALES'
  AND partition_start IS NOT NULL
GROUP BY partition_start, partition_stop;

---

执行计划生成方法

1. 使用 EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 自动追踪

ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  format => 'ALLSTATS LAST'));

3. SQL 监控

SELECT /*+ MONITOR */ *
FROM large_table WHERE condition = 'value';

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'g8x9j5s2a1b3c',
  type => 'ACTIVE') 
FROM dual;

---

重要注意事项

1. 权限要求：

   • SELECT_CATALOG_ROLE
   • SELECT ANY DICTIONARY
   • 访问基表的权限

2. 数据保留：

   • 计划信息保留到游标失效
   • 历史计划存储在 AWR 中

   SELECT * FROM dba_hist_sql_plan;
   

3. 与相关视图对比：

   特性	V$ALL_SQL_PLAN	DBA_HIST_SQL_PLAN	V$SQL_PLAN_STATISTICS
   数据源	内存游标	AWR 快照	运行时统计
   实时性	实时	历史	实时
   RAC 支持	是 (GV$)	是	是
   统计信息	估算值	估算值	实际值

4. 性能影响：

   • 查询 GV$ 视图会增加集群通信开销
   • 建议使用过滤条件
   • 避免在高峰时段查询大结果集

5. 计划稳定性：

   • 使用 SQL Plan Baselines 稳定计划

   SELECT * FROM dba_sql_plan_baselines;
   

6. 诊断工具：

   -- 生成文本格式计划
   @?/rdbms/admin/utlxpls.sql
   
   -- 生成详细计划报告
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
     'PLAN_TABLE', NULL, 'ADVANCED'));
   

最佳实践

1. 计划捕获脚本

DECLARE
  l_plan PLS_INTEGER;
BEGIN
  l_plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'g8x9j5s2a1b3c',
    plan_hash_value => 123456789);
END;
/

2. 计划对比工具

SELECT * 
FROM TABLE(DBMS_XPLAN.COMPARE_PLANS(
  comparison_type => 'TEXT',
  reference_plan => 
    CURSOR(SELECT plan_table_output 
           FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','plan1','BASIC'))),
  compare_plan => 
    CURSOR(SELECT plan_table_output 
           FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','plan2','BASIC'))));

3. 计划分析自动化

BEGIN
  DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE(
    sql_text => 'SELECT * FROM employees WHERE department_id = :dept_id',
    plan_hash_value => 135792468);
END;
/

通过 V$ALL_SQL_PLAN，DBA 可以深入分析 SQL 执行计划，识别性能瓶颈，验证优化器决策，并在 RAC 环境中确保执行计划的一致性。结合执行统计和 AWR 历史数据，可构建全面的 SQL 性能监控体系。

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