How to Take Snapshots with Oracle 12c's STATSPACK - dummies

How to Take Snapshots with Oracle 12c’s STATSPACK

By Chris Ruel, Michael Wessler

The way to use Oracle 12c’s STATSPACK 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.

Or, perhaps the problem already occurred, and you can’t repeat it because you’re concerned about adding further overhead to the system or the problem happens only on a pre-scheduled time, such as with a batch job.

First assume that the problem is something for which you can easily create snapshots. For example, there is a report that runs at 3 p.m. every day. Normally, the report runs in five minutes. However, the last few days, the report has taken over an hour.

To get snapshots that bracket the report, you must take a snapshot right before the report starts and another snapshot right after the report ends. This effort ensures that the statistics the snapshot collects are specific to the time period and any major operations that were occurring.

How to bracket an issue with Oracle 12c’s STATSPACK

  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

    You see this:

    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, type

    exec statspack.snap

    You see the following:

    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

    You see this:

    PL/SQL procedure successfully completed.

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

How to generate your Oracle 12c STATSPACK report

  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

    You see this:

    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
  2. Type

    @?/rdbms/admin/spreport

    You see something like the following:

    ~~~~~~~~~~~~~~~~
      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.

    You see something like this:

    Begin Snapshot Id specified: 1
    Enter value for end_snap:
  3. Enter number 11 from the preceding list.

    Because you have only two snapshots at this time, this step is fairly straightforward.

    You see this:

    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:
  4. Enter a name for the report. For this example, type

    test_report_snaps_1_11

    After pressing Enter, your screen scrolls through a lot of information. Don’t worry about reading it at this time; it’s all going into the report you specified.

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:

  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

    You see this:

    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
  2. Type

    @?/rdbms/admin/spauto

    You see something like the following:

    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.

Running this script does three things: It lists all your snapshots, asks for a beginning snapshot and ending snapshot, and deletes the range you specify. Out of the box, there is no way to automate this. If you upgrade to the Diagnostic pack, you can use AWR, which automatically schedules and purges snapshots based on preconfigured settings.