Don't overlook STATSPACK in Oracle 10g

by Bob Watkins | Apr 17, 2007 7:00:00 AM

Tags: Application servers, Bob Watkins, Oracle Corp., Statspack, Oracle Application Server 10g, Oracle Tips Newsletter

Takeaway: Bob Watkins passes along a tip from a local Oracle Users Group seminar: don't ignore STATSPACK in Oracle 10g. It's still there, and it has some great new measurements.

I had the privilege last month of hearing Rich Niemiec of TUSC speak about performance tuning at the Dallas Oracle Users Group. One tip he gave us was just too good not to pass on, so here it is: STATSPACK is still alive and well in Oracle 10g.

A lot of the excitement about performance tuning in Oracle 10g is about the new Automated Workload Repository (AWR) feature, and the proactive monitoring it provides through Automatic Database Diagnostic Monitor (ADDM). It is widely reported as the replacement for STATSPACK -- and it is. It does more, and it is the future of Oracle tuning, according to Mr. Niemiec.

But, he said, don't ignore STATSPACK. Not only is it still present in Oracle 10g for legacy reasons, but it was updated to include many of the new measurements captured in the AWR. While you need additional licenses for Diagnostics Pack and Tuning Pack to fully use AWR, STATSPACK is included in the price of Oracle.

Here are a few of the things that Mr. Niemiec mentioned as being new in STATSPACK in Oracle 10g:

• Time Model Statistics: Oracle's time model includes db_time, the time spent processing database requests, and various wait times, when a session is waiting for a resource. Time Model Statistics break down db_time into its components, so you can see how much time Oracle spent processing by operation type.
• Wait Event Histograms: Instead of overall counters of how many waits of each type occurred, the histogram further breaks out the count by time. You can see how many waits were less than 1ms, less than 4ms, less than 8ms, and so on. It may not be a tuning priority if you detect waits, but they're all very short.
• File Read Histograms: Like Wait Event Histograms, these break out the count of disk blocks read by the time required to read them. You can more easily see which devices are not performing well.
• Operating System Statistics: These show CPU broken down into system vs. user time and active vs. idle time, as well as bytes in and out.
• Undo Statistics: These are detailed measurements on the efficiency of the Undo segments.

The procedure for running STATSPACK hasn't changed significantly, though there are a few new options, such as setting the extent sizes of the tables built during the spcreate script.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

How to use statspack?blarman  | 04/18/07

A Lot of new featuresrbahadur@...  | 04/19/07