RE: statistics_level=ALL slows query 10x.
Date: Wed, 7 Mar 2012 18:50:02 +0000
Message-ID: <D0534F8D31056242BE8E38FA9413FDA817CB2651_at_M1EXCHMB13.mmi.local>
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<mailto: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:50:02 CST