Oracle Statspack

Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Oracle's Statspack replaces utlbstat.sql and utlestat.sql.

Some of the other statspack scripts are:

Ø sppurge.sql - Purge (delete) a range of Snapshot Id's between the specified begin and end Snap Id's

Ø spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics

Ø  spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).

Ø spdrop.sql - Deinstall STATSPACK from database (Run as SYS)

Ø spreport.sql - Report on differences between values recorded in two snapshots

Ø sptrunc.sql - Truncates all data in Statspack tables

Perfstat user has the following tables that can be queried in order to get information about snapshots:

·        STATS$BG_EVENT_SUMMARY

·        STATS$BUFFER_POOL_STATISTICS

·        STATS$DATABASE_INSTANCE

·        STATS$ENQUEUESTAT

·        STATS$FILESTATXS

·        STATS$IDLE_EVENT

·        STATS$LATCH

·        STATS$LATCH_CHILDREN

·        STATS$LATCH_MISSES_SUMMARY

·        STATS$LATCH_PARENT

·        STATS$LEVEL_DESCRIPTION

·        STATS$LIBRARYCACHE

·        STATS$PARAMETER

·        STATS$ROLLSTAT

·        STATS$ROWCACHE_SUMMARY

·        STATS$SESSION_EVENT

·        STATS$SESSTAT

·        STATS$SGA

·        STATS$SGASTAT

·        STATS$SNAPSHOT

·        STATS$SQLTEXT

·        STATS$SQL_STATISTICS

·        STATS$SQL_SUMMARY

·        STATS$STATSPACK_PARAMETER

·        STATS$SYSSTAT

·        STATS$SYSTEM_EVENT

·        STATS$TEMPSTATXS

·        STATS$WAITSTAT

Installation :

The Statspack package shouldn’t be installed on the SYSTEM tablespace. Use a separate  tablespace for it using the default initial and next extent size of 1 Mbytes. The minimum default space requirement is approximately 60 Mbytes, however the amount of space required is difficult to estimate because it is based on the amount of data collected, the size of the database and also the instance. You should create the temporary and permanent tablespace to install the StatsPack before because if you install it on interactive mode, you will be prompted to input those tablespaces (the tablespaces that will be used by PERFSTAT user).

 

Altering the default statistic level

When taking a snapshot, a statistic level can be given that determines the amount of statistics gathered. The higher the statistic level, the more data is collected.

The following descriptions were extracted using 

select * from perfstat.stats$level_description:

• 0: This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
• 5: This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
• 6: This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
• 7: This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
• 10: This level includes capturing Child Latch statistics, along with all data captured by lower levels

Additionally, level 5 and level 10 captures sql statement that exceed any of the following adjustable thresholds:

• The number of executions of the SQL statement (adjustable with the i_executions_th parameter)
• The number of disk reads the SQL statement performs (adjustable with the i_disk_reads_th parameter)
• The number of parse calls the SQL statement performs (adjustable with the i_parse_calls_th parameter)
• The number of buffer gets the SQL statement performs (adjustable with the i_buffer_gets_th parameter)

Table A-1 Levels of Statistics
Level	Information Collected
0	General Performance Statistics
5	Addition Data: SQL Statements
6	Addition Data: SQL Plans and SQL Plan Usage
7	Addition Data: Segment Level Statistics
10	Addition Data: Parent and Child Latches

Defaults are stored in stats$statspack_parameters and can be changed with  statspack.modify_statspack_parameter.

The statistic level is passed with the i_snap_level parameter:

begin

  statspack.snap(i_snap_level=> 10);

end;

/

Installation of the Oracle Statspack tool is a relatively simple process. The following is a step-by-step guide to the process of installing Oracle Statspack on a UNIX system.

STEP 1:  Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

STEP 2:  Start the Statspack install script, spcreate.sql, as follows:

# sqlplus "/ as sysdba" @spcreate.sql

STEP 3:  Enter a password for the PERFSTAT user when prompted.

STEP 4:  Enter the default tablespace (tools) for the PERFSTAT user when prompted.

STEP 5:  Enter the temporary tablespace (temp) for the PERFSTAT user when prompted.

To run a Statspack report.

Step 1:  Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

Step 2: Run the standard Statspack report as follows:

# sqlplus perfstat/<password> @spreport

·        Enter a beginning snapshot ID.

·        Enter an ending snapshot ID.

·        Enter a name for the report or accept the default.

Step 3:  View the report using more/vi

                          THANK YOU