| Getting Plan of SQL query executed in the past (merged) [message #354119] |
Thu, 16 October 2008 08:18  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I was trying to get execution plan of sql query executed 15 minutes back.
A) First I tried the following
1)select sql_id from v$sql where sql_text like '%myobj%';
Then using the sql_id returned by above statement I tried the following
2)select * from table (DBMS_XPLAN.DISPLAY_AWR('2gw6nzhs6pxu9',null,null,'ALL'));
It returned 'No rows returned'
I checked the AWR snapshot configuration and timings in OEM
Snapshot Retention (days) 7
Snapshot Interval (minutes) 60
Collection Level TYPICAL
Next Snapshot Capture Time 16-Oct-2008 14:00:34
Latest Snapshot Time 16-Oct-2008 13:00:34
Earliest Snapshot Time 09-Oct-2008 01:00:04
Now I executed the sql query at 13:25 so thinking that AWR snapshot might not have captured this, I tried the following
B) SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%myobj%'
select * from table (DBMS_XPLAN.DISPLAY_cursor('2gw6nzhs6pxu9',0));
and it returned the following
NOTE: cannot fetch plan for SQL_ID: 2gw6nzhs6pxu9, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
Then I queried v$sql_plan and there was nothing in it
select count(1) from v$sql_plan where sql_id='2gw6nzhs6pxu9'
C) The 'Next Snapshot Capture Time' being at 14:00 I again tried the following at 14:15
select * from table (DBMS_XPLAN.DISPLAY_AWR('2gw6nzhs6pxu9',null,null,'ALL'));
But even this time no results were returned.
Can anybody susggest what I am missing here?
Thanks in Advance
Pratap
|
|
|
|
| Getting Plan of SQL query executed in the past [message #354120 is a reply to message #354119] |
Thu, 16 October 2008 08:18  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I was trying to get execution plan of sql query executed 15 minutes back.
A) First I tried the following
1)select sql_id from v$sql where sql_text like '%myobj%';
Then using the sql_id returned by above statement I tried the following
2)select * from table (DBMS_XPLAN.DISPLAY_AWR('2gw6nzhs6pxu9',null,null,'ALL'));
It returned 'No rows returned'
I checked the AWR snapshot configuration and timings in OEM
Snapshot Retention (days) 7
Snapshot Interval (minutes) 60
Collection Level TYPICAL
Next Snapshot Capture Time 16-Oct-2008 14:00:34
Latest Snapshot Time 16-Oct-2008 13:00:34
Earliest Snapshot Time 09-Oct-2008 01:00:04
Now I executed the sql query at 13:25 so thinking that AWR snapshot might not have captured this, I tried the following
B) SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%myobj%'
select * from table (DBMS_XPLAN.DISPLAY_cursor('2gw6nzhs6pxu9',0));
and it returned the following
NOTE: cannot fetch plan for SQL_ID: 2gw6nzhs6pxu9, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
Then I queried v$sql_plan and there was nothing in it
select count(1) from v$sql_plan where sql_id='2gw6nzhs6pxu9'
C) The 'Next Snapshot Capture Time' being at 14:00 I again tried the following at 14:15
select * from table (DBMS_XPLAN.DISPLAY_AWR('2gw6nzhs6pxu9',null,null,'ALL'));
But even this time no results were returned.
Can anybody suggest what I am missing here?
Thanks in Advance
Pratap
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
