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: Stephane Faroult <>
Date: Fri, 16 Mar 2007 20:39:38 +0100
Message-ID: <>


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 - 14:39:38 CDT

Original text of this message