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 —