Home » SQL & PL/SQL » SQL & PL/SQL » Determine Explain Plan for a SQL run several hours prior (Oracle 10g)
Determine Explain Plan for a SQL run several hours prior [message #423304] Wed, 23 September 2009 05:04 Go to next message
solman
Messages: 3
Registered: September 2009
Location: UK
Junior Member
In Oracle 10g can you determine what the actual explain plan was for an SQL statement that was run several hours prior.
Its just that whenever i run an pl/sql procedure overnight it takes forever to run. However when i run the same procedure in the morning I have no performance issues. CPU load and data volumes are similar.
Thanks
Re: Determine Explain Plan for a SQL run several hours prior [message #423305 is a reply to message #423304] Wed, 23 September 2009 05:09 Go to previous messageGo to next message
solman
Messages: 3
Registered: September 2009
Location: UK
Junior Member
I do not have access to any windows tools like Toad. I was hoping this information would be recorded in the data dictionary somewhere.
Re: Determine Explain Plan for a SQL run several hours prior [message #423306 is a reply to message #423304] Wed, 23 September 2009 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could set tracing on overnight.

You could have a look in v$sql_plan first thing in the morning and see if the plan is still in there.
Re: Determine Explain Plan for a SQL run several hours prior [message #423307 is a reply to message #423304] Wed, 23 September 2009 05:14 Go to previous message
solman
Messages: 3
Registered: September 2009
Location: UK
Junior Member
Thanks.
using v$sql_plan and v$sql gives me what I am looking for

thanks
Previous Topic: Date function
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Fri Dec 02 12:17:42 CST 2016

Total time taken to generate the page: 0.16319 seconds