Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> workaround for v$sql_plan
Hi,
There have been couple of occasions when developers have come to me saying the query used to run fine on production a week back but it's taking long time now. To be better equipped to answer these issues in future I thought of creating a table from v$sql_paln with "create table as select" every week so that explain plan of queries can be compared to see if there are any changes. I hit this bug#4434689 with 9.2.0.7 where selects on v$sql_plan failes with ora-600 [504].
Now, is there a better approach to achieve comparison between current plan and in past other than dumping v$sql_plan at regular intervals? I have statspack report but that's not set at the level(current level -5) to grab sql plan. Also I think that should fail as well because of the bug. I cannot set tracing as this is production db. I can use statspack to check if that query appears in it and if there are any changes to the logical or physical reads its doing, but I cannot come to a conclusion instantly about what's changed. There is a patch available but applying it would be a lengthy process of approvals and meetings.
Is someone aware of better approach or workaround?
Regards,
Ankur
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 16 2007 - 08:30:55 CDT