我们在分析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)| |