Using STATSPACK in Oracle 12c

Statspack is a performance tuning tool provided by Oracle. The way to use STATSPACK in Oracle is to bracket the performance problem by getting a snapshot before the issue begins and after the issue ends. This task can be tricky. You may not be able to predict when the performance problem occurs.

LInformation Collected
0General Performance Statistics
5Addition Data: SQL Statements
6Addition Data: SQL Plans and SQL Plan Usage
7Addition Data: Segment Level Statistics
10Addition Data: Parent and Child Latches
SQL> execute statspack.snap(i_snap_level=>7);
  1. Log in to SQL*Plus from the operating system as the new PERFSTAT user with the password you chose during installation and type
sqlplus perfstat
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 17:33:22 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2. To take your beginning snapshot

exec statspack.snap
PL/SQL procedure successfully completed.

3. After the report finishes running, take another snapshot the same way you did before by typing

exec statspack.snap
PL/SQL procedure successfully completed.

4. Next, you need to generate your STATSPACK report. The STATSPACK in Oracle generates STATSPACK Report with all the operational statistics during the time period between the two snapshots so you can see where the system focused its time.

@?/rdbms/admin/spreport
~~~~~~~~~~~~~~~~
  DB Id  DB Name   Inst Num Instance
----------- ------------ -------- ------------
 3615982967 DEV12C       1 dev12c
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id  Inst Num DB Name   Instance   Host
----------- -------- ------------ ------------ ------------
 3615982967    1 DEV12C    dev12c    orasvr01
Using 3615982967 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 <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
                            Snap
Instance   DB Name    Snap Id  Snap Started  Level Comment
------------ ------------ --------- ----------------- ----- --------------------
dev12c    DEV12C        1 19 Jul 2013 17:34   5
                 11 19 Jul 2013 17:38   5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Enter number 1 for the begin snap from the list shown above.
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_11. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
test_report_snaps_1_11

How to schedule snapshots in Oracle 12c

The method for taking snapshots and generating reports is handy when you have a predictive performance issue or a situation where the problem is easily repeatable. You can take the snapshots and interpret the results.

To use the default time of one hour to automatically schedule snapshots to be taken:

Log in to SQL*Plus from the operating system as the new PERFSTAT user with the password you chose during installation and type

sqlplus perfstat
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 17:37:41 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
@?/rdbms/admin/spauto
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
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   1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
    JOB NEXT_DATE NEXT_SEC
---------- --------- --------
     1 19-JUL-13 19:00:00

This output shows that the next execution of an automatic snapshot will occur at 19:00 hours and every hour thereafter.

If you upgrade to the Diagnostic pack, you can use AWR, which automatically schedules and purges snapshots based on pre-configured settings.