Re: workaround for v$sql

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 16 Mar 2007 08:51:03 -0600
Message-Id: <20070316145021.54FD364823B@turing.freelists.org>

I am not sure how much overhead the following would introduce in a production system. Shouldn't be all that much but you could get big trace files if there is a lot of hard parsing:

http://jonathanlewis.wordpress.com/2006/11/27/event-10132/

At 07:30 AM 3/16/2007, 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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 16 2007 - 09:51:03 CDT

Re: workaround for v$sql_plan