利用AWR查看SQL执行计划

在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语句相关信息抽取出来，如:

SYS@ orcl> select /* extrace_awr */ count(*) from redo_test;
  COUNT(*)
----------
    213495

SYS@ orcl> select sql_id from v$sql where sql_text like '%extrace_awr%' and sql_text not like '%like%';
SQL_ID
-------------
7d0r6vd8yccuy

SYS@ orcl> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7d0r6vd8yccuy, child number 0
-------------------------------------
select /* extrace_awr */ count(*) from redo_test

Plan hash value: 4170294667

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |   874 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| REDO_TEST |   189K|   874   (1)| 00:00:11 |
------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

# 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
# 需要注意的是如果在AWR快照自动生成之前冲刷了共享池，那么可能丢失SQL的执行统计信息
SYS@ orcl> alter system flush shared_pool;
System altered.

SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 7d0r6vd8yccuy, child number: 0 cannot be found

# 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划
SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test

Plan hash value: 4170294667

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |   874 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| REDO_TEST |   189K|   874   (1)| 00:00:11 |
------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy',null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test

Plan hash value: 4170294667

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |   874 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| REDO_TEST |   189K|   874   (1)| 00:00:11 |
------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / REDO_TEST@SEL$1

Note
-----
   - dynamic sampling used for this statement (level=2)

# 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句
SYS@ orcl> select operation, options, object_name from dba_hist_sql_plan where sql_id='7d0r6vd8yccuy';
OPERATION                      OPTIONS                        OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           REDO_TEST