STATSPACK

STATSPACK Utility

Step 1: Overview
Step 2: How to find statspack installed or not
Step 3: Install statspack
Step 4: Available SNAP_LEVEL
Step 5: Set timed_statistics to true
Step 6: Taking STATSPACK snapshots
Step 7: List snapshots
Step 8: Generate STATSPACK reports
Step 9: How to find current SNAP level
Step 10: Modify SNAP_LEVEL (Only if required)
Step 11: Schedule automatic STATSPACK
Step 12: View the JOB
Step 13: Purge a snapshot
Step 14: Uninstall Statspack
Step 15: Other statspack scripts


Step 1: 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.

Please note Statspack makes data collection easy to automate using either DBMS_JOB or an operating system utility to schedule collection tasks.


Step 2: How to find statspack installed or not

SQL> select * from stats$level_description;
select * from stats$level_description
              *
ERROR at line 1:
ORA-00942: table or view does not exist <-- STATSPACK not installed before

SQL>


Step 3: Install statspack

SQL> CREATE TABLESPACE perfstat
DATAFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/w148p/perfstat.dbf' SIZE 500M
autoextend on maxsize 2G;  2    3

Tablespace created.

SQL>

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba 
@spcreate.sql     -- Enter tablespace names when prompted
-- Please note user PERFSTAT automatically will get create. No need to create manually(tested in 10gR2).

Output

[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -ltr spcreate.sql
-rw-r--r-- 1 oracle oinstall 861 May 17  2002 spcreate.sql
[oracle@rac1 admin]$

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Oct 27 13:05:56 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spcreate.sql <---

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: PERFSTAT <----
PERFSTAT


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT <----

Using tablespace PERFSTAT as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP <----

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user  ---


... Installing required packages


... Creating views


... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

...
--- trimed text content
--- trimed text content
...

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>


Step 4: Available SNAP_LEVEL

There are five snap shot levels (0, 5, 6, 7, 10) with 5 being the default.

SQL> set pages 999 lines 180
SQL> col DESCRIPTION for a60
SQL> select * from stats$level_description;

SNAP_LEVEL DESCRIPTION
---------- ------------------------------------------------------------
         0 This level captures general statistics, including rollback s
           egment, row cache, SGA, system events, background events, se
           ssion events, system statistics, wait statistics, lock stati
           stics, and Latch information

         5 This level includes capturing high resource usage SQL Statem
           ents, along with all data captured by lower levels

         6 This level includes capturing SQL plan and SQL plan usage in
           formation for high resource usage SQL Statements, along with
            all data captured by lower levels

         7 This level captures segment level statistics, including logi
           cal 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


5 rows selected.

SQL>


Step 5: Set timed_statistics to true

SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

ALTER SYSTEM SET timed_statistics = true; 
or 
ALTER SESSION SET timed_statistics = true;


Step 6: Taking STATSPACK snapshots

exec statspack.snap; <---- Default Level 5
-- Run your job ---
exec statspack.snap;

-- OR --

SQL> exec statspack.snap(i_snap_level => 7);

PL/SQL procedure successfully completed.

SQL>

-- Run your job ---

SQL> exec statspack.snap(i_snap_level => 7);

PL/SQL procedure successfully completed.

SQL>

-- OR --

For a particular session, in this case SID = 158

exec statspack.snap(i_session_id=>158); 


Step 7: List snapshots

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

NAME         SNAP_ID Date/Time
--------- ---------- --------------------
W148P              1 27-OCT-2016:13:25:41
W148P              2 27-OCT-2016:13:48:01

2 rows selected.

SQL>


Step 8: Generate STATSPACK reports

[oracle@rac1 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -ltr spreport.sql
-rw-r--r-- 1 oracle oinstall 1284 Apr 23  2001 spreport.sql
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL> @spreport.sql
-- Please note it will create the file w148p_snap_1_2.txt on current directory

Output

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL> @spreport.sql <-----

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3175692859 W148P               1 w148p



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3175692859        1 W148P        w148p        rac1.rajasek
                                               har.com

Using 3175692859 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
w148p        W148P                1 27 Oct 2016 13:25     7
                                  2 27 Oct 2016 13:48     7



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1 <----
Begin Snapshot Id specified: 1

Enter value for end_snap: 2 <----
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: w148p_snap_1_2.txt

Using the report name w148p_snap_1_2.txt

STATSPACK report for

Database    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3175692859 w148p               1 27-Oct-16 12:40 10.2.0.5.0  NO

Host  Name:   rac1.rajasekhar. Num CPUs:    1        Phys Memory (MB):    3,013
~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          1 27-Oct-16 13:25:41       17       3.9
  End Snap:          2 27-Oct-16 13:48:01       18       5.1
   Elapsed:               22.33 (mins)

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:       648M             Std Block Size:         8K
           Shared Pool Size:       212M                 Log Buffer:     5,954K

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              1,504.88            224,059.56
              Logical reads:                 16.82              2,504.00
              Block changes:                  5.33                793.56
             Physical reads:                  0.10                 15.56
            Physical writes:                  1.12                166.33
                 User calls:                  0.04                  5.22
                     Parses:                  0.93                138.11
                Hard parses:                  0.09                 13.11
                      Sorts:                  0.57                 85.56
                     Logons:                  0.01                  1.11
                   Executes:                  2.23                332.33
               Transactions:                  0.01

  % Blocks changed per Read:   31.69    Recursive Call %:    99.83
 Rollback per transaction %:    0.00       Rows per Sort:    38.26

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.38    In-memory Sort %:  100.00
            Library Hit   %:   92.10        Soft Parse %:   90.51
         Execute to Parse %:   58.44         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  125.00     % Non-Parse CPU:   96.24

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   44.32   46.56
    % SQL with executions>1:   47.98   62.40
  % Memory for SQL w/exec>1:   48.77   66.29

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                         1          62.7
db file sequential read                            145           0      2   12.6
log file parallel write                             92           0      2    9.5
control file parallel write                        486           0      0    8.4
os thread startup                                    6           0     13    3.8
          -------------------------------------------------------------
Host CPU  (CPUs: 1)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       0.00    0.00      0.41    0.42   99.14    0.05

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:    0.13
              % of busy  CPU for Instance:   15.45
  %DB time waiting for CPU - Resource Mgr:

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):      3,012.6      3,012.6
                   SGA use (MB):        876.0        876.0
                   PGA use (MB):         49.1         51.5
    % Host Mem used for SGA+PGA:         30.7         30.8
          -------------------------------------------------------------

...
--- trimed text content
--- trimed text content
...

End of Report ( w148p_snap_1_2.txt )

SQL>


Step 9: How to find current SNAP level

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7

4 rows selected.


Step 10: Modify SNAP LEVEL (Only if required)

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7

4 rows selected.

SQL> exec statspack.modify_statspack_parameter(i_snap_level=>10, i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

   SNAP_ID SNAP_LEVEL
---------- ----------
         1          7
         2          7
         3          7
         4          7
         5         10  <--- snap level changed

5 rows selected.

SQL>


Step 11: Schedule automatic STATSPACK

The collection of system snapshots can be automated with the DBMS_JOB package.
The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour (default).
$ORACLE_HOME/rdbms/admin/spauto.sql

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
[oracle@rac1 admin]$ sqlplus PERFSTAT/PERFSTAT; <---

SQL> @spauto.sql

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         1 <--- Please note this


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
         1 27-OCT-16 18:00:00

SQL>


Step 12: View the JOB

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SQL> set lines 180
SQL> col SCHEMA_USER for a20
SQL> col INTERVAL for a30
SQL> col WHAT for a30
SQL> select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;

       JOB SCHEMA_USER          INTERVAL                       B WHAT
---------- -------------------- ------------------------------ - ------------------------------
         1 PERFSTAT             trunc(SYSDATE+1/24,'HH')       N statspack.snap;

SQL>

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

NAME         SNAP_ID Date/Time
--------- ---------- --------------------
..
W148P              5 27-OCT-2016:17:00:58
W148P              6 27-OCT-2016:18:00:01
W148P              7 27-OCT-2016:19:02:40

REM Change the snap interval from 1 hr to 1/2 hour <— please test before use
REM SQL> exec dbms_job.interval(1, ‘trunc(SYSDATE+1/48,”HH”)’);
REM
—- FYI —-
REM — Every one hour from now: sysdate+1/24
REM — Every 1/2 hour from now: sysdate+1/48
REM — Every 15 Minuts from now: sysdate+15/1440
REM — Gather STATS FOR PERFSTAT SCHMEA TO SEE CHANGES —
conn PERFSTAT / PERFSTAT
select JOB, LOG_USER, LAST_DATE, NEXT_DATE, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;


Step 13: Purge a snapshot

Remove a range of snapshots using ORACLE_HOME/rdbms/admin/sppurge.sql

-- Remove all snapshots using ORACLE_HOME/rdbms/admin/sptrunc.sql ---

SQL> @sppurge.sql


Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 3175692859 W148P             1 w148p


Snapshots for this database instance
====================================

                               Base-  Snap
 Snap Id   Snapshot Started    line? Level Host            Comment
-------- --------------------- ----- ----- --------------- --------------------
       1  27 Oct 2016 16:58:00           7 rac1.rajasekhar
       2  27 Oct 2016 16:58:52           7 rac1.rajasekhar
       3  27 Oct 2016 17:00:19           7 rac1.rajasekhar
       4  27 Oct 2016 17:00:21           7 rac1.rajasekhar
       5  27 Oct 2016 17:00:58          10 rac1.rajasekhar
       6  27 Oct 2016 18:00:01          10 rac1.rajasekhar
       7  27 Oct 2016 19:02:40          10 rac1.rajasekhar



Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1
Using 1 for lower bound.

Enter value for hisnapid: 4
Using 4 for upper bound.

Deleting snapshots 1 - 4. <----

Number of Snapshots purged: 4 <--- selected 4 snapshots deleted.
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Purge of specified Snapshot range complete.


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

NAME       Snap Id Date/Time
--------- -------- --------------------
W148P            5 27-OCT-2016:17:00:58 
W148P            6 27-OCT-2016:18:00:01 
W148P            7 27-OCT-2016:19:02:40


6 rows selected.

SQL>


Step 14: Uninstall Statspack

If you decide you do not need Statspack installed any more you can remove/uninstall Statspack with ORACLE_HOME/rdbms/admin/spdrop.sql
The spdrop.sql needs be dropped by a user with SYSDBA. 
Remember to remove any jobs you might have created to manage the Statspack environment.

Output

SQL> @spdrop.sql <----
Dropping old versions (if any)

Synonym dropped.


Sequence dropped.


Synonym dropped.


Table dropped.
..
..
..
User dropped.

NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.

SQL>
SQL>
SQL>


Step 15: Other statspack scripts

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

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value
http://www.orafaq.com/wiki/Statspack

Thank you,
Rajasekhar Amudala