Reading sql_plan of executed sql via dbms_xplan.display_awr()

我们在分析awr报告时肯定不可避免要分析top sql，为了能更好的再现当时sql的执行计划，我们可以通过sql_id来读取当时sql的执行计划，如：

QL> select * from table(dbms_xplan.display_awr('91k4w10zgrg4m'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 91k4w10zgrg4m
--------------------
select object_name, object_type from sys.all_objects o where o.owner =
:schema and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE',
'PROCEDURE', 'FUNCTION', 'SEQUENCE')

Plan hash value: 1956164056

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |       |       |   331 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID      | SUM$           |     1 |     7 |     1   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN               | I_SUM$_1       |     1 |       |     0   (0)|          |
|   3 |  VIEW                             | ALL_OBJECTS    |   270 |  7560 |   331   (6)| 00:00:04 |
|   4 |   FILTER                          |                |       |       |            |          |
|   5 |    HASH JOIN                      |                |   797 | 64557 |   331   (6)| 00:00:04 |
|   6 |     NESTED LOOPS                  |                |   149 |  5513 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID  | USER$          |     1 |    15 |     1   (0)| 00:00:01 |
|   8 |       INDEX UNIQUE SCAN           | I_USER1        |     1 |       |     0   (0)|          |
|   9 |      INDEX FULL SCAN              | I_USER2        |   149 |  3278 |     1   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL             | OBJ$           | 78131 |  3357K|   328   (6)| 00:00:04 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  11 |    TABLE ACCESS BY INDEX ROWID    | IND$           |     1 |     8 |     2   (0)| 00:00:01 |
|  12 |     INDEX UNIQUE SCAN             | I_IND1         |     1 |       |     1   (0)| 00:00:01 |
|  13 |    FIXED TABLE FULL               | X$KZSPR        |     1 |    26 |     0   (0)|          |
|  14 |    HASH JOIN                      |                |     1 |    25 |     3  (34)| 00:00:01 |
|  15 |     INDEX RANGE SCAN              | I_OBJAUTH1     |     1 |    12 |     2   (0)| 00:00:01 |
|  16 |     FIXED TABLE FULL              | X$KZSRO        |   100 |  1300 |     0   (0)|          |
|  17 |    FIXED TABLE FULL               | X$KZSPR        |     1 |    26 |     0   (0)|          |
|  18 |    NESTED LOOPS                   |                |     1 |    25 |     2   (0)| 00:00:01 |
|  19 |     INDEX RANGE SCAN              | I_OBJAUTH1     |     1 |    12 |     2   (0)| 00:00:01 |
|  20 |     FIXED TABLE FULL              | X$KZSRO        |     1 |    13 |     0   (0)|          |
|  21 |    HASH JOIN                      |                |     1 |    25 |     3  (34)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  22 |     INDEX RANGE SCAN              | I_OBJAUTH1     |     1 |    12 |     2   (0)| 00:00:01 |
|  23 |     FIXED TABLE FULL              | X$KZSRO        |   100 |  1300 |     0   (0)|          |
|  24 |    FIXED TABLE FULL               | X$KZSPR        |     1 |    26 |     0   (0)|          |
|  25 |    FIXED TABLE FULL               | X$KZSPR        |     1 |    26 |     0   (0)|          |
|  26 |    NESTED LOOPS                   |                |       |       |            |          |
|  27 |     NESTED LOOPS                  |                |     1 |    78 |     7   (0)| 00:00:01 |
|  28 |      NESTED LOOPS                 |                |     1 |    68 |     5   (0)| 00:00:01 |
|  29 |       NESTED LOOPS                |                |     1 |    56 |     4   (0)| 00:00:01 |
|  30 |        MERGE JOIN CARTESIAN       |                |     1 |    52 |     3   (0)| 00:00:01 |
|  31 |         INDEX RANGE SCAN          | I_OBJ5         |     1 |    39 |     3   (0)| 00:00:01 |
|  32 |         BUFFER SORT               |                |   100 |  1300 |     0   (0)|          |