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@xxxxxxxxxxxx>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@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> > 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> -- //www.freelists.org/webpage/oracle-l