Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> workaround for v$sql_plan

workaround for v$sql_plan

From: Ankur Godambe <agodambe_at_ketera.com>
Date: Fri, 16 Mar 2007 06:30:55 -0700
Message-ID: <F2B0430DD90A1C4383C1F887FBAC45DB033DA14E@kusex2.ketera.com>


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-l
Received on Fri Mar 16 2007 - 08:30:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US