The way to use Oracle 12c 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
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
To take your beginning snapshot, type
exec statspack.snap
You see the following:
PL/SQL procedure successfully completed.
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
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
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:
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:
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:
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
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
1Job 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:00This 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.
