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.
| L | Information Collected |
| 0 | General Performance Statistics |
| 5 | Addition Data: SQL Statements |
| 6 | Addition Data: SQL Plans and SQL Plan Usage |
| 7 | Addition Data: Segment Level Statistics |
| 10 | Addition Data: Parent and Child Latches |
SQL> execute statspack.snap(i_snap_level=>7);- 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 options2. 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_11How 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:00This 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.