Oracle AWR 闕值影響曆史執行計畫

最近有網友提到為什麼在dba_hist_sql_plan中無法查看到sql語句的曆史執行計畫，對於這個問題是由於預設情況下，Oracle 設定的闕值並非捕獲所有的sql語句，所以無法看到某些sql曆史執行計畫乃正常現象。在Oracle 9i的時候，我們可以通過設定不同的快照level獲得不同程度的詳細資料。也可以單獨配置收集sql的闕值，如指定sql的執行次數，磁碟讀的次數，解析調用的數量等。所有超出這個設定的sql語句都收集到snapshot之中。Oracle 10g，11g也有相應的設定。下面來描述這個問題。

1、預設闕值的情形

--環境，下面的示範基於Oracle 10g
scott@CNMMBO> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--下面的查詢awr配置
scott@CNMMBO> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION                TOPNSQL
---------- -------------------- ------------------------- ----------
 938506715 +00000 01:00:00.0    +00007 00:00:00.0        DEFAULT

--發布sql查詢
scott@CNMMBO> select * from dept where loc='CHICAGO';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

--獲得sql_id
scott@CNMMBO> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ------------------------------------------
000000009F942760 2626775672 2jbkb5qf92ums            3          0 select * from dept where loc='CHICAGO'

--從v$sql_plan獲得緩衝區的執行計畫
scott@CNMMBO> get sql_plan_curr.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col options format a25
  5  col object_name format a25 wrap
  6  SELECT id,
  7          operation,
  8          options,
  9          object_name,
 10          bytes,
 11          cpu_cost,
 12          io_cost
 13  FROM v$sql_plan
 14    WHERE sql_id = '&input_sql_id'
 15* ORDER BY id;
scott@CNMMBO> @sql_plan_curr.sql      -->此時可以查詢到對應sql的執行計畫
Enter value for input_sql_id: 2jbkb5qf92ums

 ID OPERATION                OPTIONS                  OBJECT_NAME      BYTES  CPU_COST    IO_COST
--- ------------------------- ------------------------- --------------- ------- ---------- ----------
  0 SELECT STATEMENT
  1 TABLE ACCESS              FULL                      DEPT                20      36567          3

--下面嘗試從dba_hist_sql_plan獲得執行計畫
scott@CNMMBO> get sql_plan_his.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col object_name format a25 wrap
  5  SELECT id,
  6          operation,
  7          options,
  8          object_name,
  9          bytes,
 10          cpu_cost,
 11          io_cost
 12      FROM dba_hist_sql_plan
 13    WHERE sql_id = '&input_sql_id'
 14* ORDER BY id;
scott@CNMMBO> @sql_plan_his    --查詢無法獲得執行計畫
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

scott@CNMMBO> exec dbms_workload_repository.create_snapshot();  -->執行一次快照，寫入緩衝區的內容倒snapsho

PL/SQL procedure successfully completed.

scott@CNMMBO> @sql_plan_his    -->依舊無法獲得執行計畫
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

1 2 3 下一頁