146 DBMS_WORKLOAD_REPOSITORY

Using DBMS_WORKLOAD_REPOSITORY

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

• Examples

---

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

OBJECT Types

• AWR_BASELINE_METRIC_TYPE Object Type

TABLE Types

• AWR_BASELINE_METRIC_TYPE_TABLE Table Type

---

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

---

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;

Summary of DBMS_WORKLOAD_REPOSITORY Subprograms

---

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

---

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

Parameters

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 146-5 ASH_REPORT_HTML: Wildcards Allowed (or Not) in Arguments

  Argument Name	Comment	Wildcard Allowed
  l_sid	Session ID (for example, V$SESSION.SID)	No
  l_sql_id	SQL ID (for example, V$SQL.SQL_ID)	Yes
  l_wait_class	Wait class name (for example, V$EVENT_NAME.WAIT_CLASS)	Yes
  l_service_hash	Service name hash (for example, V$ACTIVE_SERVICES.NAME_HASH)	No
  l_module	Module name (for example, V$SESSION.MODULE)	Yes
  l_action	Action name (for example, V$SESSION.ACTION)	Yes
  l_client_id	Client ID for end-to-end backtracing (for example, V$SESSION.CLIENT_IDENTIFIER)	Yes
  l_plsql_entry	PL/SQL entry point (for example, "SYS.DBMS_LOB.*")	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,
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL)
 RETURN awrrpt_text_type_table PIPELINED;

Parameters

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 146-7 ASH_REPORT_TEXT: Wildcards Allowed (or Not) in Arguments

  Argument Name	Comment	Wildcard Allowed
  l_sid	Session ID (for example, V$SESSION.SID)	No
  l_sql_id	SQL ID (for example, V$SQL.SQL_ID)	Yes
  l_wait_class	Wait class name (for example, V$EVENT_NAME.WAIT_CLASS)	Yes
  l_service_hash	Service name hash (for example, V$ACTIVE_SERVICES.NAME_HASH)	No
  l_module	Module name (for example, V$SESSION.MODULE)	Yes
  l_action	Action name (for example, V$SESSION.ACTION)	Yes
  l_client_id	Client ID for end-to-end backtracing (for example, V$SESSION.CLIENT_IDENTIFIER)	Yes
  l_plsql_entry	PL/SQL entry point (for example, "SYS.DBMS_LOB.*")	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

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

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

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

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

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

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

Exceptions

• An error will be returned if this baseline name already exists in the system.

• The snapshot range that is specified for this interface must be an existing pair of snapshots in the database. An error will be returned if the inputted snapshots do not exist in the system.

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

---

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

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

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

---

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

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

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

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

---

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

---

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