106
DBMS_WORKLOAD_REPOSITORY

The DBMS_WORKLOAD_REPOSITORY package lets you manage the Workload Repository, performing operations such as managing snapshots and baselines.

The chapter contains the following topic:

• Summary of DBMS_WORKLOAD_REPOSITORY Subprograms

---

Summary of DBMS_WORKLOAD_REPOSITORY Subprograms

Table 106-1  DBMS_WORKLOAD_REPOSITORY Package Subprograms  

Subprogram	Description
AWR_REPORT_HTML Function	Displays the AWR report in HTML
AWR_REPORT_TEXT Function	Displays the AWR report in text
CREATE_BASELINE Function and Procedure	Creates a single baseline
CREATE_SNAPSHOT Function and Procedure	Creates a manual snapshot immediately
DROP_BASELINE Procedure	Drops a range of snapshots
DROP_SNAPSHOT_RANGE Procedure	Activates service
MODIFY_SNAPSHOT_SETTINGS Procedure	Modifies the snapshot settings.

---

AWR_REPORT_HTML Function

This table function displays the AWR report in HTML.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
   l_dbid       IN    NUMBER,
   l_inst_num   IN    NUMBER,
   l_bid        IN    NUMBER,
   l_eid        IN    NUMBER,
   l_options    IN    NUMBER DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

Table 106-2  AWR_REPORT_HTML Parameters

Parameter	Description
l_dbid	The database identifier.
l_ins_num	The instance number.
l_bid	The 'Begin Snapshot' Id
l_eid	The 'End Snapshot' Id
l_options	A flag to specify to control the output of the report. Currently, Oracle supports one value : l_options - 8. Displays the ADDM specific portions of the report. These sections include the Buffer Pool Advice, Shared Pool Advice, PGA Target Advice, and Wait Class sections.

Return Values

The output will be one column of VARCHAR2(150).

Usage Notes

You can call the function directly but Oracle recommends you use the awrrpt.sql script which prompts users for the required information.

---

AWR_REPORT_TEXT Function

This table function displays the AWR report in text.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
   l_dbid       IN    NUMBER,
   l_inst_num   IN    NUMBER,
   l_bid        IN    NUMBER,
   l_eid        IN    NUMBER,
   l_options    IN    NUMBER DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

Table 106-3  AWR_REPORT_TEXT Parameters

Parameter	Description
l_dbid	The database identifier.
l_ins_num	The instance number.
l_bid	The 'Begin Snapshot' Id
l_eid	The 'End Snapshot' Id
l_options	A flag to specify to control the output of the report. Currently, Oracle supports one value : l_options - 8. Displays the ADDM specific portions of the report. These sections include the Buffer Pool Advice, Shared Pool Advice, PGA Target Advice, and Wait Class sections.

Return Values

The output will be one column of VARCHAR2(80).

Usage Notes

You can call the function directly but Oracle recommends you use the awrrpt.sql script which prompts users for the required information.

---

CREATE_BASELINE Function and Procedure

This function and procedure creates a baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
   start_snap_id    IN  NUMBER,
   end_snap_id      IN  NUMBER,
   baseline_name    IN  VARCHAR2,
   dbid             IN  NUMBER DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
   start_snap_id    IN  NUMBER,
   end_snap_id      IN  NUMBER,
   baseline_name    IN  VARCHAR2,
   dbid             IN  NUMBER DEFAULT NULL)
 RETURN NUMBER;

Parameters

Table 106-4  CREATE_BASELINE Parameters

Parameter	Description
start_snap_id	The start snapshot sequence number.'
end_snap_id	The end snapshot sequence number.
baseline_name	The name of baseline.
dbid	The database id (default to local DBID).

Examples

This example creates a baseline (named 'oltp_peakload_bl') between snapshots 105 and 107 for the local database:

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 105,
end_snap_id => 107,
baseline_name => 'oltp_peakload_bl');

If you query the DBA_HIST_BASELINE view after the Create Baseline action, you will see the newly created baseline in the Workload Repository.

---

CREATE_SNAPSHOT Function and Procedure

This function and procedure create snapshots.In the case of the function, the snapshot ID is returned.

Syntax

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
   flush_level IN VARCHAR2 DEFAULT 'TYPICAL');

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
   flush_level IN VARCHAR2 DEFAULT 'TYPICAL')
 RETURN NUMBER;

Parameters

Table 106-5  CREATE_SNAPSHOT Parameters

Parameter	Description
flush_level	The flush level for the snapshot is either 'TYPICAL' or 'ALL'

Examples

This example creates a manual snapshot at the TYPICAL level:

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

If you query the DBA_HIST_SNAPSHOT view after the CREATE_SNAPSHOT action, you will see one more snapshot ID added to the Workload Repository.

---

DROP_BASELINE Procedure

This procedure drops a baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
   baseline_name  IN  VARCHAR2,
   cascade        IN  BOOLEAN DEFAULT false,
   dbid           IN  NUMBER DEFAULT NULL);

Parameters

Table 106-6  DROP_BASELINE Parameters

Parameter	Description
baseline_name	The name of baseline.
cascade	If TRUE, the pair of snapshots associated with the baseline will also be dropped. Otherwise, only the baseline is removed.
dbid	The (optional) database id (default to local DBID).

Examples

This example drops the baseline 'oltp_peakload_bl' without dropping the underlying snapshots:

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
    baseline_name => 'oltp_peakload_bl');

If you query the DBA_HIST_BASELINE view after the DROP_BASELINE action, you will see the specified baseline definition is removed. You can query the DBS_HIST_SNAPSHOT view to find that the underlying snapshots are left intact.

---

DROP_SNAPSHOT_RANGE Procedure

This procedure drops a range of snapshots.

Syntax

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id    IN  NUMBER,
   high_snap_id   IN  NUMBER
   dbid           IN  NUMBER DEFAULT NULL);

Parameters

Table 106-7  DROP_SNAPSHOT_RANGE Procedure Parameters

Parameter	Description
low_snap_id	The low snapshot id of snapshots to drop.
high_snap_id	The high snapshot id of snapshots to drop.
dbid	The database id (default to local DBID.

Examples

This example drops the range of snapshots between snapshot id 102 to 105 for the local database:

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(102, 105);

If you query the dba_hist_snapshot view after the Drop Snapshot action, you will see that snapshots 102 to 105 are removed from the Workload Repository.

---

MODIFY_SNAPSHOT_SETTINGS Procedure

This procedure controls two aspects of snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload Repository.

Syntax

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS((
   retention   IN  NUMBER DEFAULT NULL,
   interval    IN  NUMBER DEFAULT NULL,
   dbid        IN  NUMBER DEFAULT NULL);

Parameters

Table 106-8  MODIFY_SNAPSHOT_SETTINGS Procedure Parameters

Parameter	Description
retention	The new retention time (in minutes). The specified value must be in the range of 1 day to 100 years. If ZERO is specified, the maximum value of 100 years will be used. If NULL is specified, the old value for retention is preserved.
interval	The new interval setting between each snapshot, in units of minutes. The specified value must be in the range between 10 minutes to 1 year. If ZERO is specified, the maximum value of 1 year will be used. If NULL is specified, the current value is preserved.
dbid	The database id (default to local DBID.

Examples

This example changes the interval setting to one hour and the retention setting to two weeks for the local database:

execute dbms_workload_repository.modify_snapshot_settings(interval => 60,
retention => 20160);

If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.