In Oracle : Oracle Statspack Utility

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Statspack Utility

 

 Oracle Statspack Utility

 

 

Here are the main topics of this article:

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:

 

 

Level Collected Information
0 General statistics: rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, Latch information.
5

(Default level)

Information from Level 0 plus:

high resource usage SQL Statements

6 Information from Level 5 plus:

SQL plan and SQL plan usage information for high resource usage SQL Statements

7 Information from Level 6 plus:

segment level statistics (including logical and physical reads), row lock, buffer busy waits

10 Information from Level 7 plus:

Child Latch statistics

 

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 )

 

 

Removing STATSPACK from the database

 

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

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Statspack Utility

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.