Re: statistics_level=ALL slows query 10x.

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: Josh.Collier@xxxxxxxxxxxx
  • Date: Thu, 8 Mar 2012 08:05:01 -0800 (PST)


As Greg pointed out, it's almost always a good idea to set 
statistics_level only at session level for the session that needs it. If 
you can't inject "alter session" into a session, consider a logon trigger.

You may only need the extended plan statistics for SQL tuning purposes, 
not everything else implicitly enabled by setting statistics_level to all. 
(Most of those "side effects" of setting it to all are shown in part II 
and appendix II of

I don't know how to explain why setting statistics_level to all eliminates 
recursive calls. Is it reproducible, with mixed order of setting the 
parameter to typical and all?

Yong Huang

> 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
> When statistics_level=ALL it show no recursive sql and all CPU time.  
> What is the next thing to trace to find out where the time is being spent
> when statistics_level=ALL?

Other related posts: