Using Oracle Statspack to monitor VDB performance when AWR reports are not available (KBA1362)
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);