I am not sure how much overhead the following would introduce in a production system. Shouldn't be all that much but you could get big trace files if there is a lot of hard parsing:
http://jonathanlewis.wordpress.com/2006/11/27/event-10132/ At 07:30 AM 3/16/2007, Ankur Godambe wrote:
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
Regards Wolfgang Breitling Centrex Consulting Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
