Re: workaround for v$sql_plan
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: agodambe@xxxxxxxxxx
- Date: Fri, 16 Mar 2007 20:39:38 +0100
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:
