RE: workaround for v$sql_plan
- From: "Ankur Godambe" <agodambe@xxxxxxxxxx>
- To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
- Date: Fri, 16 Mar 2007 13:26:04 -0700
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.
Thanks,
Ankur
-----Original Message-----
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Saturday, March 17, 2007 1:10 AM
To: Ankur Godambe
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: workaround for v$sql_plan
Ankur,
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
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
