119 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:


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.


Summary of DBMS_WORKLOAD_REPOSITORY Subprograms

Table 119-1 DBMS_WORKLOAD_REPOSITORY Package Subprograms

Subprogram Description

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

Modifies the snapshot settings.



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)
 RETURN awrrpt_html_type_table PIPELINED;

Parameters

Table 119-2 ASH_REPORT_HTML Parameters

Parameter Description

l_dbid

The database identifier

l_inst_num

The 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

The session ID (see Usage Notes)

l_sql_id

The SQL ID (see Usage Notes)

l_wait_class

The wait class name (see Usage Notes)

l_service_hash

The service name hash (see Usage Notes)

l_module

The module name (see Usage Notes)

l_action

The action name (see Usage Notes)

l_client_id

The client ID for end-to-end backtracing (see Usage Notes)


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 ashrpt.sql script which prompts users for the required information.

  • The unspecified optional arguments are used to generate an ASH Reports that specify 'report targets' such as a SQL statement, or a session, or a particular Service/Module combination. These arguments are specified to restrict the ASH rows that would be used to generate the report. For example, to generate an ASH report on a particular SQL statement, such as SQL_ID 'abcdefghij123 ' pass that sql_id value to the l_sql_id argument:

    l_sql_id =>  'abcdefghij123'
    

    Any combination of those optional arguments can be passed in, and only rows in ASH that satisfy all of those 'report targets' will be used. If multiple 'report targets' are specified, AND conditional logic is used to connect them. For example, to generate an ASH report on MODULE "PAYROLL" and ACTION "PROCESS", use the following predicate:

    l_module =>  'PAYROLL', l_action =>  'PROCESS'
    

    Valid SQL wildcards can be used in all the arguments that are of type VARCHAR2.

    Table 119-3 ASH_REPORT_HTML: Wildcards Allowed (or Not) in Arguments

    Argument Name Comment Wildcard Allowed

    l_sid

    The session ID (for example, V$SESSION.SID)

    No

    l_sql_id

    The SQL ID (for example, V$SQL.SQL_ID)

    Yes

    l_wait_class

    The wait class name (for example, V$EVENT_NAME.WAIT_CLASS)

    Yes

    l_service_hash

    The service name hash (for example, V$ACTIVE_SERVICES.NAME_HASH)

    No

    l_module

    The module name (for example, V$SESSION.MODULE)

    Yes

    l_action

    The action name (for example, V$SESSION.ACTION)

    Yes

    l_client_id

    The client ID for end-to-end backtracing (for example, V$SESSION.CLIENT_IDENTIFIER)

    Yes



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)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

Table 119-4 ASH_REPORT_TEXT Parameters

Parameter Description

l_dbid

The database identifier

l_inst_num

The 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

The session ID (see Usage Notes)

l_sql_id

The SQL ID (see Usage Notes)

l_wait_class

The wait class name (see Usage Notes)

l_service_hash

The service name hash (see Usage Notes)

l_module

The module name (see Usage Notes)

l_action

The action name (see Usage Notes)

l_client_id

The client ID for end-to-end backtracing (see Usage Notes)


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 ashrpt.sql script which prompts users for the required information.

  • The unspecified optional arguments are used to generate an ASH Reports that specify 'report targets' such as a SQL statement, or a session, or a particular Service/Module combination. These arguments are specified to restrict the ASH rows that would be used to generate the report. For example, to generate an ASH report on a particular SQL statement, such as SQL_ID 'abcdefghij123 ' pass that sql_id value to the l_sql_id argument:

    l_sql_id =>  'abcdefghij123'
    

    Any combination of those optional arguments can be passed in, and only rows in ASH that satisfy all of those 'report targets' will be used. If multiple 'report targets' are specified, AND conditional logic is used to connect them. For example, to generate an ASH report on MODULE "PAYROLL" and ACTION "PROCESS", use the following predicate:

    l_module =>  'PAYROLL', l_action =>  'PROCESS'
    

    Valid SQL wildcards can be used in all the arguments that are of type VARCHAR2.

    Table 119-5 ASH_REPORT_TEXT: Wildcards Allowed (or Not) in Arguments

    Argument Name Comment Wildcard Allowed

    l_sid

    The session ID (for example, V$SESSION.SID)

    No

    l_sql_id

    The SQL ID (for example, V$SQL.SQL_ID)

    Yes

    l_wait_class

    The wait class name (for example, V$EVENT_NAME.WAIT_CLASS)

    Yes

    l_service_hash

    The service name hash (for example, V$ACTIVE_SERVICES.NAME_HASH)

    No

    l_module

    The module name (for example, V$SESSION.MODULE)

    Yes

    l_action

    The action name (for example, V$SESSION.ACTION)

    Yes

    l_client_id

    The client ID for end-to-end backtracing (for example, V$SESSION.CLIENT_IDENTIFIER)

    Yes



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 119-6 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 119-7 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_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 119-8 AWR_REPORT_HTML Parameters

Parameter Description

l_dbid

The database identifier

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


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 119-9 AWR_REPORT_TEXT Parameters

Parameter Description

l_dbid

The database identifier

l_insT_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, 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 119-10 AWR_SQL_REPORT_HTML Parameters

Parameter Description

l_dbid

The database identifier

l_inst_num

The instance number

l_bid

The 'Begin Snapshot' ID

l_eid

The 'End Snapshot' ID

l_sqlid

The 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 119-11 AWR_SQL_REPORT_TEXT Parameters

Parameter Description

l_dbid

The database identifier

l_inst_num

The instance number

l_bid

The 'Begin Snapshot' ID

l_eid

The 'End Snapshot' ID

l_sqlid

The 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(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 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 119-12 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 119-13 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 119-14 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 DBA_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 119-15 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 Procedures

This procedure controls three aspects of snapshot generation.

  • The INTERVAL setting affects how often snapshots are automatically captured.

  • The RETENTION setting affects how long snapshots are retained in the Workload Repository.

  • The number of SQL captured for each Top criteria. If the user manually specifies a value for Top N SQL, the AWR SQL collection will use the user-specified number for both automatic and manual snapshots.

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 119-16 MODIFY_SNAPSHOT_SETTINGS Procedure Parameters

Parameter Description

retention

The 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.

interval

The new interval setting between each snapshot, in units of minutes. The specified value must be in the range MIN_RETENTION (10 minutes) to MAX_RETENTION (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 100000000. 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

The 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.