10G中查看历史执行计划信息
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/04/10g_hist_sql_plan.html
链接：http://www.dbaroad.me/archives/2009/04/10g_hist_sql_plan.html
曾经写过一篇文章：对比前后执行计划，发现问题。当系统出现异常语句，在分析语句前，我常会先查查这个语句的历史执划信息，看看是否发生变化，何时发生了变化。如果发生了变化，就可以找出以前的执行计划，与当前的执行计划进行对比，看看有什么不同，是新建、删除了索引？是bind peeking的原因？还是表长时间没有分析？
之前的文章写的是9i的系统，可以使用statspace报告获得相关信息，现在总结下10G的，使用的是AWR报告中的信息，主要是查询以下三个视图：
DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT。
下面来看看实验：
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 —
关键字: SQL优化 | 性能优化
不错不错，赞一个！
Reply
select * from table(dbms_xplan.display_awr(’gzyw5f73q0707′));
可以看到语句和计划
Reply