Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

160 DBMS_WORKLOAD_REPOSITORY

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

See Also:

Oracle Database Performance Tuning Guide for more information about the "Automatic Workload Repository"

The chapter contains the following topics:


Using DBMS_WORKLOAD_REPOSITORY

This section contains topics which relate to using the DBMS_WORKLOAD_REPOSITORY package.


Examples

This example shows how to generate an AWR text report with the DBMS_WORKLOAD_REPOSITORY package for database identifier 1557521192, instance id 1, snapshot ids 5390 and 5391 and with default options.

-- make sure to set line size appropriately
-- set linesize 152
SELECT output FROM TABLE(
   DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
     1557521192,  1, 5390, 5392) ) ;

You can call the DBMS_WORKLOAD_REPOSITORY packaged functions directly as in the example, but Oracle recommends you use the corresponding supplied SQL script (awrrpt.sql in this case) for the packaged function, which prompts the user for required information.


Data Structures

The DBMS_WORKLOAD_REPOSITORY package defines an object and associated table types.

OBJECT Types

TABLE Types


AWR_BASELINE_METRIC_TYPE Object Type

This type shows the values of the metrics corresponding to a baseline.

Syntax

TYPE breakpoint_info AS OBJECT (
   baseline_name      VARCHAR2(64),
   dbid               NUMBER NOT NULL,
   instance_number    NUMBER NOT NULL,
   beg_time           DATE NOT NULL,
   end_time           DATE NOT NULL,
   metric_id          NUMBER NOT NULL,
   metric_name        VARCHAR2(64) NOT NULL,
   metric_unit        VARCHAR2(64) NOT NULL,
   num_interval       NUMBER NOT NULL,
   interval_size      NUMBER NOT NULL,
   average            NUMBER NOT NULL,
   minimum            NUMBER NOT NULL,
   maximum            NUMBER NOT NULL);

Fields

Table 160-1 RUNTIME_INFO Fields

Field Description

baseline_name

Name of the Baseline

dbid

Database ID for the snapshot

instance_number

Instance number for the snapshot

beg_time

Begin time of the interval

end_time

End time of the interval

metric_id

Metric ID

metric_name

Metric name

metric_unit

Unit of measurement

num_interval

Number of intervals observed

interval_size

Interval size (in hundredths of a second)

average

Average over the period

minimum

Minimum value observed

maximum

Maximum value observed



AWR_BASELINE_METRIC_TYPE_TABLE Table Type

This type is used by the SELECT_BASELINE_METRICS Function.

Syntax

CREATE TYPE awr_baseline_metric_type_table AS TABLE OF awr_baseline_metric_type;

AWRRPT_INSTANCE_LIST_TYPE Table Type

This type provides an alternative to a comma-separated list.

Syntax

CREATE TYPE awrrpt_instance_list_type AS TABLE OF NUMBER;

Summary of DBMS_WORKLOAD_REPOSITORY Subprograms

Table 160-2 DBMS_WORKLOAD_REPOSITORY Package Subprograms

Subprogram Description

ADD_COLORED_SQL Procedure

Adds a colored SQL ID

ASH_GLOBAL_REPORT_HTML Function

Displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in HTML format.

ASH_GLOBAL_REPORT_TEXT Function

Displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in Text format.

ASH_REPORT_HTML Function

Displays the ASH report in HTML

ASH_REPORT_TEXT Function

Displays the ASH report in text

AWR_DIFF_REPORT_HTML Function

Displays the AWR Diff-Diff report in HTML

AWR_DIFF_REPORT_TEXT Function

Displays the AWR Diff-Diff report in text

AWR_GLOBAL_DIFF_REPORT_HTML Functions

Displays the Global AWR Compare Periods Report in HTML

AWR_GLOBAL_DIFF_REPORT_TEXT Functions

Displays the Global AWR Compare Periods Report in text

AWR_GLOBAL_REPORT_HTML Functions

Displays the Global AWR report in HTML

AWR_GLOBAL_REPORT_TEXT Functions

Displays the Global AWR report in text

AWR_REPORT_HTML Function

Displays the AWR report in HTML

AWR_REPORT_TEXT Function

Displays the AWR report in text

AWR_SQL_REPORT_HTML Function

Displays the AWR SQL Report in HTML format

AWR_SQL_REPORT_TEXT Function

Displays the AWR SQL Report in text format

CREATE_BASELINE Functions & Procedures

Creates a single baseline

CREATE_BASELINE_TEMPLATE Procedures

Creates a baseline template

CREATE_SNAPSHOT Function and Procedure

Creates a manual snapshot immediately

DROP_BASELINE Procedure

Drops a range of snapshots

DROP_BASELINE_TEMPLATE Procedure

Removes a baseline template that is no longer needed

DROP_SNAPSHOT_RANGE Procedure

Activates service

MODIFY_SNAPSHOT_SETTINGS Procedures

Modifies the snapshot settings

MODIFY_BASELINE_WINDOW_SIZE Procedure

Modifies the window size for the Default Moving Window Baseline

RENAME_BASELINE Procedure

Renames a baseline

SELECT_BASELINE_METRICS Function

Shows the values of the metrics corresponding to a baseline



ADD_COLORED_SQL Procedure

This procedure adds a colored SQL ID. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities (so that it does not have to be a TOP SQL). Capture occurs if the SQL is found in the cursor cache at snapshot time.To uncolor the SQL, invoke the REMOVE_COLORED_SQL Procedure.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
   sql_id         IN VARCHAR2,
   dbid           IN NUMBER DEFAULT NULL);

Parameters

Table 160-3 ADD_COLORED_SQL Procedure Parameters

Parameter Description

sql_id

13-character external SQL ID

dbid

Optional dbid, defaults to Local DBID



ASH_GLOBAL_REPORT_HTML Function

This table function displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in HTML format.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_HTML(
   l_dbid          IN NUMBER,
   l_inst_num      IN VARCHAR2((1023),
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,
   l_slot_width    IN NUMBER    DEFAULT 0,
   l_sid           IN NUMBER    DEFAULT NULL,
   l_sql_id        IN VARCHAR2  DEFAULT NULL,
   l_wait_class    IN VARCHAR2  DEFAULT NULL,
   l_service_hash  IN NUMBER    DEFAULT NULL,
   l_module        IN VARCHAR2  DEFAULT NULL,
   l_action        IN VARCHAR2  DEFAULT NULL,
   l_client_id     IN VARCHAR2  DEFAULT NULL,
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 160-4 ASH_GLOBAL_REPORT_HTML Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

List of instances (such as '1,2,3'), or NULL to report on all instances in the database

l_btime

The 'begin time'

l_etime

The 'end time'

l_options

Report level (currently not used)

l_slot_width

Specifies (in seconds) how wide the slots used in the "Top Activity" section of the report should be. This argument is optional, and if it is not specified the time interval between l_btime and l_etime is appropriately split into not more than 10 slots.

l_sid

Session ID (see Usage Notes)

l_sql_id

SQL ID (see Usage Notes)

l_wait_class

Wait class name (see Usage Notes)

l_service_hash

Service name hash (see Usage Notes)

l_module

Module name (see Usage Notes)

l_action

Action name (see Usage Notes)

l_client_id

Client ID for end-to-end backtracing (see Usage Notes)

l_plsql_entry

PL/SQL entry point (see Usage Notes)

l_data_src

Ignored since the report works off of data on disk only


Return Values

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

Usage Notes


ASH_GLOBAL_REPORT_TEXT Function

This table function Displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in Text format.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
   l_dbid          IN VARCHAR2(1023),
   l_inst_num      IN NUMBER,
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,
   l_slot_width    IN NUMBER    DEFAULT 0,
   l_sid           IN NUMBER    DEFAULT NULL,
   l_sql_id        IN VARCHAR2  DEFAULT NULL,
   l_wait_class    IN VARCHAR2  DEFAULT NULL,
   l_service_hash  IN NUMBER    DEFAULT NULL,
   l_module        IN VARCHAR2  DEFAULT NULL,
   l_action        IN VARCHAR2  DEFAULT NULL,
   l_client_id     IN VARCHAR2  DEFAULT NULL,
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

Table 160-6 ASH_GLOBAL_REPORT_TEXT Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

List of instances (such as '1,2,3'), or NULL to report on all instances in the database

l_btime

The 'begin time'

l_etime

The 'end time'

l_options

Report level (currently not used)

l_slot_width

Specifies (in seconds) how wide the slots used in the "Top Activity" section of the report should be. This argument is optional, and if it is not specified the time interval between l_btime and l_etime is appropriately split into not more than 10 slots.

l_sid

Session ID (see Usage Notes)

l_sql_id

SQL ID (see Usage Notes)

l_wait_class

Wait class name (see Usage Notes)

l_service_hash

Service name hash (see Usage Notes)

l_module

Module name (see Usage Notes)

l_action

Action name (see Usage Notes)

l_client_id

Client ID for end-to-end backtracing (see Usage Notes)

l_plsql_entry

PL/SQL entry point (see Usage Notes)

l_data_src

Ignored since the report works off of data on disk only


Return Values

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

Usage Notes


ASH_REPORT_HTML Function

This table function displays the ASH Spot report in HTML.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
   l_dbid          IN NUMBER,
   l_inst_num      IN NUMBER,
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,
   l_slot_width    IN NUMBER    DEFAULT 0,
   l_sid           IN NUMBER    DEFAULT NULL,
   l_sql_id        IN VARCHAR2  DEFAULT NULL,
   l_wait_class    IN VARCHAR2  DEFAULT NULL,
   l_service_hash  IN NUMBER    DEFAULT NULL,
   l_module        IN VARCHAR2  DEFAULT NULL,
   l_action        IN VARCHAR2  DEFAULT NULL,
   l_client_id     IN VARCHAR2  DEFAULT NULL,
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 160-8 ASH_REPORT_HTML Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

Instance number

l_btime

The 'begin time'

l_etime

The 'end time'

l_options

Report level (currently not used)

l_slot_width

Specifies (in seconds) how wide the slots used in the "Top Activity" section of the report should be. This argument is optional, and if it is not specified the time interval between l_btime and l_etime is appropriately split into not more than 10 slots.

l_sid

Session ID (see Usage Notes)

l_sql_id

SQL ID (see Usage Notes)

l_wait_class

Wait class name (see Usage Notes)

l_service_hash

Service name hash (see Usage Notes)

l_module

Module name (see Usage Notes)

l_action

Action name (see Usage Notes)

l_client_id

Client ID for end-to-end backtracing (see Usage Notes)

l_plsql_entry

PL/SQL entry point (see Usage Notes)

l_data_src

Can be used to specify a data source (see Usage Notes)

  • 1 => memory (V$ACTIVE_SESION_HISTORY)

  • 2 => disk (DBA_HIST_ACTIVE_SESS_HISTORY)

  • 0 => both. This is the default value. Here, the begin and end time parameters are used to get the samples from the appropriate data source, which can be memory, disk, or both.


Return Values

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

Usage Notes


ASH_REPORT_TEXT Function

This table function displays the ASH Spot report in text.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
   l_dbid          IN NUMBER,
   l_inst_num      IN NUMBER,
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,
   l_slot_width    IN NUMBER    DEFAULT 0,
   l_sid           IN NUMBER    DEFAULT NULL,
   l_sql_id        IN VARCHAR2  DEFAULT NULL,
   l_wait_class    IN VARCHAR2  DEFAULT NULL,
   l_service_hash  IN NUMBER    DEFAULT NULL,
   l_module        IN VARCHAR2  DEFAULT NULL,
   l_action        IN VARCHAR2  DEFAULT NULL,
   l_client_id     IN VARCHAR2  DEFAULT NULL,
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

Table 160-10 ASH_REPORT_TEXT Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

Instance number

l_btime

The 'begin time'

l_etime

The 'end time'

l_options

Report level (currently not used)

l_slot_width

Specifies (in seconds) how wide the slots used in the "Top Activity" section of the report should be. This argument is optional, and if it is not specified the time interval between l_btime and l_etime is appropriately split into not more than 10 slots.

l_sid

Session ID (see Usage Notes)

l_sql_id

SQL ID (see Usage Notes)

l_wait_class

Wait class name (see Usage Notes)

l_service_hash

Service name hash (see Usage Notes)

l_module

Module name (see Usage Notes)

l_action

Action name (see Usage Notes)

l_client_id

Client ID for end-to-end backtracing (see Usage Notes)

l_plsql_entry

PL/SQL entry point (see Usage Notes)

l_data_src

Can be used to specify a data source (see Usage Notes)

  • 1 => memory (V$ACTIVE_SESION_HISTORY)

  • 2 => disk (DBA_HIST_ACTIVE_SESS_HISTORY)

  • 0 => both. This is the default value. Here, the begin and end time parameters are used to get the samples from the appropriate data source, which can be memory, disk, or both.


Return Values

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

Usage Notes


AWR_DIFF_REPORT_HTML Function

This table function displays the AWR Compare Periods report in HTML.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
   dbid1     IN NUMBER,
   inst_num1 IN NUMBER,
   bid1      IN NUMBER,
   eid1      IN NUMBER,
   dbid2     IN NUMBER,
   inst_num2 IN NUMBER,
   bid2      IN NUMBER,
   eid2      IN NUMBER)
  RETURN awrdrpt_text_type_table PIPELINED;

Parameters

Table 160-12 AWR_DIFF_REPORT_HTML Parameters

Parameter Description

dbid1

1st database identifier

inst_num1

1st instance number

bid1

1st 'Begin Snapshot' ID

eid1

1st 'End Snapshot' ID

dbid2

2nd database identifier

inst_num2

2nd instance number

bid2

2nd 'Begin Snapshot' ID

eid2

2nd 'End Snapshot' ID


Return Values

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

Usage Notes

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


AWR_DIFF_REPORT_TEXT Function

This table function displays the AWR Compare Periods report in text.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(
   dbid1     IN NUMBER,
   inst_num1 IN NUMBER,
   bid1      IN NUMBER,
   eid1      IN NUMBER,
   dbid2     IN NUMBER,
   inst_num2 IN NUMBER,
   bid2      IN NUMBER,
   eid2      IN NUMBER)
  RETURN awrdrpt_text_type_table PIPELINED;

Parameters

Table 160-13 AWR_DIFF_REPORT_TEXT Parameters

Parameter Description

dbid1

1st database identifier

inst_num1

1st instance number

bid1

1st 'Begin Snapshot' ID

eid1

1st 'End Snapshot' ID

dbid2

2nd database identifier

inst_num2

2nd instance number

bid2

2nd 'Begin Snapshot' ID

eid2

2nd 'End Snapshot' ID


Return Values

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

Usage Notes

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


AWR_GLOBAL_DIFF_REPORT_HTML Functions

This table function displays Global AWR Compare Periods Report in HTML format.

The first overload accepts comma-separated lists of instance numbers for inst_num1 and inst_num2. No leading zeroes are allowed and there is a limit of 1023 characters.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_HTML (
   dbid1        IN    NUMBER,
   inst_num1    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrrpt_html_type_table PIPELINED;

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_HTML (
   dbid1        IN    NUMBER,
   inst_num1    IN    VARCHAR2,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    VARCHAR2,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 160-14 AWR_GLOBAL_DIFF_REPORT_HTML Function Parameters

Parameter Description

dbid1

1st database identifier

inst_num1

1st list of instance numbers. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

bid1

1st Begin Snap ID

eid1

1st End Snapshot ID

dbid2

2nd database identifier

inst_num2

2nd list of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

bid2

2nd Begin Snap ID

eid2

2nd End Snapshot ID


Return Values

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


AWR_GLOBAL_DIFF_REPORT_TEXT Functions

This table function displays Global AWR Compare Periods Report in text format.

The first overload accepts comma-separated lists of instance numbers for inst_num1 and inst_num2. No leading zeroes are allowed and there is a limit of 1023 characters.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_TEXT (
   dbid1        IN    NUMBER,
   inst_num1    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrdrpt_text_type_table PIPELINED;

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_TEXT (
   dbid1        IN    NUMBER,
   inst_num1    IN    VARCHAR2,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    VARCHAR2,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrdrpt_text_type_table PIPELINED;

Parameters

Table 160-15 AWR_GLOBAL_DIFF_REPORT_TEXT Functions Parameters

Parameter Description

dbid1

1st database identifier

inst_num1

1st list of instance numbers. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

bid1

1st Begin Snap ID

eid1

1st End Snapshot ID

dbid2

2nd database identifier

inst_num2

2nd list of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

bid2

2nd Begin Snap ID

eid2

2nd End Snapshot ID


Return Values

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


AWR_GLOBAL_REPORT_HTML Functions

This table function displays the Global AWR report in HTML.

The first overload accepts a comma-separated list of instance numbers. No leading zeroes are allowed and there is a limit of 1023 characters.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML (
   l_dbid        IN    NUMBER,
   l_inst_num    IN    AWRRPT_INSTANCE_LIST_TYPE,   l_bid         IN    NUMBER,
   l_eid         IN    NUMBER,
   l_options     IN    NUMBER DEFAULT 0)
  RETURN awrrpt_html_type_table PIPELINED;

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML (
   l_dbid       IN    NUMBER,
   l_inst_num   IN    VARCHAR2,   l_bid        IN    NUMBER,
   l_eid        IN    NUMBER,
   l_options    IN    NUMBER DEFAULT 0)
  RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 160-16 AWR_GLOBAL_REPORT_HTML Function Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

l_bid

Begin Snap ID

l_eid

End Snapshot ID

l_options

 

Return Values

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


AWR_GLOBAL_REPORT_TEXT Functions

This table function displays the Global AWR report in text.

The first overload accepts a comma-separated list of instance numbers. No leading zeroes are allowed and there is a limit of 1023 characters

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_TEXT(
   l_dbid        IN    NUMBER,
   l_inst_num    IN    AWRRPT_INSTANCE_LIST_TYPE,
   l_bid         IN    NUMBER,
   l_eid         IN    NUMBER,
   l_options     IN    NUMBER DEFAULT 0)
  RETURN awrdrpt_text_type_table PIPELINED;

DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_TEXT(
   l_dbid       IN    NUMBER,
   l_inst_num   IN    VARCHAR2,
   l_bid        IN    NUMBER,
   l_eid        IN    NUMBER,
   l_options    IN    NUMBER DEFAULT 0)
  RETURN awrdrpt_text_type_table PIPELINED;

Parameters

Table 160-17 AWR_GLOBAL_REPORT_TEXT Function Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available, and which have not been restarted between snapshots, will be included in the report.

l_bid

Begin Snap ID

l_eid

End Snapshot ID

l_options

A flag to specify to control the output of the report. Currently, not used.


Return Values

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


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 160-18 AWR_REPORT_HTML Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

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, and PGA Target Advice.


Return Values

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

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 160-19 AWR_REPORT_TEXT Parameters

Parameter Description

l_dbid

Database identifier

l_insT_num

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, and PGA Target Advice.


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.


AWR_SQL_REPORT_HTML Function

This table function displays the AWR SQL Report in HTML format.

Syntax

DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(
   l_dbid       IN    NUMBER,
   l_inst_num   IN    NUMBER,
   l_bid        IN    NUMBER,
   l_eid        IN    NUMBER,
   l_sqlid      IN    VARCHAR2,
   l_options    IN    NUMBER DEFAULT 0)
 RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 160-20 AWR_SQL_REPORT_HTML Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

Instance number

l_bid

The 'Begin Snapshot' ID

l_eid

The 'End Snapshot' ID

l_sqlid

SQL ID of statement to be analyzed

l_options

A flag to specify to control the output of the report. Currently, not used.


Return Values

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

Usage Notes

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


AWR_SQL_REPORT_TEXT Function

This table function displays the AWR SQL Report in text format.

Syntax

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

Parameters

Table 160-21 AWR_SQL_REPORT_TEXT Parameters

Parameter Description

l_dbid

Database identifier

l_inst_num

Instance number

l_bid

The 'Begin Snapshot' ID

l_eid

The 'End Snapshot' ID

l_sqlid

SQL ID of statement to be analyzed

l_options

Flag to specify to control the output of the report. Currently, not used.


Return Values

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

Usage Notes

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


CREATE_BASELINE Functions & Procedures

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,
   expiration       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,
   expiration       IN  NUMBER DEFAULT NULL)
 RETURN NUMBER;

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
   start_time       IN  DATE,
   end_time         IN  DATE,
   baseline_name    IN  VARCHAR2,
   dbid             IN  NUMBER DEFAULT NULL,
   expiration       IN  NUMBER DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
   start_time       IN  DATE,
   end_time         IN  DATE,
   baseline_name    IN  VARCHAR2,
   dbid             IN  NUMBER DEFAULT NULL,
   expiration       IN  NUMBER DEFAULT NULL);
 RETURN NUMBER;

Parameters

Table 160-22 CREATE_BASELINE Function & Procedure Parameters

Parameter Description

start_snap_id

Start snapshot sequence number for the baseline'

end_snap_id

End snapshot sequence number for the baseline

start_time

Start time for the baseline'

end_time

End time for the baseline

baseline_name

Name of baseline.

dbid

Database Identifier for baseline. If NULL, this takes the database identifier for the local database. Defaults to NULL.

expiration

Expiration in number of days for the baseline. If NULL, then expiration is infinite, meaning do not drop baseline ever. Defaults to NULL.


Exceptions

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_BASELINE_TEMPLATE Procedures

This procedure specifies a template for how they would like baselines to be created for future time periods.

Syntax

Specifies a template for generating a baseline for a single time period in the future.

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
   start_time              IN DATE,
   end_time                IN DATE,
   baseline_name           IN VARCHAR2,
   template_name           IN VARCHAR2,
   expiration              IN NUMBER,
   dbid                    IN NUMBER DEFAULT NULL);

Specifies a template for creating and dropping baseline based on repeating time periods:

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
   day_of_week             IN VARCHAR2,
   hour_in_day             IN NUMBER, 
   duration                IN NUMBER,
   start_time              IN DATE,
   end_time                IN DATE,
   baseline_name_prefix    IN VARCHAR2,
   template_name           IN VARCHAR2,
   expiration              IN NUMBER,
   dbid                    IN NUMBER DEFAULT NULL);

Parameters

Table 160-23 CREATE_BASELINE_TEMPLATE Procedure Parameters

Parameter Description

start_time

Start Time for the baseline to be created'

end_time

End Time for the baseline to be created

baseline_name

Name of baseline to be created

template_name

Name for the template

expiration

Expiration in number of days for the baseline. If NULL, then expiration is infinite, meaning do not drop baseline ever. Defaults to NULL

dbid

Database Identifier for baseline. If NULL, this takes the database identifier for the local database. Defaults to NULL.

day_of_week

Day of week that the baseline should repeat on. Specify one of the following values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY.

hour_in_day

Value of 0-23 to specify the Hour in the Day the baseline should start

duration

Duration (in number of hours) after hour in the day that the baseline should last

baseline_name_prefix

Name for baseline prefix. When creating the baseline, the name of the baseline will be the prefix appended with the date information.



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 160-24 CREATE_SNAPSHOT Parameters

Parameter Description

flush_level

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 160-25 DROP_BASELINE Parameters

Parameter Description

baseline_name

Name of baseline to drop from the system

cascade

If TRUE, the pair of snapshots associated with the baseline will also be dropped. Otherwise, only the baseline is removed.

dbid

Database Identifier for baseline. If NULL, this takes the database identifier for the local database. Defaults to NULL.


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 DBA_HIST_SNAPSHOT view to find that the underlying snapshots are left intact.


DROP_BASELINE_TEMPLATE Procedure

This procedure removes a template that is no longer needed.

Syntax

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE(
   template_name           IN VARCHAR2,   dbid                    IN NUMBER DEFAULT NULL);

Parameters

Table 160-26 DROP_BASELINE_TEMPLATE Procedure Parameters

Parameter Description

template_name

Name of the template to remove

dbid

Database Identifier for baseline. If NULL, this takes the database identifier for the local database. Defaults to NULL.



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 160-27 DROP_SNAPSHOT_RANGE Procedure Parameters

Parameter Description

low_snap_id

Low snapshot id of snapshots to drop.

high_snap_id

High snapshot id of snapshots to drop.

dbid

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 Procedures

This procedure controls three aspects of snapshot generation.

There are two overloads. The first takes a NUMBER and the second takes a VARCHAR2 for the topnsql argument. The differences are described under the Parameters description.

Syntax

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

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

Parameters

Table 160-28 MODIFY_SNAPSHOT_SETTINGS Procedure Parameters

Parameter Description

retention

New retention time (in minutes). The specified value must be in the range of MIN_RETENTION (1 day) to MAX_RETENTION (100 years).

If ZERO is specified, snapshots will be retained forever. A large system-defined value will be used as the retention setting.

If NULL is specified, the old value for retention is preserved.

NOTE: The retention setting must be greater than or equal to the window size of the 'SYSTEM_MOVING_WINDOW' baseline. If the retention needs to be less than the window size, the MODIFY_BASELINE_WINDOW_SIZE Procedure can be used to adjust the window size.

interval

New interval setting between each snapshot, in units of minutes. The specified value must be in the range MIN_INTERVAL (10 minutes) to MAX_INTERVAL (1 year).

If ZERO is specified, automatic and manual snapshots will be disabled. A large system-defined value will be used as the retention setting.

If NULL is specified, the current value is preserved.

topnsql

  • If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.

  • If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.

dbid

Database identifier in AWR for which to modify the snapshot settings. If NULL is specified, the local dbid will be used. Defaults to NULL.


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.


MODIFY_BASELINE_WINDOW_SIZE Procedure

This procedure modifies the window size for the Default Moving Window Baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(
   window_size    IN   NUMBER,
   dbid           IN   NUMBER DEFAULT NULL);

Parameters

Table 160-29 MODIFY_BASELINE_WINDOW_SIZE Procedure Parameters

Parameter Description

window_size

New Window size for the default Moving Window Baseline, in number of days.

dbid

Database ID (default to local DBID)


Usage Notes

The window size must be less than or equal to the AWR retention setting. If the window size needs to be greater than the retention setting, the MODIFY_SNAPSHOT_SETTINGS Procedures can be used to adjust the retention setting.


REMOVE_COLORED_SQL Procedure

This procedure removes a colored SQL ID. After a SQL is uncolored, it will no longer be captured in a snapshot automatically, unless it makes the TOP list.

Syntax

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
   sql_id         IN VARCHAR2,
   dbid           IN NUMBER DEFAULT NULL);

Parameters

Table 160-30 REMOVE_COLORED_SQL Procedure Parameters

Parameter Description

sql_id

13-character external SQL ID

dbid

Optional dbid, defaults to Local DBID



RENAME_BASELINE Procedure

This procedure renames a baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE(
   old_baseline_name     IN   VARCHAR2,
   new_baseline_name     IN VARCHAR2,
   dbid                  IN NUMBER DEFAULT NULL);

Parameters

Table 160-31 RENAME_BASELINE Procedure Parameters

Parameter Description

old_baseline_name

Old baseline name

new_baseline_name

New baseline name

dbid

Database ID (default to local DBID)



SELECT_BASELINE_METRICS Function

This table function shows the values of the metrics corresponding to a baseline. The table function will return an object of the AWR_BASELINE_METRIC_TYPE Object Type.

Syntax

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS(
   baseline_name     IN VARCHAR2,
   dbid              IN NUMBER DEFAULT NULL,
   instance_num      IN NUMBER DEFAULT NULL)
 RETURN awr_baseline_metric_type_table PIPELINED;

Parameters

Table 160-32 SELECT_BASELINE_METRICS Function Parameters

Parameter Description

baseline_name

Name of the baseline for which we would like to view metrics

dbid

Database Identifier for baseline. If NULL, then use the database identifier for the local database. Defaults to NULL.

instance_num

Instance for which number the user wants to see statistics. If NULL, show statistics for the local instance. Defaults to NULL.