RE: statistics_level=ALL slows query 10x.
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 7 Mar 2012 18:50:02 +0000
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@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, March 07, 2012 10:10 AM
To: Josh Collier
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:Josh.Collier@xxxxxxxxxxxx>> 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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
