Re: statistics_level=ALL slows query 10x.

From: Greg Rahn <greg_at_structureddata.org>
Date: Wed, 7 Mar 2012 11:24:13 -0800
Message-ID: <CAGXkmiu1VNOkbaRaKaYc3N6bCx-iHTgj1v--xKvZrSJTRjXv-w_at_mail.gmail.com>

It is related to Linux - thought I conveyed that fact. Sorry if I was unclear. Has to do with the way Linux gets time (fast timer implementing).  Solaris has a "cheap" fast timer call.
See
http://jonathanlewis.wordpress.com/2007/11/25/gather_plan_statistics/ http://jonathanlewis.wordpress.com/2007/04/26/heisenberg/

On Wed, Mar 7, 2012 at 10:50 AM, Josh Collier <Josh.Collier_at_banfield.net>wrote:

> It must be related to linux, I have an identical database on solaris
> that does not show this behavior. I am curious to find the specific
> cause. It is also strange that the query emits no recursive sql when stats
> level is set to ALL. ****
>
> ** **
>
> *From:* Greg Rahn [mailto:greg_at_structureddata.org]
> *Sent:* Wednesday, March 07, 2012 10:10 AM
> *To:* Josh Collier
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: statistics_level=ALL slows query 10x.****
>
> ** **
>
> That is really the expected behavior, especially on Linux. The simple
> reason is the cost of the timing instrumentation in Linux. Never
> set statistics_level=ALL for a database, only at a session/query level (or
> use /*+ gather_plan_statistics */ ), and then it would be for capturing the
> actual number of rows for the row sources - performance should not be
> compared. Better yet, in 11.2, use SQL Monitoring if licensed for it.****
>
> ** **
>
> On Wed, Mar 7, 2012 at 9:59 AM, Josh Collier <Josh.Collier_at_banfield.net>
> wrote:****
>
> I have a database that when statistics_level=ALL is set, queries become
> 10x slower, spending all their time on CPU. I have run 10046 trace and
> verified that the plans are the same when I change this setting to TYPICAL.
> The reference query I have chosen is indicative of a set of batch queries
> that are all affected by this issue. I want to be able to use
> statistics_level=ALL periodically in production for performance diagnosis,
> this issue is making that impossible.
> The 10046 when statistics_level=TYPICAL shows lots of recursive sql. When
> statistics_level=ALL it show no recursive sql and all CPU time. Symptoms
> are the same when reference query is run serial or parallel. I have
> eyeballed the trace file and find no wait events emitted when the ALL query
> is on CPU.
>
>
> What is the next thing to trace to find out where the time is being spent
> when statistics_level=ALL?
> RH linux 64 update 4
> 11.2.0.2.4****
>
> ** **
>
> -- ****
>
> Regards,****
>
> Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter<http://bit.ly/v733dJ> |
> linkedin <http://linkd.in/gregrahn>****
>

-- 
Regards,
Greg Rahn  |  blog <http://bit.ly/u9N0i8>  |  twitter <http://bit.ly/v733dJ>  |
 linkedin <http://linkd.in/gregrahn>


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

Received on Wed Mar 07 2012 - 13:24:13 CST