Re: Statistics_Level = All for Stress Testing?

From: Jonathan Lewis <>
Date: Sun, 23 Mar 2008 17:38:48 -0000
Message-ID: <010a01c88d0c$c06297f0$0200a8c0@Primary>

The overhead on statistics_level = all may be more platform dependent than anything else, although the nature of the execution plan may also make a big difference to the overhead.

With statistics_level = all, you get rowsource execution statistics collected with a 100% sample rate - and this can result in a very large number of calls to the system timer. On a platform where this call is expensive, the CPU increase can be significant. See the blog entries below for an example where a query's CPU jumped by a factor of more than 3, and a comment about the timer impact.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

> -----Original Message-----
> From: [] On
> Behalf Of
> Sent: Thursday, March 20, 2008 8:37 PM
> To: oracle-l
> Subject: Statistics_Level = All for Stress Testing?
> Anyone use this setting in a Dev or Test environment to bring out the worst in
> your Developers' SQL?
> Background is that I set Statistics_Level = All in our Production Student
> Information database ( on 64bit Linux)
> to do some tracing and analysis with HotSOS Profiler over Spring Break last
> week and didn't change it back to Typical
> before School restarted on Monday. I hadn't yet searched MetaLink and found
> all the problems associated with the All
> setting, so didn't even think about it until we'd fought maxed-out CPUs for a
> day and a half and had a bunch of
> frustrated Users who couldn't use our applications. Once I finally remembered
> that I'd done that and set it back to
> Typical, CPU went way down to normal levels.
> In the course of trying to figure out what the heck was going wrong with our
> previously fairly nicely behaved apps, we
> found instances of inefficient SQL in several apps. Adding an index and hint
> or two here and there worked wonders and
> made performance noticeably better even after Statistics_Level was set back to
> Typical. That got me to thinking that
> maybe I should use the All setting in our Dev and Test databases to bring out
> the worst in new/revised apps and tune
> them before they go to Production.
> Does that seem reasonable or does the All setting only affect certain kinds of
> SQL or be skewed some other way to not
> provide the kind of general stress-testing I'm envisioning?
> Thanks.
> Jack C. Applewhite - Database Administrator
> Austin I.S.D.
> 414.9715 (phone) / 935.5929 (pager)

Received on Sun Mar 23 2008 - 12:38:48 CDT

Original text of this message