Statspack Utility

STATSPACK: It is a diagnosis tool for instance-wide performance problems; also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem

When a snapshot is executed, the STATSPACK software will sample from the in-memory structures inside the SGA and transfer the values into the corresponding STATSPACK tables.

Installing and Configuring Statspack:

The STATSPACK utility requires a tablespace to store all of the objects and data gathered. It is suggested that the tablespace be called STATSPACK for the STATSPACK tables. Closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space.

SQL> CREATE TABLESPACE statspack
DATAFILE '/u01/ora10/mydb1 /statspack.dbf' SIZE 200M AUTOEXTED ON NEXT 10M MAXSIZE 500M;

Once the tablespace is created run the following script to generate the statspack user schema and the utility tables.

> sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spcreate

Once the above script is completed, we can set the snapshot data collection level as per our requirement, default level is 5.

SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

Snapshot Level Details:

Level 0 - GENERAL PERFORMANCE

This level can be used to gather general performance information about the database.

Level 5 - GENERAL PERFORMANCE + SQL STATEMENTS (DEFAULT)

This snapshot level will gather all the information from the previous levels, plus it will collect

performance data on high resource SQL statements. This is also the default snapshot level when

Statspack is installed.

Level 6 - GENERAL PERFORMANCE + SQL STATEMENTS + SQL PLANS AND SQL PLAN USAGE

This level is new in Oracle9i and it will include all the information collected from the previous

snapshot levels, plus execution path and plan usage information as they relate to high resource SQL

statements. This type of information can prove critical when determining if the execution path or plan

has changed for high resource SQL statements. Oracle recommends using this level for when one of the

following situations has occurred:

- A plan has possibly changed after large volumes of data have been added.

- Obtaining new optimizer setting information.

Level 10 - GENERAL PERFORMANCE + SQL STATEMENTS + SQL PLANS AND SQL PLAN USAGE + PARENT AND CHILD LATCHES

This level will include all the information collected from previous snapshot levels, plus the addition of

parent and child latch information. This level will take even longer to complete since the parent and

child latch information are added to the duration of the previous 2 levels, which are already information

gathering intensive. First, because the information gathered is based on the shared_pool_size and

secondly the volume of information gathered based on SQL statement information, plus the parent and

child latch information. Snapshots taken from this level will take even longer and it is Oracle's recommendation to only use this level when requested by Oracle technical support personnel.

It is recommended to set the timed_statistics to true BEFORE the first snapshot because it will help to establish a better baseline, otherwise another baseline will be needed AFTER it is turned on. This can be done with the Alter SYSTEM command and/or setting it in the init.ora file.

Snapshot creation:

Login to the perfstat user and execute the following command:

Manual Mode:

SQL> Connect perfstat/******
SQL> exec statspack.snap;

Automated Mode:

Automating and scheduling when to take snapshots allows for the collection of database performance information that would be beneficial for troubleshooting performance problems that occurred earlier.

The following are two ways that snapshots can be automated:

Ø Oracle's DBMS_JOB utility to schedule snapshots. This utility will be discussed in greater detail.

Ø An operating specific job scheduler. For example on Unix, shell scripts can be written and then scheduled through the CRON scheduler.

To view the snapshot details:

SQL> select name, snap_id, to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
MYDB1 4 14.09.2010:10:56:01
MYDB1 1 13.09.2004:08:48:47
MYDB1 2 13.09.2010:09:00:01
MYDB1 3 13.09.2010:09:01:48

Creating report:

sqlplus perfstat/******
SQL> @?/rdbms/admin/spreport.sql

Information on Sections of the Statspack Report:

Section(s)	What You Can Use the Section(s) for
Wait Events	Look for excessive waits and wait times; drill down to specific problems
SQL Ordered by Buffer Gets, Physical Reads, and Rows Processed	Figure out which SQL statements to tune
Instance Activity Statistics	Compare with baseline report; compute additional statistics
Tablespace and File I/O	Investigate I/O bottlenecks, identify files and tablespaces with heavy I/O
Buffer Pool	Identify specific buffer pools with high contention or I/O
Buffer Wait Statistics	Identify types of buffers with large number of buffer waits
Enqueue Activity	Investigate specific lock types that are causing the most waits
Rollback Segment Statistics and Storage	Investigate waits for rollback segment headers
Latch Activity, Latch Sleep Breakdown, Latch Miss Sources	Identify latching bottlenecks; diagnose and related problems
Library Cache	Diagnose problems with shared pool
Non-default init.ora	Look for unnecessary or problematic parameter definitions

Wait Problems and their potential fixes:

Wait Problem	Potential Fix
DB File Scattered Read	Wait for Multi-block read of a table or index (full scan): tune the code and/or cache small tables.
DB File Sequential Read	Wait for single block read of a table or index. Indicates many index reads: tune the code (especially joins).
DB File parallel read	Used when Oracle performs in parallel reads from multiple datafiles to noncontiguous buffers in memory (PGA or Buffer Cache). Similar to db file sequential read
Free Buffer	Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code.
Buffer Busy	Segment header: add freelists or freelist groups.
Buffer Busy	Data block: separate "hot" data; use reverse key indexes and/or smaller blocks.
Buffer Busy	Data block: increase initrans and/or maxtrans.
Buffer Busy	Undo header: add rollback segments or areas.
Buffer Busy	Undo block: commit more often; use larger rollback segments or areas.
Latch Free	Investigate the latch detail.
Enqueue-ST	Use LMTs or preallocate large extents.
Enqueue-HW	Preallocate extents above high-water mark.
Enqueue-TX4	Enqueue-TX4 Increase initrans and/or maxtrans on the table or index.
Enqueue-TM	Index foreign keys; check application locking of tables.
Log Buffer Space	Increase the log buffer; use faster disks for the redo logs.
Log File Switch	Archive destination slow or full; add more or larger redo logs.
Log File Sync	Commit more records at a time; use faster redo log disks or raw devices.
Direct Path Read	Used by Oracle when reading directly into PGA (sort or hash)
Direct Path Write	Used by Oracle when writing directly into PGA (sort or hash)
Idle Event	Ignore it.

Common Idle Events:

Event	Idle Event Type
Dispatcher timer	Shared server
Lock manager wait for remote message	Oracle9i Real Application Clusters
Pipe get	User process
pmon timer	Background process
PX Idle wait	Parallel query
PX Deq Credit: need buffer	Parallel query
PX Deq Credit: send blkd	Parallel query
rdbms ipc message	Background process
smon timer	Background process
SQL*Net message from client	User process
virtual Circuit status	User process

To purge the snapshots:

SQL> @?/rdbms/admin/sppurge;
Enter the Lower and Upper Snapshot ID

Removing STATSPACK from the Database:

sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spdrop.sql
SQL> drop tablespace statspack including contents and datafiles;