之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。 这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:
SQL> set linesize 200 pagesize 2000; SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- oracledatabase12g.com SQL> select /* extract_me */ count(*) from maclean; COUNT(*) ---------- 9564 SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%'; SQL_ID ------------- 8vff23q8qp9fj SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0)); PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 8vff23q8qp9fj, child number 0 ------------------------------------- select /* extract_me */ count(*) from maclean Plan hash value: 1679547536 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 34 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 10438 | 34 (0)| 00:00:01 | ---------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 18 rows selected. /* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失, 需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息 */ SQL> alter system flush shared_pool; System altered. SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0)); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found /* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */ SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj')); PLAN_TABLE_OUTPUT -------------------------------------------------- SQL_ID 8vff23q8qp9fj -------------------- select /* extract_me */ count(*) from maclean Plan hash value: 1679547536 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 34 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 10438 | 34 (0)| 00:00:01 | ---------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 18 rows selected. /* 这里可以代入'all'选项获取更细致的计划信息 */ SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- SQL_ID 8vff23q8qp9fj -------------------- select /* extract_me */ count(*) from maclean Plan hash value: 1679547536 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 34 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 10438 | 34 (0)| 00:00:01 | ---------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / MACLEAN@SEL$1 Note ----- - dynamic sampling used for this statement 24 rows selected. /* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */ SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj'; OPERATION OPTIONS OBJECT_NAME ------------------------------ ------------------------------ ------------------------------- SELECT STATEMENT SORT AGGREGATE TABLE ACCESS FULL MACLEAN
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277761