Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: workaround for v$sql_plan

From: Ankur Godambe <>
Date: Fri, 16 Mar 2007 13:26:04 -0700
Message-ID: <>

My whole motive of dumping v$sql_plan is to find any deviations in the explain plan quickly and get to the root cause of the issue. If there is no deviation then look for the wait events. As you have pointed out (storing cpu time,etc), would be helpful but would not pinpoint and say "this is the problem. Something's wrong in this part/predicate".

Wolfgang and Rjamya suggestion on setting event 10132 would be difficult to implement. And that's because there are around 600 connections to database at any given time from 8 different applications and selectively setting it for some session will not assure all the sql plans are captured and I will have to do it lots and lots of time to build a repository of sql plans. Also its production OLTP database so cannot take the risk of setting it at system level. Performance issues are quickly reported and escalated.

The bug which I might be hitting is
Bug 4434689 OERI[504] / dumps selecting from V$SQL_PLAN and it's description says: Queries on V$SQL_PLAN for SQL which used a database link or PQ can fail with ORA-600 [504] errors. This can occur at high levels of STATSPACK monitoring.

Checking if somehow I can avoid querying the sqls with db link and PQ operation. I tried CTAS for couple of hash_values which don't have db links and its working.


-----Original Message-----
From: Stephane Faroult [] Sent: Saturday, March 17, 2007 1:10 AM
To: Ankur Godambe
Subject: Re: workaround for v$sql_plan


If your aim is simply to check if there is any change (I mean Yes/No) there is somewhere (V$SQL out of memory) a plan hash value. Storing it may be enough. Actually, if I were you I think I would store it together with values such as CPU time/exec and elapsed time/exec. I don't find it shocking to see an execution plan change. After all, that's what the optimizer is here for - altering the plan when circumstances change. Which is why it might be a better idea to check circumstances rather than the plan. I have been working these last days on something that is related if not exactly similar: comparing several databases (production/development/performance test environments). My approach has been to collect V$PARAMETER minus the parameters that refer to the instance or database name (on second thoughts collecting boolean and numerical parameters should suffice), compute for each table in the applicative schemas the number of indexes as well as the minimum and maximum number of columns in the indexes, and the number of rows (as last computed by the stats), generously rounded to allow for variations, and then display the differences. It's not a 100% coverage, but it should explain most issues. A change of plan is just a symptom.

HTH Stéphane Faroult

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
> 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

Received on Fri Mar 16 2007 - 15:26:04 CDT

Original text of this message