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

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

Re: workaround for v$sql_plan

From: A. Coskan Gundogar <gundogar_at_gmail.com>
Date: Fri, 16 Mar 2007 15:40:48 +0200
Message-ID: <d1f742de0703160640i703afcb0la67aba169a0537d4@mail.gmail.com>


I think the blog entry of Mr Jonathan Lewis may assist you

http://jonathanlewis.wordpress.com/2006/11/15/dbms_xplan-pt2/

On 3/16/07, Ankur Godambe <agodambe_at_ketera.com> 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
>

-- 
A. Coskan GUNDOGAR

Oracle DBA

http://coskan.wordpress.com

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 16 2007 - 08:40:48 CDT

Original text of this message

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