Home » RDBMS Server » Performance Tuning » Getting Plan of SQL query executed in the past (merged)
Getting Plan of SQL query executed in the past (merged) [message #354119] Thu, 16 October 2008 08:18 Go to next message
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 Go to previous message
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
Previous Topic: Very Strange and Interesting Oracle Issue
Next Topic: optimize query
Goto Forum:
  


Current Time: Sun Dec 04 04:29:29 CST 2016

Total time taken to generate the page: 0.16675 seconds