10G中查看历史执行计划信息[转]

现在总结下10G的，使用的是AWR报告中的信息，主要是查询以下四个视图：
DBA_HIST_SQLTEXT、DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT。

Sometimes you will receive complains from users that something that has been running ok for ages on a production server, suddenly is running very very slowly. In 10g, AWR gives you the ability to go and check and possibly compare the execution path of a query at different times in the past.

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%your query%’;

DBID      | SQL_ID      | SQL_TEXT |

———-+————-+—————————+-

1234567890|12345xxxxxxxx|UPDATE STAGING SET BLAH    |

We know that yesterday it was running fine, but today it’s not, and users have confirmed that data volumes are identical between the two dates

select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.dbid = 1234567890 and q.sql_id = ‘12345xxxxxxxx’

and q.snap_id = s.snap_id

and s.begin_interval_time between sysdate-2 and sysdate

order by s.snap_id desc;

When you find this, take note of the PLAN_HASH_VALUE from the 2 different times that you want to compare

Actually the 3 parameters that you need are:

·         DBID

·         SQL_ID

·         PLAN_HASH_VALUE

 

And to check the execution plan, run the following query:

 

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value1>

 

compare that with the second one

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value2>

下面来看看实验：
Session 1：

SQL> select max(sid) from v$mystat;   MAX(SID) ---------- 542   SQL> create table test_plan as 2 select object_id,object_name from dba_objects;   Table created.   SQL> exec dbms_workload_repository.create_snapshot();   PL/SQL procedure successfully completed.   SQL> select * from test_plan where object_id=10 and object_name='C_USER#';   OBJECT_ID OBJECT_NAME ---------- ------------------------------ 10 C_USER#   SQL> exec dbms_workload_repository.create_snapshot();   PL/SQL procedure successfully completed.   SQL> select * from test_plan where object_id=10 and object_name='C_USER#';   OBJECT_ID OBJECT_NAME ---------- ------------------------------ 10 C_USER#

Session 2：

SQL> select sid,SQL_HASH_VALUE,SQL_ID,PREV_HASH_VALUE,PREV_SQL_ID from v$session 2 where sid=542;   SID SQL_HASH_VALUE SQL_ID PREV_HASH_VALUE PREV_SQL_ID ---------- -------------- ------------- --------------- ------------- 542 0 2397140960 36qmvdf7f2xz0   SQL> select sql_id, 2 plan_hash_value, 3 id, 4 operation, 5 options, 6 object_owner, 7 object_name, 8 depth, 9 cost, 10 timestamp 11 from DBA_HIST_SQL_PLAN 12 where sql_id = '36qmvdf7f2xz0';   SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_OWN OBJECT_NAME DEPTH COST TIMESTAMP ------------- --------------- --- ----------------- -------- ---------- ------------ ----- ------ ------------------- 36qmvdf7f2xz0 2068845065 0 SELECT STATEMENT 0 24 2009-04-08 11:27:42 36qmvdf7f2xz0 2068845065 1 TABLE ACCESS FULL SYS TEST_PLAN 1 24 2009-04-08 11:27:42

可以看到DBA_HIST_SQL_PLAN已经记录了执行计划的信息。此时，如果在object_id列上建索引，使执行计划发生变化：
Session 1：

SQL> create index ind_id on test_plan(object_id);   Index created.   SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST_PLAN',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);   PL/SQL procedure successfully completed.   SQL> set autotrace on SQL> select * from test_plan where object_id=10 and object_name='C_USER#';   OBJECT_ID OBJECT_NAME ---------- ------------------------------ 10 C_USER#     Execution Plan ---------------------------------------------------------- Plan hash value: 2839240733   ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_PLAN | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("OBJECT_NAME"='C_USER#') 2 - access("OBJECT_ID"=10)     Statistics ---------------------------------------------------------- 252 recursive calls 0 db block gets 68 consistent gets 0 physical reads 0 redo size 593 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 15 sorts (memory) 0 sorts (disk) 1 rows processed   SQL> set autotrace off SQL> select * from test_plan where object_id=10 and object_name='C_USER#';   OBJECT_ID OBJECT_NAME ---------- ------------------------------ 10 C_USER#   SQL> exec dbms_workload_repository.create_snapshot();   PL/SQL procedure successfully completed.

Session 2:

SQL> select sql_id, 2 plan_hash_value, 3 id, 4 operation, 5 options, 6 object_owner, 7 object_name, 8 depth, 9 cost, 10 timestamp 11 from DBA_HIST_SQL_PLAN 12 where sql_id = '36qmvdf7f2xz0' 13 order by 2, 3;   SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_OWN OBJECT_NAME DEPTH COST TIMESTAMP ------------- --------------- --- -------------------- ---------- ---------- --------------- ----- ------ ------------------- 36qmvdf7f2xz0 2068845065 0 SELECT STATEMENT 0 24 2009-04-08 11:27:42 36qmvdf7f2xz0 2068845065 1 TABLE ACCESS FULL SYS TEST_PLAN 1 24 2009-04-08 11:27:42 36qmvdf7f2xz0 2839240733 0 SELECT STATEMENT 0 2 2009-04-08 11:30:59 36qmvdf7f2xz0 2839240733 1 TABLE ACCESS BY INDEX R SYS TEST_PLAN 1 2 2009-04-08 11:30:59 36qmvdf7f2xz0 2839240733 2 INDEX RANGE SCAN SYS IND_ID 2 1 2009-04-08 11:30:59   SQL> select a.INSTANCE_NUMBER, 2 a.snap_id, 3 a.sql_id, 4 a.plan_hash_value, 5 b.begin_interval_time 6 from dba_hist_sqlstat a, dba_hist_snapshot b 7 where sql_id = '36qmvdf7f2xz0' 8 and a.snap_id = b.snap_id 9 order by instance_number, snap_id;   INSTANCE_NUMBER SNAP_ID SQL_ID PLAN_HASH_VALUE BEGIN_INTERVAL_TIME --------------- ---------- ------------- --------------- ------------------------ 1 3997 36qmvdf7f2xz0 2068845065 08-APR-09 11.27.28.891 1 3998 36qmvdf7f2xz0 2839240733 08-APR-09 11.27.50.131

通过查询dba_hist_sqlstat、dba_hist_snapshot就可以获得执行计划的历史信息，可以看出何时发生了变化。
如果我们再在object_id,object_name列上建组合索引。使执行计划再发生变化，这时再来观察执行计划信息：

SQL> select sql_id, 2 plan_hash_value, 3 id, 4 operation, 5 options, 6 object_owner, 7 object_name, 8 depth, 9 cost, 10 timestamp 11 from DBA_HIST_SQL_PLAN 12 where sql_id = '36qmvdf7f2xz0' 13 order by 2, 3;   SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_OWN OBJECT_NAME DEPTH COST TIMESTAMP ------------- --------------- --- -------------------- ---------- ---------- --------------- ----- ------ ------------------- 36qmvdf7f2xz0 2068845065 0 SELECT STATEMENT 0 24 2009-04-08 11:27:42 36qmvdf7f2xz0 2068845065 1 TABLE ACCESS FULL SYS TEST_PLAN 1 24 2009-04-08 11:27:42 36qmvdf7f2xz0 2829165872 0 SELECT STATEMENT 0 1 2009-04-08 11:36:01 36qmvdf7f2xz0 2829165872 1 INDEX RANGE SCAN SYS IND_ID_NAME 1 1 2009-04-08 11:36:01 36qmvdf7f2xz0 2839240733 0 SELECT STATEMENT 0 2 2009-04-08 11:30:59 36qmvdf7f2xz0 2839240733 1 TABLE ACCESS BY INDEX R SYS TEST_PLAN 1 2 2009-04-08 11:30:59 36qmvdf7f2xz0 2839240733 2 INDEX RANGE SCAN SYS IND_ID 2 1 2009-04-08 11:30:59   7 rows selected.   SQL> select a.INSTANCE_NUMBER, 2 a.snap_id, 3 a.sql_id, 4 a.plan_hash_value, 5 b.begin_interval_time 6 from dba_hist_sqlstat a, dba_hist_snapshot b 7 where sql_id = '36qmvdf7f2xz0' 8 and a.snap_id = b.snap_id 9 order by instance_number, snap_id;   INSTANCE_NUMBER SNAP_ID SQL_ID PLAN_HASH_VALUE BEGIN_INTERVAL_TIME --------------- ---------- ------------- --------------- ------------------------ 1 3997 36qmvdf7f2xz0 2068845065 08-APR-09 11.27.28.891 1 3998 36qmvdf7f2xz0 2839240733 08-APR-09 11.27.50.131 1 4003 36qmvdf7f2xz0 2829165872 08-APR-09 11.39.46.599

同样，也能查到相关的执行计划信息。所以，当出现异常语句时，我们可以先查查dba_hist_sqlstat，dba_hist_snapshot，获得历史执行计划信息，看看何时发生了变化。再通过查询DBA_HIST_SQL_PLAN来获得具体的历史执行执行计划，与当前的执行计划进行对比，发现问题。

注：这里有几个snapshot点，3999至4002间，都没有记录我执行这个SQL的信息,不知是执行次数少了，还是刷快了的原因。最后多执行了几次这条语句，再手工创建一个快照，才产生相关信息。

SQL> select INSTANCE_NUMBER, 2 snap_id, 3 sql_id, 4 plan_hash_value 5 from dba_hist_sqlstat 6 where sql_id = '36qmvdf7f2xz0';   INSTANCE_NUMBER SNAP_ID SQL_ID PLAN_HASH_VALUE --------------- ---------- ------------- --------------- 1 3997 36qmvdf7f2xz0 2068845065 1 3998 36qmvdf7f2xz0 2839240733 1 4003 36qmvdf7f2xz0 2829165872

— The End —