By analyzing the awr report ,you found that there was some issue with a particular sql_id, and you want to get the explain plans associated with that sql_id. We can get the explains using dbms_xplan.display_awr
Suppose the sql_id is : 00tr6c6tngp4x
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('00tr6c6tngp4x'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 00tr6c6tngp4x
--------------------
insert into SIEBEL.S_DOCK_INITM_01 (PR_TBL_ROW_ID, DOCK_NAME,
NODE_ID, VIS_STRENGTH) select DISTINCT S1.ROW_ID PR_TBL_ROW_ID_T,
'PTY-A' DOCK_NAME_T, '1-1LFJZRG' NODE_ID_T, 56 VIS_STRENGTH_T
from SIEBEL.S_DOCK_INITM_01 di , SIEBEL.S_PARTY_PER T1 ,
SIEBEL.S_PARTY S1 where di.NODE_ID = :V1 and
di.DOCK_NAME = 'PTY-O' and di.VIS_STRENGTH >= 60 and S1.ROW_ID is not
null and S1.ROW_ID <> 'No Match Row Id' and T1.PARTY_ID = S1.ROW_ID
and S1.PARTY_TYPE_CD = 'AccessGroup' and not exists (select 'X'
from SIEBEL.S_DOCK_INITM_01 di2 where di2.NODE_ID
= :V2 and di2.DOCK_NAME = 'PTY-A' and
di2.PR_TBL_ROW_ID = T1.PARTY_ID)
Plan hash value: 272704099
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 27122 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | HASH UNIQUE | | 1 | 73 | 27122 (76)| 00:00:02 |
| 3 | MERGE JOIN ANTI | | 225M| 15G| 6554 (1)| 00:00:01 |
| 4 | SORT JOIN | | 225M| 10G| 6551 (1)| 00:00:01 |
| 5 | HASH JOIN | | 225M| 10G| 6551 (1)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN| | 3252 | 133K| 42 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | S_PARTY_V1 | 1 | 24 | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 3252 | 58536 | 39 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 3252 | 58536 | 39 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN| S_PARTY_PER_F1 | 69355 | 474K| 2 (0)| 00:00:01 |
| 11 | SORT UNIQUE | | 17 | 408 | 3 (0)| 00:00:01 |
| 12 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 17 | 408 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_014f215e3eec0001" used for this statement
- this is an adaptive plan
SQL_ID 00tr6c6tngp4x
--------------------
insert into SIEBEL.S_DOCK_INITM_01 (PR_TBL_ROW_ID, DOCK_NAME,
NODE_ID, VIS_STRENGTH) select DISTINCT S1.ROW_ID PR_TBL_ROW_ID_T,
'PTY-A' DOCK_NAME_T, '1-1LFJZRG' NODE_ID_T, 56 VIS_STRENGTH_T
from SIEBEL.S_DOCK_INITM_01 di , SIEBEL.S_PARTY_PER T1 ,
SIEBEL.S_PARTY S1 where di.NODE_ID = :V1 and
di.DOCK_NAME = 'PTY-O' and di.VIS_STRENGTH >= 60 and S1.ROW_ID is not
null and S1.ROW_ID <> 'No Match Row Id' and T1.PARTY_ID = S1.ROW_ID
and S1.PARTY_TYPE_CD = 'AccessGroup' and not exists (select 'X'
from SIEBEL.S_DOCK_INITM_01 di2 where di2.NODE_ID
= :V2 and di2.DOCK_NAME = 'PTY-A' and
di2.PR_TBL_ROW_ID = T1.PARTY_ID)
Plan hash value: 922667118
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | HASH UNIQUE | | 1 | 204 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS ANTI | | 1 | 204 | 4 (0)| 00:00:01 |
| 4 | HASH JOIN | | 1 | 108 | 4 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN| | 1 | 101 | 2 (0)| 00:00:01 |
| 6 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 1 | 77 | 0 (0)| |
| 7 | BUFFER SORT | | 1 | 24 | 2 (0)| 00:00:01 |
| 8 | INDEX RANGE SCAN | S_PARTY_V1 | 1 | 24 | 2 (0)| 00:00:01 |
| 9 | INDEX FAST FULL SCAN| S_PARTY_PER_F1 | 1000M| 6675M| 2 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 1 | 96 | 0 (0)| |
-----------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_014f215e3eec0001" used for this statement
- this is an adaptive plan
SQL_ID 00tr6c6tngp4x
--------------------
insert into SIEBEL.S_DOCK_INITM_01 (PR_TBL_ROW_ID, DOCK_NAME,
NODE_ID, VIS_STRENGTH) select DISTINCT S1.ROW_ID PR_TBL_ROW_ID_T,
'PTY-A' DOCK_NAME_T, '1-1LFJZRG' NODE_ID_T, 56 VIS_STRENGTH_T
from SIEBEL.S_DOCK_INITM_01 di , SIEBEL.S_PARTY_PER T1 ,
SIEBEL.S_PARTY S1 where di.NODE_ID = :V1 and
di.DOCK_NAME = 'PTY-O' and di.VIS_STRENGTH >= 60 and S1.ROW_ID is not
null and S1.ROW_ID <> 'No Match Row Id' and T1.PARTY_ID = S1.ROW_ID
and S1.PARTY_TYPE_CD = 'AccessGroup' and not exists (select 'X'
from SIEBEL.S_DOCK_INITM_01 di2 where di2.NODE_ID
= :V2 and di2.DOCK_NAME = 'PTY-A' and
di2.PR_TBL_ROW_ID = T1.PARTY_ID)
Plan hash value: 1562107792
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 8865 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | HASH UNIQUE | | 1 | 204 | 8865 (3)| 00:00:01 |
| 3 | HASH JOIN RIGHT ANTI | | 1 | 204 | 8865 (3)| 00:00:01 |
| 4 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 1 | 96 | 0 (0)| |
| 5 | HASH JOIN | | 1 | 108 | 8864 (3)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN| | 1 | 101 | 3 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | S_PARTY_V1 | 1 | 24 | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 77 | 0 (0)| |
| 9 | INDEX RANGE SCAN | S_DOCK_INITM_01_U1 | 1 | 77 | 0 (0)| |
| 10 | INDEX FAST FULL SCAN| S_PARTY_PER_F1 | 13M| 87M| 8765 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_014f215e3eec0001" used for this statement
112 rows selected.
We can observe, for that particular sql_id, 3 explain plans were associated. This will help us in finding better explain plans running in the past.
