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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of JApplewhite@xxxxxxxxxxxxx 
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

Other related posts: