Oracle dbms_workload_repository

Oracle Database Tips by Donald Burleson

Manual snapshot collection and retention You can modify the snapshot collection interval using the Oracle dbms_workload_repository package.  The procedure dbms_workload_repository.modify_snapshot_settings is used in this example to modify the snapshot collection so that it occurs every 15 minutes, and retention of snapshot data is fixed at 20160 minutes: -- This causes the repository to refresh every 15 minutes -- and retain all data for 2 weeks. Exec dbms_workload_repository.modify_snapshot_settings (retention=>20160, interval=> 15); (Setting the interval parameter to 0 will disable all statistics collection.)

DBMS_WORKLOAD_REPOSITORY

Two recent and significant additions to the Oracle database's data dictionary are more granular performance statistical data and the ability to collect that data over time. Once that performance data has been collected, it is a trivial process to perform analysis and translate that raw data into meaningful information, which often yields both problem detection as well as recommendations to solve the problems. This performance repository is known as the Automatic Workload Repository (AWR) and is managed via the PL/SQL package DBMS_WORKLOAD_REPOSITORY

The AWR collects and maintains this performance data when the Oracle initialization parameter statistics_level  is set to ALL or TYPICAL (the default). Furthermore, that performance data is captured by default on the hour, called a snapshot, and kept for eight days, known as the retention period. Frequency and retention levels are fully configurable by the DBA. It is not uncommon to see snapshots taken more often and retained much longer, in some cases as much as a year. This data is kept in the SYSAUX tablespace and is the primary consumer of space within that tablespace.

Obviously the more frequent the snapshots and longer the retention period, the larger the SYSAUX space needs will be. With disk space so cheap these days, just allocate a couple of gigabytes and do not worry about it. For most databases, 500 MB will be more than sufficient.

There are two key terms with AWR: snapshots and baselines. A snapshot is a numbered collection of performance data at a given time. It represents a static point-in-time of the database as expressed in all the key performance metric metadata within the data dictionary. Snapshots are the key inputs to the Automatic Database Diagnostic Monitor (ADDM) to perform relative comparisons which represent the measured workload for the delta in time.

If StatsPack has been used in the past, this concept of a snapshot is pretty much the same. Baselines are simply a user named set of snapshots representing a time period, being either fixed or sliding. Baselines allow one to name and keep meaningful snapshots because baselines and their snapshots are not aged out of the AWR by the retention cleanup process, i.e. that data is not automatically deleted. Next to be examined is the interface that DBMS_WORKLOAD_REPOSITORY offers.

ash_report_html is a DBMS_WORKLOAD_REPOSITORY function that quite simply displays an Automated Session History or ASH report in HTML with multiple (pipelined) returned values of VARCHAR2(500).

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
L_DBID	NUMBER	IN
L_INST_NUM	NUMBER	IN
L_BTIME	DATE	IN
L_ETIME	DATE	IN
L_OPTIONS	NUMBER	IN	0
L_SLOT_WIDTH	NUMBER	IN	0
L_SID	NUMBER	IN	NULL
L_SQL_ID	VARCHAR2	IN	NULL
L_WAIT_CLASS	VARCHAR2	IN	NULL
L_SERVICE_HASH	NUMBER	IN	NULL
L_MODULE	VARCHAR2	IN	NULL
L_ACTION	VARCHAR2	IN	NULL
L_CLIENT_ID	VARCHAR2	IN	NULL
L_PLSQL_ENTRY	VARCHAR2	IN	NULL

ash_report_textis a DBMS_WORKLOAD_REPOSITORY function that displays an Automated Session History or ASH report as plain text with multiple returned values of VARCHAR2(80).

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
L_DBID	NUMBER	IN
L_INST_NUM	NUMBER	IN
L_BTIME	DATE	IN
L_ETIME	DATE	IN
L_OPTIONS	NUMBER	IN	0
L_SLOT_WIDTH	NUMBER	IN	0
L_SID	NUMBER	IN	NULL
L_SQL_ID	VARCHAR2	IN	NULL
L_WAIT_CLASS	VARCHAR2	IN	NULL
L_SERVICE_HASH	NUMBER	IN	NULL
L_MODULE	VARCHAR2	IN	NULL
L_ACTION	VARCHAR2	IN	NULL
L_CLIENT_ID	VARCHAR2	IN	NULL
L_PLSQL_ENTRY	VARCHAR2	IN	NULL

awr_diff_report_htmlis a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository (AWR) report that compares differences between two snapshots in HTML with pipelined returned values of VARCHAR2(500).

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
DBID1	NUMBER	IN
INST_NUM1	NUMBER	IN
BID1	NUMBER	IN
EID1	NUMBER	IN
DBID2	NUMBER	IN
INST_NUM2	NUMBER	IN
BID2	NUMBER	IN
EID2	NUMBER	IN

awr_diff_report_textis a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository (AWR) report that compares differences between two snapshots as plain text with multiple returned values of VARCHAR2(80).

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
DBID1	NUMBER	IN
INST_NUM1	NUMBER	IN
BID1	NUMBER	IN
EID1	NUMBER	IN
DBID2	NUMBER	IN
INST_NUM2	NUMBER	IN
BID2	NUMBER	IN
EID2	NUMBER	IN

awr_sql_report_htmlis a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository (AWR) SQL report in HTML with multiple (pipelined) returned values of VARCHAR2(500).

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
L_DBID	NUMBER	IN
L_INST_NUM	NUMBER	IN
L_BID	NUMBER	IN
L_EID	NUMBER	IN
L_SQLID	VARCHAR2	IN
L_OPTIONS	NUMBER	IN	0

awr_sql_report_text  is a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository  (AWR) SQL report as plain text with multiple returned values of VARCHAR2(120).

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
L_DBID	NUMBER	IN
L_INST_NUM	NUMBER	IN
L_BID	NUMBER	IN
L_EID	NUMBER	IN
L_SQLID	VARCHAR2	IN
L_OPTIONS	NUMBER	IN	0

create_baseline  is both a DBMS_WORKLOAD_REPOSITORY function and a procedure that defines a baseline by either its starting snapshot ID or time and its ending snapshot ID or time. As a function, it returns the snapshot ID number.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
START_SNAP_ID | START_TIME	NUMBER | DATE	IN
END_SNAP_ID | END_TIME	NUMBER | DATE	IN
BASELINE_NAME	VARCHAR2	IN
DBID	NUMBER	IN	NULL
EXPIRATION	NUMBER	IN	NULL

create_baseline_template  is an overloaded DBMS_WORKLOAD_REPOSITORY procedure, meaning it  has two forms, that defines a template for how future baselines are to be created. The first form, which creates a baseline as the period between two snapshots, is as follows:

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
START_TIME	DATE	IN
END_TIME	DATE	IN
BASELINE_NAME	VARCHAR2	IN
TEMPLATE_NAME	VARCHAR2	IN
EXPIRATION	NUMBER	IN
DBID	NUMBER	IN	NULL

The second form of create_baseline_template, which creates a baseline as the period from a user defined start time and duration, is as follows:

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
DAY_OF_WEEK	VARCHAR2	IN
HOUR_IN_DAY	NUMBER	IN
DURATION	NUMBER	IN
START_TIME	DATE	IN
END_TIME	DATE	IN
BASELINE_NAME_PREFIX	VARCHAR2	IN
TEMPLATE_NAME	VARCHAR2	IN
EXPIRATION	NUMBER	IN
DBID	NUMBER	IN	NULL

create_snapshot  is both a function and a procedure that defines a new snapshot of performance data that is to be collected. As a function, it simply returns the snapshot ID number. The valid inputs are TYPICAL (default) and ALL. Also remember that calling this action consumes disk space in the SYSAUX tablespace.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
FLUSH_LEVEL	VARCHAR2	IN	'TYPICAL'

drop_baseline  is a procedure that deletes an existing baseline. When CASCADE is set to TRUE, it also deletes the pair of associated snapshots for that baseline. Otherwise, the snapshots will remain until as such time as their date and time fall outside the retention period and are then cleaned up automatically.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
BASELINE_NAME	VARCHAR2	IN
CASCADE	BOOLEAN	IN	FALSE
DBID	NUMBER	IN	NULL

drop_baseline_template  is a procedure that simply deletes an existing baseline template.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
TEMPLATE_NAME	VARCHAR2	IN
DBID	NUMBER	IN	NULL

drop_snapshot_range  is a procedure that deletes an existing range of snapshots. The space thus freed can be utilized by future snapshots.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
LOW_SNAP_ID	NUMBER	IN
HIGH_SNAP_ID	NUMBER	IN
DBID	NUMBER	IN	NULL

modify_snapshot_settings is a DBMS_WORKLOAD_REPOSITORY procedure that permits control of three important aspects of snapshots: the frequency of the collection interval, the retention or persistence period, and the number of top SQL captured. While this call does not consume any space per se in the SYSAUX tablespace, it nonetheless defines how often snapshots are collected and for how long they are retained. Therefore, care should be taken when setting these parameters. The retention time is expressed in minutes and must be from 1 day to 100 years, where zero means keep forever. The interval is also expressed in minutes, and must be between 10 minutes and 1 year. The further apart the snapshots are spread, the harder it becomes to diagnose problems, so set accordingly.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
RETENTION	NUMBER	IN	NULL
INTERVAL	NUMBER	IN	NULL
TOPNSQL	NUMBER | VARCHAR2	IN	NULL |
DBID	NUMBER	IN	NULL

The constants for these minimum and maximum values are as follows:

 

  -- Minimum and Maximum values for the

  -- Snapshot Interval Setting (in minutes)

  MIN_INTERVAL    CONSTANT NUMBER := 10;                /* 10 minutes */

  MAX_INTERVAL    CONSTANT NUMBER := 52560000;          /* 100 years */

 

  -- Minimum and Maximum values for the

  -- Snapshot Retention Setting (in minutes)

  MIN_RETENTION   CONSTANT NUMBER := 1440;              /* 1 day */

  MAX_RETENTION   CONSTANT NUMBER := 52560000;          /* 100 years */

 

modify_baseline_window_size is a procedure that permits one to redefine the window size of a sliding or moving window baseline. The window size is expressed in number of days and must be less than or equal to the retention period.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
WINDOW_SIZE	NUMBER	IN
DBID	NUMBER	IN	NULL

rename_baselineis a procedure that simply renames an existing baseline.

 

ARGUMENT	TYPE	IN / OUT	DEFAULT VALUE
OLD_BASELINE_NAME	VARCHAR2	IN
NEW_BASELINE_NAME	VARCHAR2	IN
DBID	NUMBER	IN	NULL

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

---