Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
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:
This section contains topics which relate to using the DBMS_WORKLOAD_REPOSITORY package.
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.
Table 118-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. |
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 118-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 118-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 |
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 118-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 118-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 |
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 118-6 AWR_DIFF_REPORT_HTML Parameters
Parameter | Description |
---|---|
dbid 1 |
1st database identifier |
inst_num 1 |
1st instance number |
bid 1 |
1st 'Begin Snapshot' ID |
eid 1 |
1st 'End Snapshot' ID |
dbid 2 |
2nd database identifier |
inst_num 2 |
2nd instance number |
bid 2 |
2nd 'Begin Snapshot' ID |
eid 2 |
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.
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 118-7 AWR_DIFF_REPORT_TEXT Parameters
Parameter | Description |
---|---|
dbid 1 |
1st database identifier |
inst_num 1 |
1st instance number |
bid 1 |
1st 'Begin Snapshot' ID |
eid 1 |
1st 'End Snapshot' ID |
dbid 2 |
2nd database identifier |
inst_num 2 |
2nd instance number |
bid 2 |
2nd 'Begin Snapshot' ID |
eid 2 |
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.
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 118-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:
|
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.
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 118-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:
|
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.
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 118-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.
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 118-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.
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 118-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.
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 118-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.
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 118-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.
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 118-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.
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 118-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 If |
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 If |
topnsql |
|
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.