获取执行计划之dbms_xplan.display_awr()

前提

目标SQL的执行计划被Oracle采集到AWR Reponsitory中

操作流程

1. 查看CONTROL_MANAGEMENT_PACK_ACCESS、STATISTICS_LEVEL参数

SHOW PARAMETER control_management_pack_access
show parameter STATISTICS_LEVEL

以上两个参数用于控制诊断和调优包的使用
CONTROL_MANAGEMENT_PACK_ACCESS 应该被设置为DIAGNOSTIC+TUNING诊断和调优模式
DIAGNOSTIC为确保启用自动数据库诊断监视器，包括AWR、ADDM
TUNING显示一sql推断信息和调优建议，包括SQL Tuning Advisor,、SQLAccess Advisor

alter system set control_management_pack_access="DIAGNOSTIC+TUNING";

2. 执行测试SQL

select count(1) from emp;

3. 查询Shared Pool中是否已经缓存了select count(1) from emp的执行计划

select t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS from v$sqlarea t where t.SQL_TEXT like '%select count(1) from emp%';

4. 手工收集AWR报告，清空Shared Pool缓冲池

exec dbms_workload_repository.create_snapshot();
alter system flush shared_pool;
select t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS from v$sqlarea t where t.SQL_TEXT like '%select count(1) from emp%';

5. 使用dbms_xplan.display_awr(‘sql_id’)查看执行计划

select * from table(dbms_xplan.display_awr('9r5a71wx8rpr1'));

优缺点

• 优点
  可以得到真实的执行计划
• 缺点
  该方法不能显示谓词信息，是因为从V$SQL_PLAN导入AWR基表WRH$_SQL_PLAN时未将谓词字段access_predicates和filter_predicates导入