Re: statistics_level=ALL slows query 10x.

From: Greg Rahn <>
Date: Wed, 7 Mar 2012 10:09:36 -0800
Message-ID: <>

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 <>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

Greg Rahn  |  blog <>  |  twitter <>  |
 linkedin <>

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

Original text of this message