如何追踪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;  /*再次手动收集快照*/


/*DBA_HIST_SQLPLAN视图可以看到同一个SQL_ID的PLAN_HASH_VALUE的变化*/
select sql_id, PLAN_HASH_VALUE,
       to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') Timestamp
from DBA_HIST_SQL_PLAN
where sql_id= '2yfq6rwp3krjt';  

SQL_ID        PLAN_HASH_VALUE TIMESTAMP
------------- --------------- -----------------------
2yfq6rwp3krjt      3747652938 26-NOV-2014 17:34:18
2yfq6rwp3krjt      3747652938 26-NOV-2014 17:34:18
2yfq6rwp3krjt      3747652938 26-NOV-2014 17:34:18
2yfq6rwp3krjt      3993303771 26-NOV-2014 17:42:26
2yfq6rwp3krjt      3993303771 26-NOV-2014 17:42:26


/*通过DBMS_XPLAN.DISPLAY_AWR 可以直接看到变化的执行计划*/
SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('2yfq6rwp3krjt',null, null,  'ALL' ));
PLAN_TABLE_OUTPUT
SQL_ID 2yfq6rwp3krjt
--------------------
select /*Zhou Test*/ * from big_table where id<10
Plan hash value: 3747652938
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |     9 |   819 |     3   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN          | BIG_TABLE_PK |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


SQL_ID 2yfq6rwp3krjt
--------------------
select /*Zhou Test*/ * from big_table where id<10
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |    22 (100)|          |
|   1 |  TABLE ACCESS FULL| BIG_TABLE |     9 |   819 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------------------


/*通过awrsqrpt.sql 可以更加直观地看到执行计划的变化 同一个SQL_ID 有两个PHV  及执行计划变化前后的Statistics*/
SQL> @?/rdbms/admin/awrsqrpt.sql

caixuqad

Adam丶鱼

caixuqad 发表于 2017-5-22 21:28

fteair

哇哇哇哇( ⊙o⊙ )哇

caixuqad