| How to retrieve Explain Plan in Past [message #233124] |
Tue, 24 April 2007 16:34  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
i am using Oracle 9.2.0.6.0 on Reh Hat Linux RHEL-4
We are gathering database statistics every week end.
However in last two months we have observed that after gathering stats on week end the execution plan for certain queries is changing drastically.
My question can we find out what was the execution plan in history e.g. last week before gathering statistics?
I tried using sprepsql.sql but for all the hash values i provided it gave an error.(i retrieved hash values from v$sqlarea,v$sqltext)
Also, is it possible to get statspack with Explain Plan for all sql statments exeeding threshold for disk reads/gets?
Thanks and Regards,
Pratap
|
|
|
|
| Re: How to retrieve Explain Plan in Past [message #233335 is a reply to message #233124] |
Wed, 25 April 2007 13:35  |
dbaxchangedba
Messages: 26
Registered: November 2005
|
Junior Member |
|
|
"We are gathering database statistics every week end.
However in last two months we have observed that after gathering stats on week end the execution plan for certain queries is changing drastically."
Hope you've enabled monitoring on your tables and only collect statistics for tables where 10% of data has changed (stale statistics detection ) otherwise it is not a good idea to collect statistics every week.
Never get rid of old statistics completely. Before you collect statistics on your database tables and indexes, it is always a good idea to back up your existing statistics using dbms_stats.export_schema_stats. By giving it a new statid everytime you export the stats, you can save your old stats within your own stats table.
"My question can we find out what was the execution plan in history e.g. last week before gathering statistics?"
You could try your luck against v$sql_plan but its not guaranteed especially if your database was restarted. Your best bet would be to do a restore of your production database to the date when you think the statistics were okay, to a test machine and then compare your plans between production and test machine.
If you want to establish plan stability for certain queries then stored outlines is a possiblity .
You best bet of getting run time execution plans is to enable session tracing (sql_trace). You can potentially enable it through an "on logon" trigger.
Statspack dont give execution plans (as far as I know in 9.2.0.6) but you do get listing of statements that have high buffer reads, physical reads, parses and such and you can compare reports from two different intervals that will easily tell you as to which statements have changed over a period of time.
Good luck.....
http://www.dbaxchange.com
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
