We can get the all the execution plan details of an sql_id for a particular awr snapshot.
EXAMPLE:
sql_id – > 5vqy6cj4jr89k
Run the below script:
start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql
We need to pass below inputs while running the script.
REPORT_TYPE –
NUM_DAYS –
BEGIN_SNAP –
END_SNAP –
SQL_ID –
SQL> start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3933706161 POCDB 1 POCDB
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3893947977 1 POCDB POCDB sec58-6
* 3933706161 1 POCDB POCDB sec60-1
Using 3933706161 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
POCDB POCDB 11718 04 Feb 2018 00:00 1
11719 04 Feb 2018 01:00 1
11720 04 Feb 2018 02:00 1
11721 04 Feb 2018 03:00 1
11722 04 Feb 2018 04:00 1
11723 04 Feb 2018 05:00 1
11724 04 Feb 2018 06:00 1
11725 04 Feb 2018 07:00 1
11726 04 Feb 2018 08:00 1
11727 04 Feb 2018 09:00 1
11728 04 Feb 2018 10:00 1
11729 04 Feb 2018 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11728
Begin Snapshot Id specified: 11728
Enter value for end_snap: 11729
End Snapshot Id specified: 11729
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 5vqy6cj4jr89k
SQL ID specified: 5vqy6cj4jr89k
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_11728_11729.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrsqlrpt_1_11728_11729.txt
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDB 3933706161 POCDB 1 05-Nov-17 14:45 12.1.0.2.0 NO
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 11728 04-Feb-18 10:00:06 58 1.5
End Snap: 11729 04-Feb-18 11:00:32 58 1.5
Elapsed: 60.44 (mins)
DB Time: 3.63 (mins)
SQL Summary DB/Inst: POCDB/POCDB Snaps: 11728-11729
Elapsed
SQL Id Time (ms)
------------- ----------
5vqy6cj4jr89k 11,548
Module: JDBC Thin Client
SELECT /*+ parallel(10) */ INTERFACE_ID , STATUS , EVENT_TIMESTAMP , CORRELATION
_ID , SERVER_INFO , SERVICE_NAME , RESUBMISSION_TIMESTAMP , RESUBMISSION_USER ,
INSERT_TIMESTAMP , RESUBMISSION_IN_PROGRESS_FLAG , LOCKED_BY , SOURCE , EXECUTIO
N_TIME FROM ( SELECT ROWNUM ROWNUMBER , SEARCHRESULT.* FROM ( SELECT * FROM POCD_
-------------------------------------------------------------
SQL ID: 5vqy6cj4jr89k DB/Inst: POCDB/POCDB Snaps: 11728-11729
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> SELECT /*+ parallel(10) */ INTERFACE_ID , STATUS , EVENT_TIMESTAMP , C...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 2060884574 11,548 16 11729 11729
-------------------------------------------------------------
Plan 1(PHV: 2060884574)
-----------------------
Plan Statistics DB/Inst: POCDB/POCDB Snaps: 11728-11729
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 11,548 721.7 5.3
CPU Time (ms) 8,521 532.6 3.7
Executions 16 N/A N/A
Buffer Gets 793,223 49,576.4 27.3
Disk Reads 317,762 19,860.1 45.6
Parse Calls 324 20.3 1.5
Rows 17 1.1 N/A
User I/O Wait Time (ms) 1,971 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 12 N/A N/A
Concurrency Wait Time (ms) 1,527 N/A N/A
Invalidations 0 N/A N/A
Version Count 2 N/A N/A
Sharable Mem(KB) 231 N/A N/A
-------------------------------------------------------------
Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 618 (100)| |
| 1 | VIEW | | 1 | 362 | 618 (1)| 00:00:01 |
| 2 | COUNT STOPKEY | | | | | |
| 3 | PX COORDINATOR | | | | | |
| 4 | PX SEND QC (ORDER) | :TQ10001 | 1 | 349 | 618 (1)| 00:00:01 |
| 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 1 | 349 | 618 (1)| 00:00:01 |
| 7 | SORT GROUP BY STOPKEY | | 1 | 240 | 618 (1)| 00:00:01 |
| 8 | PX RECEIVE | | 1 | 240 | 618 (1)| 00:00:01 |
| 9 | PX SEND RANGE | :TQ10000 | 1 | 240 | 618 (1)| 00:00:01 |
| 10 | HASH GROUP BY | | 1 | 240 | 618 (1)| 00:00:01 |
| 11 | FILTER | | | | | |
| 12 | NESTED LOOPS | | 1 | 240 | 618 (1)| 00:00:01 |
| 13 | NESTED LOOPS | | 6 | 240 | 618 (1)| 00:00:01 |
| 14 | PX BLOCK ITERATOR | | | | | |
| 15 | TABLE ACCESS FULL | POCD_RULL_SEARCH | 1 | 184 | 617 (1)| 00:00:01 |
| 16 | INDEX RANGE SCAN | RULL_ATTRIBUTES_COR_ID_IDX | 6 | | 0 (0)| |
| 17 | TABLE ACCESS BY INDEX ROWID| POCD_RULL_ATTRIBUTES | 1 | 56 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
5vqy6cj4jr89 SELECT /*+ parallel(10) */ INTERFACE_ID , STATUS , EVENT_TIMESTAM
P , CORRELATION_ID , SERVER_INFO , SERVICE_NAME , RESUBMISSION_TI
MESTAMP , RESUBMISSION_USER , INSERT_TIMESTAMP , RESUBMISSION_IN_
PROGRESS_FLAG , LOCKED_BY , SOURCE , EXECUTION_TIME FROM ( SELECT
ROWNUM ROWNUMBER , SEARCHRESULT.* FROM ( SELECT * FROM POCD_RULL_
SEARCH WHERE CORRELATION_ID IN ( SELECT CORRELATION_ID FROM ( SEL
ECT CORRELATION_ID FROM POCD_RULL_ATTRIBUTES WHERE ATTRIBUTE_VALUE
IN(:B3 ) AND ( EVENT_TIMESTAMP BETWEEN TO_DATE (:B2 , 'YYYY-MON-
DD HH24:MI:SS') AND TO_DATE (:B1 , 'YYYY-MON-DD HH24:MI:SS') ) GR
OUP BY CORRELATION_ID ) ) AND ( EVENT_TIMESTAMP BETWEEN TO_DATE (
:B2 , 'YYYY-MON-DD HH24:MI:SS') AND TO_DATE (:B1 , 'YYYY-MON-DD H
H24:MI:SS') ) ORDER BY EVENT_TIMESTAMP DESC ) SEARCHRESULT WHERE
ROWNUM<=:B4 ) WHERE ROWNUMBER >:B5
Report written to awrsqlrpt_1_11728_11729.txt
