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

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

核心作用

V$ADVISOR_CURRENT_SQLPLAN 是 SQL 调优顾问（SQL Tuning Advisor）的核心组件，主要用于：

1. 实时展示SQL调优分析：显示当前SQL调优任务生成的执行计划
2. 对比优化前后计划：并行展示原始执行计划和顾问建议的执行计划
3. 诊断性能差异：识别计划变化对性能的影响
4. 验证优化建议：预览优化器如何执行建议后的SQL
5. 减少测试风险：无需实际执行即可评估计划变化

📌 注意：此视图仅当SQL调优顾问运行时填充数据，任务完成后数据消失

关键特性

• 临时存储：仅存在于SQL调优顾问任务执行期间
• 双计划对比：同时显示原始计划和顾问建议的计划
• 详细操作信息：提供完整的执行计划步骤细节
• 优化器模拟：展示优化器如何处理调优建议
• 零执行开销：不实际执行SQL即可获取计划信息

---

字段详解（Oracle 19c）

基础标识字段

字段名	数据类型	描述
TASK_ID	NUMBER	SQL调优任务的唯一标识符
TASK_EXEC_NAME	VARCHAR2(30)	调优任务执行名称
SQL_ID	VARCHAR2(13)	被分析的SQL语句ID
PLAN_ID	NUMBER	执行计划的唯一标识符
PLAN_TYPE	VARCHAR2(10)	计划类型： - ORIGINAL：原始计划 - ADVISED：顾问建议计划

计划结构字段

字段名	数据类型	描述
ID	NUMBER	执行计划步骤ID（树状结构中的节点）
PARENT_ID	NUMBER	父步骤ID
DEPTH	NUMBER	步骤深度（根节点为0）
OPERATION	VARCHAR2(30)	操作类型（如：INDEX SCAN, HASH JOIN）
OPTIONS	VARCHAR2(30)	操作选项（如：FULL, RANGE SCAN）
OBJECT_OWNER	VARCHAR2(30)	对象所有者
OBJECT_NAME	VARCHAR2(30)	对象名称（表/索引名）
OBJECT_TYPE	VARCHAR2(30)	对象类型（如：TABLE, INDEX）
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)	分区结束位置
ACCESS_PREDICATES	VARCHAR2(4000)	访问谓词（WHERE子句中用于访问的过滤条件）
FILTER_PREDICATES	VARCHAR2(4000)	过滤谓词（结果集过滤条件）

---

基表与底层原理

底层结构：X$ADV_SQLPLAN（SQL调优顾问内部结构）

数据来源：

1. SQL调优顾问分析引擎
2. 优化器成本计算模块
3. 执行计划生成器

工作原理：

1. SQL调优顾问接收SQL语句
2. 分析器生成原始执行计划
3. 优化引擎应用建议（如索引、统计信息更新等）
4. 生成建议后的执行计划
5. 两种计划同时存储于内存结构
6. V$ADVISOR_CURRENT_SQLPLAN 提供这些计划的可读视图

数据生命周期：

• 任务启动时创建
• 任务执行期间存在
• 任务完成后自动清除

---

核心使用场景

1. 实时调优监控

SELECT plan_type, operation, options, object_name, cost
FROM v$advisor_current_sqlplan
WHERE task_id = 12345;

2. 计划变更对比

SELECT o.id, 
       o.operation || ' ' || o.options AS original_plan,
       a.operation || ' ' || a.options AS advised_plan,
       o.cost AS original_cost,
       a.cost AS advised_cost
FROM v$advisor_current_sqlplan o
JOIN v$advisor_current_sqlplan a 
  ON o.task_id = a.task_id AND o.id = a.id
WHERE o.task_id = 12345
  AND o.plan_type = 'ORIGINAL'
  AND a.plan_type = 'ADVISED'
ORDER BY o.id;

3. 调优效果预估

SELECT 
  SUM(o.cost) AS original_total_cost,
  SUM(a.cost) AS advised_total_cost,
  ROUND((SUM(o.cost) - SUM(a.cost)) / SUM(o.cost) * 100, 2) AS improvement_pct
FROM v$advisor_current_sqlplan o
JOIN v$advisor_current_sqlplan a 
  ON o.task_id = a.task_id AND o.id = a.id
WHERE o.task_id = 12345
  AND o.plan_type = 'ORIGINAL'
  AND a.plan_type = 'ADVISED';

4. 索引建议验证

SELECT operation, options, object_name
FROM v$advisor_current_sqlplan
WHERE task_id = 12345
  AND plan_type = 'ADVISED'
  AND operation LIKE '%INDEX%';

---

常用查询 SQL 示例

1. 基础计划对比

SELECT 
  o.id,
  o.operation || ' ' || o.options AS original_operation,
  o.object_name AS original_object,
  o.cost AS original_cost,
  a.operation || ' ' || a.options AS advised_operation,
  a.object_name AS advised_object,
  a.cost AS advised_cost
FROM v$advisor_current_sqlplan o
JOIN v$advisor_current_sqlplan a 
  ON o.task_id = a.task_id AND o.id = a.id
WHERE o.task_id = &task_id
  AND o.plan_type = 'ORIGINAL'
  AND a.plan_type = 'ADVISED'
ORDER BY o.id;

2. 成本差异分析

SELECT 
  o.id,
  o.operation,
  o.cost - a.cost AS cost_reduction,
  ROUND((o.cost - a.cost) / o.cost * 100, 2) AS reduction_pct
FROM v$advisor_current_sqlplan o
JOIN v$advisor_current_sqlplan a 
  ON o.task_id = a.task_id AND o.id = a.id
WHERE o.task_id = 12345
  AND o.plan_type = 'ORIGINAL'
  AND a.plan_type = 'ADVISED'
  AND o.cost > a.cost
ORDER BY cost_reduction DESC;

3. 计划树形展示

SELECT 
  LPAD(' ', 2*depth) || operation || ' ' || options AS operation_tree,
  object_name,
  cardinality,
  bytes,
  cost
FROM v$advisor_current_sqlplan
WHERE task_id = 12345
  AND plan_type = 'ADVISED'
CONNECT BY PRIOR id = parent_id
START WITH parent_id IS NULL;

4. 谓词分析

SELECT 
  id,
  operation,
  access_predicates,
  filter_predicates
FROM v$advisor_current_sqlplan
WHERE task_id = 12345
  AND plan_type = 'ADVISED'
  AND (access_predicates IS NOT NULL OR filter_predicates IS NOT NULL);

5. 对象访问变化

SELECT 
  o.object_name,
  o.operation AS original_access,
  a.operation AS advised_access
FROM v$advisor_current_sqlplan o
JOIN v$advisor_current_sqlplan a 
  ON o.task_id = a.task_id AND o.id = a.id
WHERE o.task_id = 12345
  AND o.plan_type = 'ORIGINAL'
  AND a.plan_type = 'ADVISED'
  AND o.object_name IS NOT NULL;

---

SQL调优顾问操作示例

1. 创建调优任务

DECLARE
  l_task_name VARCHAR2(30);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'g8x9j5s2a1b3c',
    scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit => 300,
    task_name => 'TUNE_SQL_g8x9j5s2a1b3c',
    description => 'Tuning task for problematic SQL');
END;
/

2. 执行调优任务

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_SQL_g8x9j5s2a1b3c');
END;
/

3. 查询任务状态

SELECT task_name, status 
FROM USER_ADVISOR_TASKS 
WHERE task_name = 'TUNE_SQL_g8x9j5s2a1b3c';

4. 查看建议计划（任务执行期间）

SELECT * 
FROM V$ADVISOR_CURRENT_SQLPLAN
WHERE task_id = (
  SELECT task_id 
  FROM USER_ADVISOR_TASKS 
  WHERE task_name = 'TUNE_SQL_g8x9j5s2a1b3c'
);

5. 获取最终建议报告

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SQL_g8x9j5s2a1b3c') 
FROM DUAL;

---

优化器行为解析

计划生成原理

典型优化建议类型

1. 索引变更：

   • 创建新索引
   • 修改索引类型
   • 删除冗余索引

2. 统计信息：

   • 刷新过时统计信息
   • 调整采样率
   • 创建扩展统计

3. SQL重写：

   • 查询转换
   • 消除冗余操作
   • 优化连接顺序

4. 参数调整：

   • 优化器参数
   • 并行执行设置
   • 缓存配置

---

重要注意事项

1. 权限要求：

   • SELECT_CATALOG_ROLE
   • ADVISOR 权限
   • EXECUTE ON DBMS_SQLTUNE

2. 数据时效性：

   • 仅任务执行期间有效
   • 任务完成自动清除
   • 无法查询历史计划

3. 与执行计划视图区别：

   特性	V$ADVISOR_CURRENT_SQLPLAN	V$SQL_PLAN
   数据来源	SQL调优顾问	实际执行
   计划类型	原始+建议	实际执行计划
   持久性	临时（任务期间）	持久（直到SQL老化）
   执行开销	无	需要实际执行

4. 调优任务限制：

   • 并行任务数量有限
   • 大型SQL可能超时
   • 复杂依赖可能分析失败

5. 最佳实践：

   -- 监控任务执行进度
   SELECT sofar, totalwork 
   FROM V$ADVISOR_PROGRESS 
   WHERE task_id = 12345;
   
   -- 自动捕获长期任务
   BEGIN
     DBMS_SQLTUNE.INTERRUPT_TUNING_TASK('TUNE_SQL_g8x9j5s2a1b3c');
   END;
   /
   

通过 V$ADVISOR_CURRENT_SQLPLAN，DBA 可以在不实际执行 SQL 的情况下，深入分析 SQL 调优顾问的建议效果，验证优化方案的有效性，并预测性能改进潜力。结合 SQL 调优顾问的其他功能，可显著提升数据库性能优化效率。

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