Home  >  Oracle Database Administration (DBA)  >  Database Tuning  >  Statspack Utility

STATSPACK Utility Overview

     STATSPACK is a performance diagnosis tool, available since Oracle8i: Oracle 8.1.6 introduced statspack as a replacement for the UTLBSTAT/UTLESTAT scripts. The Statspack package is a set of SQL, PL/SQL and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Using Statspack we can collect statistics which are put in specific tables. When we need, we can run reports based on these tables (snapshots) to tune the database. 

Installing and Configuring STATSPACK

1. Connect to the database as sysdba (and create the PERFSTAT user (statspack owner) if the user doesn't exit):

    a)  sqlplus /nolog;

   b)  connect / as sysdba

    c)  create the PERFSTAT user if it is not created

2. Set the default tablespaces (the tablespaces must exist if not must be created):

     a) define default_tablespace = 'TOOLS'

     b) define temporary_tablespace = 'TEMP'

3. Run the spcreate script:

     @?/rdbms/admin/spcreate

4. Set timed_statistics to true:

ALTER SYSTEM SET timed_statistics = true; 

or 
ALTER SESSION SET timed_statistics = true;

5. Set the "level" statspack parameter (In this example the "level" parameter is set to 6):

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

Select the STATSPACK Collection Level

     When the snapshots are taken, the collected information is in function of the statspack settings (the level parameter controls the type of data collected). Here is a table which show the information which is collected in function of the "level" statspack parameter:

To see the current statspack level we can use the following select:

        SELECT * FROM stats$level_description ORDER BY snap_level;

Taking STATSPACK snapshots

     The snapshots could be taken every 30 minutes, but the interval is fixed in function of the needs and database usage. For this purpose we can use the statspack.snap procedure:

  exec statspack.snap;    (for the database)

  OR

  execute statspack.snap(i_session_id=>13);     (for a particular session, in this case SID = 13)

Generate STATSPACK reports

1. To generate a report for the database:

     @?/rdbms/admin/spreport.sql

      We have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)

2. To generate a report for a particular statement:

    @?/rdbms/admin/sprepsql.sql

    We have to provide at the prompt: BEGIN_SNAP, END_SNAP, Hash_Value, REPORT_NAME (we can use a default value as well)

  NOTE: The reports could be run in batch by providing the responses before the execution of the reports. Here is an example:

  Connected as perfstat run:

  define begin_snap=10
  define end_snap=211
  define report_name=batch_run
  @?/rdbms/admin/spreport

STATSPACK Maintenance 

1. View snapshot details in the current database:

      SELECT name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')"Date/Time", SESSION_ID, SERIAL#

      FROM stats$snapshot,v$database;

2. Gather statistics on PERFSTAT schema to create reports faster:

      execute dbms_stats.gather_schema_stats('PERFSTAT', DBMS_STATS.AUTO_SAMPLE_SIZE);
          or 
      execute dbms_stats.gather_schema_stats('PERFSTAT');

3. Delete old snapshot:

      @?/rdbms/admin/sppurge;     (after that Enter the Lower and Upper Snapshot ID )

More information about  this subject ( Statspack Utility ) you can get from  www.in-oracle.com

Top 5 Wait Events

When you are trying to eliminate bottlenecks on your system, your Statspack report's Top 5 Wait Events section is the first place to look. This section of the report shows the top 5 wait events, the full list of wait events, and the background wait events. If your system's TIMED_STATISTICS initialization parameter is set to true, the events are ordered in time waited, which is preferable, since all events don't show the waits. If TIMED_STATISTICS is false, the events are ordered by the number of waits.

Listing 1shows a large number of waits related to reading a single block (db file sequential read) as well as waits for latches (latch free). You can see in this listing high waits for some of the writing to datafiles and log files. To identify which of these are major issues, you must narrow down the list by investigating the granular reports within other sections of Statspack.

Resolving Your Wait Events

The following are 10 of the most common causes for wait events, along with explanations and potential solutions:

1. DB File Scattered Read.This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform. a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

More information you get get from here as well.