前提

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

操作流程

  1. 查看 CONTROL_MANAGEMENT_PACK_ACCESS、STATISTICS_LEVEL 参数

    1
    2
    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

    1
    alter system set control_management_pack_access="DIAGNOSTIC+TUNING";

  2. 执行测试 SQL

    1
    select count(1) from emp;

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

    1
    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 缓冲池

    1
    2
    3
    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’)查看执行计划

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

优缺点

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