如何追踪Oracle历史执行计划变动

caixuqad

/*
在oracle数据库中 同一个SQL语句可能拥有不同的执行计划 历史的执行计划变动可以通过DBA_HIST_SQLPLAN视图或者awr查询到
当前的执行计划保存在V$SQL_PLAN中
Metalink:How to Determine When an Execution Plan was Produced Using DBA_HIST_SQLPLAN (文档 ID 879677.1)
通过对一个简单查询谓词使用到的索引状态进行更改 模拟执行计划的变化
由于DBA_HIST_SQLPLAN视图的数据来源于 workload repository 所以在开始和结束的时候都会手动强制做一次awr快照
*/

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Session 1#: conn / as sysdba
SQL>  exec dbms_workload_repository.create_snapshot();
SQL> select index_name,status from dba_indexes where index_name='BIG_TABLE_PK';
INDEX_NAME                     STATUS
------------------------------ --------
BIG_TABLE_PK                   VALID
SQL>  oradebug setorapid 31
Oracle pid: 31, Unix process pid: 4673, image: oracle@vrh-ora11gR2 (TNS V1-V3)
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/freebaseun/freebase/trace/freebase_ora_4673.trc
SQL> oradebug event 10046 trace name context forever,level 12;

Session 2#: conn as scott
SQL> conn scott/tiger
Connected.
SQL> select /*Zhou Test*/ * from big_table where id<10;

Session 1#:
SQL> alter index scott.big_table_pk unusable;
SQL> select index_name,status from dba_indexes where index_name='BIG_TABLE_PK';
INDEX_NAME                     STATUS
------------------------------ --------
BIG_TABLE_PK                   UNUSABLE

Session 2#:
SQL> select /*Zhou Test*/ * from big_table where id<10;

Session 1#:
SQL>  oradebug event 10046 trace name context off;

/***********************************************************OracleFreeBase********************************************/
/*make tkprof on 10046 raw trace*/
oracle@vrh-ora11gR2:/home/oracle>cp /u01/app/oracle/diag/rdbms/freebaseun/freebase/trace/freebase_ora_4673.trc /home/oracle/
oracle@vrh-ora11gR2:/home/oracle>tkprof freebase_ora_4673.trc freebase_ora_4673.out explain="/as sysdba" sys=no

SQL ID: 2yfq6rwp3krjt Plan Hash: 3747652938
select /*Zhou Test*/ *
from
big_table where id<10
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         9          9          9  TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=5 pr=5 pw=0 time=34214 us cost=3 size=819 card=9)
         9          9          9   INDEX RANGE SCAN BIG_TABLE_PK (cr=3 pr=4 pw=0 time=34240 us cost=2 size=0 card=9)(object id 77220)


                                 
SQL ID: 2yfq6rwp3krjt Plan Hash: 3993303771
select /*Zhou Test*/ *
from
big_table where id<10
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         9          9          9  TABLE ACCESS FULL BIG_TABLE (cr=72 pr=34 pw=0 time=67 us cost=22 size=819 card=9)
                 
/*上面的10046 trace里面可以看到同一个SQL_ID 2yfq6rwp3krjt 的执行计划已经了 SQL_PLAN中记录了最后一次的plan_hash_value*/

SQL> col sql_text for a50
SQL> col time for a35
SQL> set linesize 200
SQL> SELECT s.sql_text,p.hash_value,p.sql_id,p.plan_hash_value,p.child_number,to_char(p.timestamp,'DD-MON-YYYY HH24:MI:SS') TIME
      FROM V$SQL s, V$SQL_PLAN p
     where s.plan_hash_value = p.plan_hash_value   
       and p.sql_id='2yfq6rwp3krjt';
SQL_TEXT                                           HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER TIME
-------------------------------------------------- ---------- ------------- --------------- ------------ -----------------------------------
select /*Zhou Test*/ * from big_table where id<10   708402745 2yfq6rwp3krjt      3993303771            1 26-NOV-2014 17:42:26
select /*Zhou Test*/ * from big_table where id<10   708402745 2yfq6rwp3krjt      3993303771            1 26-NOV-2014 17:42:26


SQL> execute DBMS_WORKLOAD_REPOSITORY.create_snapshot;  /*再次手动收集快照*/

caixuqad

张兴康

caixuqad

fteair