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)
Josh, 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 http://yong321.freeshell.org/computer/ParameterDependencyAndStatistics.doc) 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? -- http://www.freelists.org/webpage/oracle-l