Re: Statistics_Level = All for Stress Testing?

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.

http://jonathanlewis.wordpress.com/2007/04/26/heisenberg/ http://jonathanlewis.wordpress.com/2007/11/25/gather_plan_statistics/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
> Behalf Of JApplewhite_at_austinisd.org
> 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 (9.2.0.4 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)
>
>

--
http://www.freelists.org/webpage/oracle-l