之前有用户在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
--------------------------------------------------------------------------------
askmac.cn
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
