Skip to content

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.