Skip to main content
Delphix

Using Oracle Statspack to monitor VDB performance when AWR reports are not available

Background

Oracle has introduced the ability to disable the use of the Diagnostics and Tuning Pack which provides the ability to generate AWR reports. As the Diagnostics and Tuning packs are licenced separately, the ability to use them should be disabled in environments where these packs have not been purchased from Oracle. Use of Oracle Statspack does not require a licence above and beyond the standard Oracle licence agreement. This presents an issue should AWR reports be required to assist in the diagnosis of performance problems in Delphix virtual databases (VDBs).

Controlling the availability of AWR reports is performed through the parameter.

SQL> show parameter pack
NAME           TYPE   VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access     string   DIAGNOSTIC+TUNING

When the parameter control_management_pack_access is set to DIAGNOSTIC+TUNING AWR reports can be generated.

Should this parameter be set to NONE it has been set this way deliberately and AWR reports cannot be generated.

The value for the parameter statistics_level will also impact Oracles ability to provide performance metrics. The value for this parameter will need to be set to TYPICAL or ALL

Further information regarding these parameters and there impact can be found in the Reference links at the bottom of the article.

 

AWR behaviour when the Diagnostics and Tuning Packs are enabled

While the parameter control_management_pack_access is set DIAGNOSTIC+TUNING or DIAGNOSTIC snapshots can be manually captured and reports generated.

To generate snapshots execute dbms_workload_repository.

SQL> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

To generate an AWR report.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~
  DB Id  DB Name   Inst Num Instance
----------- ------------ -------- ------------
 3544581389 ORCL12       1 orcl121

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html'     HTML format (default)
'text'     Text format
'active-html'  Includes Performance Hub active report

Enter value for report_type: text
Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id   Inst Num DB Name   Instance   Host
------------ -------- ------------ ------------ ------------
* 3544581389    1 ORCL12    orcl121   oelc8n1.plb.internal
 3544581389    2 ORCL12    orcl122   oelc8n2.plb.internal

Using 3544581389 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.

Enter value for num_days: 1
Listing the last day's Completed Snapshots
                            Snap
Instance   DB Name    Snap Id  Snap Started  Level
------------ ------------ --------- ------------------ -----
orcl121   ORCL12       18 01 Sep 2017 00:00   1
                 19 01 Sep 2017 01:00   1
                 20 01 Sep 2017 02:00   1
                 21 01 Sep 2017 03:00   1
                 22 01 Sep 2017 04:00   1
                 23 01 Sep 2017 05:00   1
                 24 01 Sep 2017 06:00   1
                 25 01 Sep 2017 07:00   1
                 26 01 Sep 2017 08:00   1
                 27 01 Sep 2017 09:00   1
                 28 01 Sep 2017 09:45   1
                 29 01 Sep 2017 09:46   1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 28
Begin Snapshot Id specified: 28

Enter value for end_snap: 29
End  Snapshot Id specified: 29

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_28_29.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:
Using the report name awrrpt_1_28_29.txt

WORKLOAD REPOSITORY report for

DB Name     DB Id  Instance   Inst Num Startup Time  Release   RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL12    3544581389 orcl121       1 31-Aug-17 17:37 12.1.0.2.0 YES


Host Name    Platform             CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
oelc8n1.plb.inte Linux x86 64-bit          2   2    1    5.78


       Snap Id   Snap Time   Sessions Curs/Sess Instances
      --------- ------------------- -------- --------- ---------
Begin Snap:    28 01-Sep-17 09:45:08    61    1.1     2
 End Snap:    29 01-Sep-17 09:46:57    59    1.2     2
  Elapsed:        1.83 (mins)
  DB Time:        0.05 (mins)
..
.
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                      Total Wait    Wait  % DB Wait
Event                Waits Time (sec)  Avg(ms)  time Class
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                      1.3       46.3
db file sequential read         31     .3    8.73  9.9 User I/O
control file sequential read      481     .3    0.54  9.4 System I
row cache lock            1,016     .1    0.12  4.4 Concurre
gc current block 2-way         271     .1    0.39  3.9 Cluster
gc current block busy          30     .1    3.30  3.6 Cluster
library cache lock           160     .1    0.57  3.4 Concurre
library cache pin           178     .1    0.48  3.1 Concurre
gc current grant busy         116     .1    0.44  1.9 Cluster
gc cr block 2-way           105     0    0.34  1.3 Cluster
..
.
End of Report
Report written to awrrpt_1_28_29.txt
SQL>

Disabling the ability to capture AWR reports

Management of the diagnostics and tuning packs in Oracle 11g and above is performed through the database parameter control_management_pack_access. To disable this licenced functionality perform the following:

SQL> alter system set control_management_pack_access=NONE;
System altered.

An alert log entry is generated for any changes made to this parameter and can be used to track its changes.

Fri Sep 01 09:49:52 2017
ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=BOTH;

AWR behaviour when the Diagnostics and Tuning Packs are disabled.

 Snapshots can still be captured.

SQL> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

Attempts to generate reports can also be made and they will appear to run as normal.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~
  DB Id  DB Name   Inst Num Instance
----------- ------------ -------- ------------
 3544581389 ORCL12       1 orcl121

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.

'html'     HTML format (default)
'text'     Text format
'active-html'  Includes Performance Hub active report

Enter value for report_type: text
Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id   Inst Num DB Name   Instance   Host
------------ -------- ------------ ------------ ------------
* 3544581389    1 ORCL12    orcl121   oelc8n1.plb.internal
 3544581389    2 ORCL12    orcl122   oelc8n2.plb.internal

Using 3544581389 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.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                            Snap
Instance   DB Name    Snap Id  Snap Started  Level
------------ ------------ --------- ------------------ -----
orcl121   ORCL12       18 01 Sep 2017 00:00   1
                 19 01 Sep 2017 01:00   1
                 20 01 Sep 2017 02:00   1
                 21 01 Sep 2017 03:00   1
                 22 01 Sep 2017 04:00   1
                 23 01 Sep 2017 05:00   1
                 24 01 Sep 2017 06:00   1
                 25 01 Sep 2017 07:00   1
                 26 01 Sep 2017 08:00   1
                 27 01 Sep 2017 09:00   1
                 28 01 Sep 2017 09:45   1
                 29 01 Sep 2017 09:46   1
                 30 01 Sep 2017 09:50   1
                 31 01 Sep 2017 09:53   1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 30
Begin Snapshot Id specified: 30

Enter value for end_snap: 31
End  Snapshot Id specified: 31

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_30_31.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:
Using the report name awrrpt_1_30_31.txt

The report however will be useless as it will not contain any usable data with statistics showing 0 values or the message "No data exists for this section of the report" for the bulk of the sections in the report.

WORKLOAD REPOSITORY report for

DB Name     DB Id  Instance   Inst Num Startup Time  Release   RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL12    3544581389 orcl121       1 31-Aug-17 17:37 12.1.0.2.0 YES

Host Name    Platform             CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
oelc8n1.plb.inte Linux x86 64-bit                    .00

       Snap Id   Snap Time   Sessions Curs/Sess
      --------- ------------------- -------- ---------
Begin Snap:    30 01-Sep-17 09:50:06
 End Snap:    31 01-Sep-17 09:53:04
  Elapsed:        2.95 (mins)
  DB Time:        0.00 (mins)

Load Profile          Per Second  Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~      ---------------  --------------- --------- ---------
       DB Time(s):        0.0        0.0   0.00   0.00
       DB CPU(s):        0.0        0.0   0.00   0.00
   Background CPU(s):     0.00
..
.
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                      Total Wait    Wait  % DB Wait
Event                Waits Time (sec)  Avg(ms)  time Class
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                       0       100.0
..
.
Foreground Wait Events          DB/Inst: ORCL12/orcl121 Snaps: 30-31


         No data exists for this section of the report.
             ------------------------------------------------------
..
.
End of Report
Report written to awrrpt_1_30_31.txt

Enabling Oracle Statspack in environments where AWR is not available

In Environments where access to the diagnostics and tuning packs has been disabled due to licence restrictions tuning Oracle and capturing metrics around database performance will need to be executed through the use of the Statspack report. Oracle Statspack is the mechanism DBA's used to tune Oracle database prior to the existence of the Automatic Workload Repository and AWR reports.

Unfortunately it is not installed by default and will need to be created.

Enabling Statspack and the collection of performance metrics through it should only be done with the consent of the administrator of the VDB.

Configuring Oracle Statspack

Check to see if the perstat schema exists, the existence of PERFSTAT user and objects like STATS$SYSSTAT will likely indicate that Statspack schema and repository has already been built into the database.

SQL> select username from dba_users where username='PERFSTAT';

USERNAME
--------------------------------------------------------------------------------
PERFSTAT

SQL> col object_name format a15
SQL> select object_name,object_type 
   from dba_objects where owner='PERFSTAT' and object_name='STATS$SYSSTAT';

OBJECT_NAME   OBJECT_TYPE
--------------- -----------------------
STATS$SYSSTAT  TABLE

To build statspack into an Oracle Database where licencing requirements prohibit the use of AWR perform the following:

Execute the spcreate script provided by Oracle. This will create a new user and schema in the database called perfstat. 

[oracle@oelc8n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 1 10:09:03 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: delphix
delphix

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME        CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
SYSAUX             PERMANENT *
USERS             PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: SYSAUX

Using tablespace SYSAUX as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME        CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP              TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
..
.
Creating Package STATSPACK...
Package created.

No errors.
Creating Package Body STATSPACK...
Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Capturing Statspack Snapshots

Generating Statspack reports, like AWR reports relies on snapshot of the databases internal views to capture metrics at a specific point in time and the comparison of two snapshots to determine what database performance looked like across a defined period of time.

Capturing snapshots can be performed automatically on a per hourly basis. Executing the Oracle supplied script spauto.sql will establish a job that performs this hourly snapshot.

 SQL> @?/rdbms/admin/spauto
SQL> Rem
SQL> Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
..
.
PL/SQL procedure successfully completed.
..
.
SQL> prompt  Next scheduled run
Next scheduled run
SQL> prompt  ~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~
SQL> prompt  The next scheduled run for this job is:
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
 2  from user_jobs
 3  where job = :jobno;

    JOB NEXT_DATE NEXT_SEC
---------- --------- --------
   1 01-SEP-17 11:00:00

1 row selected.

SQL> 
SQL> spool off;
Capturing Snapshots manually

Manually capturing the snapshots for Statspack reports can also be performed using:

SQL> exec perfstat.statspack.snap;

PL/SQL procedure successfully completed.

Execute the workload against the database...

SQL> exec perfstat.statspack.snap;

PL/SQL procedure successfully completed.
Generating a Statspack report

Oracle supplies the script spreport for creating Statspack reports. A report requires to snapshots be provided across which the databases performance can be examined. Ideally this time frame will cover the period in which the performance problem was occurring.

SQL> @?/rdbms/admin/spreport
..
.
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id  Inst Num DB Name   Instance   Host
----------- -------- ------------ ------------ ------------
 3544581389    1 ORCL12    orcl121   oelc8n1.plb.internal
                        
Using 3544581389 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
------------ ------------ --------- ----------------- ----- --------------------
orcl121   ORCL12        1 01 Sep 2017 10:20   5
                 2 01 Sep 2017 10:22   5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End  Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: sp_1_2.txt
..
.
Using the report name sp_1_2.txt

STATSPACK report for

Database  DB Id  Instance   Inst Num Startup Time  Release   RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
     3544581389 orcl121       1 31-Aug-17 17:37 12.1.0.2.0 YES

Host Name       Platform        CPUs Cores Sockets  Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
   oelc8n1.plb.inte Linux x86 64-bit      2   2    1     5.8

Snapshot    Snap Id   Snap Time   Sessions Curs/Sess Comment
~~~~~~~~  ---------- ------------------ -------- --------- ------------------
Begin Snap:     1 01-Sep-17 10:20:44    61    1.2
 End Snap:     2 01-Sep-17 10:22:20    61    1.2
  Elapsed:    1.60 (mins) Av Act Sess:    0.0
  DB time:    0.04 (mins)   DB CPU:    0.02 (mins)
..
.
Top 5 Timed Events                          Avg %Total
~~~~~~~~~~~~~~~~~~                          wait  Call
Event                      Waits  Time (s)  (ms)  Time
----------------------------------------- ------------ ----------- ------ ------
lreg timer                     32     96  3003  19.1
heartbeat redo informer               95     95  1000  18.9
Streams AQ: load balancer idle           19     95  4992  18.9
AQPC idle                      3     90 30007  17.9
REPL Capture/Apply: RAC AQ qmn coordinato      1     76 76145  15.1
     -------------------------------------------------------------
..
.
End of Report ( sp_1_2.txt )

 The report will be written to the directory you were sitting in at the time sqlplus was invoked.

SQL> ! pwd
/home/oracle

SQL> ! ls -l sp_1_2.txt
-rw-r--r--. 1 oracle oinstall 167870 Sep 1 10:24 sp_1_2.txt
Including SQL Execution Plans in the Statspack report

Should execution plans for SQL be required and produced in reports snapshots need to be captured with the snapshot metrics level set to 6. Use the following command to increase the level of detail available in the snapshots and subsequent reports.

exec statspack.modify_statspack_parameter(i_snap_level=>6);