Re: statistics_level=ALL slows query 10x.

From: Greg Rahn <greg_at_structureddata.org>
Date: Wed, 7 Mar 2012 10:09:36 -0800
Message-ID: <CAGXkmisp0uiAF7AqJNmRj77-QUH35qr5weDqPjic2M0u0yMOQg_at_mail.gmail.com>

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>


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

Received on Wed Mar 07 2012 - 12:09:36 CST