216 DBMS_WORKLOAD_REPOSITORY
The DBMS_WORKLOAD_REPOSITORY
package lets you manage the Automatic Workload Repository (AWR) by performing operations, such as, managing snapshots and baselines.
The chapter contains the following topics:
See Also:
Oracle Database Performance Tuning Guide for more information about the Automatic Workload Repository
216.1 DBMS_WORKLOAD_REPOSITORY Examples
This example shows how to generate an AWR text report with the DBMS_WORKLOAD_REPOSITORY
package for database ID 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.
216.2 DBMS_WORKLOAD_REPOSITORY Data Structures
The DBMS_WORKLOAD_REPOSITORY
package defines an object and associated table types.
OBJECT Types
216.2.1 DBMS_WORKLOAD_REPOSITORY AWR_BASELINE_METRIC_TYPE Object Type
This type shows the values of the metrics corresponding to a baseline.
Syntax
TYPE awr_baseline_metric_type 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 216-1 AWR_BASELINE_METRIC_TYPE Fields
Field | Description |
---|---|
|
Name of the Baseline |
|
Database ID for the snapshot |
|
Instance number for the snapshot |
|
Begin time of the interval |
|
End time of the interval |
|
Metric ID |
|
Metric name |
|
Unit of measurement |
|
Number of intervals observed |
|
Interval size (in hundredths of a second) |
|
Average over the period |
|
Minimum value observed |
|
Maximum value observed |
216.2.2 DBMS_WORKLOAD_REPOSITORY AWR_BASELINE_METRIC_TYPE_TABLE Table Type
This type is used by the SELECT_BASELINE_METRIC Function.
Syntax
CREATE TYPE awr_baseline_metric_type_table AS TABLE OF awr_baseline_metric_type;
Related Topics
216.3 Summary of DBMS_WORKLOAD_REPOSITORY Subprograms
This table lists the DBMS_WORKLOAD_REPOSITORY
subprograms and briefly describes them.
Table 216-2 DBMS_WORKLOAD_REPOSITORY Package Subprograms
Subprogram | Description |
---|---|
Adds a colored SQL ID |
|
Displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in HTML format. |
|
Displays a global or Oracle Real Application Clusters (Oracle RAC) ASH Spot report in Text format. |
|
Displays the ASH Analytics active report |
|
Displays the ASH report in HTML |
|
Displays the ASH report in text |
|
Displays the AWR Diff-Diff report in HTML |
|
Displays the AWR Diff-Diff report in text |
|
Displays the Global AWR Compare Periods Report in HTML |
|
Displays the Global AWR Compare Periods Report in text |
|
Extracts AWR data from the AWR schema into a dump file. |
|
Displays the Global AWR report in HTML |
|
Displays the Global AWR report in text |
|
Loads the AWR data from a dump file into the
|
|
Displays the AWR report in HTML |
|
Displays the AWR report in text |
|
Configures specified report thresholds, including the number of rows in the report |
|
Displays the AWR SQL Report in HTML format |
|
Displays the AWR SQL Report in text format |
|
Controls the AWR snapshot creation for a database in the restricted session mode. |
|
Creates a single baseline |
|
Creates a baseline template |
|
Creates a manual remote snapshot immediately using the Remote Management Framework (RMF) |
|
Creates a manual local snapshot immediately |
|
Drops a previously-defined baseline |
|
Removes a baseline template that is no longer needed |
|
Drops a range of snapshots |
|
Returns the database identifier for the local AWR database |
|
Modifies the window size for the Default Moving Window Baseline |
|
Modifies the snapshot settings |
|
Purges SQL details, specifically rows from |
|
Registers a remote database in the AWR using the Remote Management Framework (RMF) |
|
Removes a colored SQL ID |
|
Renames a baseline |
|
Shows the values of the metrics corresponding to a baseline for a range of snapshots |
|
Shows the values of the metrics corresponding to a baseline for all the snapshots |
|
Removes all the statistics, metadata, and partitions of a remote database from the AWR using the Remote Management Framework (RMF) |
|
Updates the data file and tablespace information stored in the AWR with the current information in the database |
|
Updates rows of |
216.3.1 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.ADD_COLORED_SQL( sql_id IN VARCHAR2, dbid IN NUMBER DEFAULT NULL);
Parameters
Table 216-3 ADD_COLORED_SQL Procedure Parameters
Parameter | Description |
---|---|
|
13-character external SQL ID |
|
Optional DBID, defaults to Local |
216.3.2 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, l_container IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_html_type_table PIPELINED;
Parameters
Table 216-4 ASH_GLOBAL_REPORT_HTML Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
List of instances (such as |
|
The 'begin time' |
|
The 'end time' |
|
Report level (currently not used) |
|
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 |
|
Session ID (see Usage Notes) |
|
SQL ID (see Usage Notes) |
|
Wait class name (see Usage Notes) |
|
Service name hash (see Usage Notes) |
|
Module name (see Usage Notes) |
|
Action name (see Usage Notes) |
|
Client ID for end-to-end backtracing (see Usage Notes) |
|
PL/SQL entry point (see Usage Notes) |
|
Ignored since the report works off of data on disk only |
|
Name of the container for which report activity is limited. Valid values other than
Note: If while connected to a PDB you request information from another PDB this produces an empty report. |
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
ashrpti.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 thatsql_id
value to thel_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 onMODULE
"PAYROLL
" andACTION
"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 216-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_data_src
Wildcards are not allowed for
l_data_src
as it is of numeric datatypeNo
216.3.3 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, l_container IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_text_type_table PIPELINED;
Parameters
Table 216-6 ASH_GLOBAL_REPORT_TEXT Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
List of instances (such as |
|
The 'begin time' |
|
The 'end time' |
|
Report level (currently not used) |
|
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 |
|
Session ID (see Usage Notes) |
|
SQL ID (see Usage Notes) |
|
Wait class name (see Usage Notes) |
|
Service name hash (see Usage Notes) |
|
Module name (see Usage Notes) |
|
Action name (see Usage Notes) |
|
Client ID for end-to-end backtracing (see Usage Notes) |
|
PL/SQL entry point (see Usage Notes) |
|
Ignored since the report works off of data on disk only |
|
Name of the container for which report activity is limited. Valid values other than
Note: If while connected to a PDB you request information from another PDB this produces an empty report. |
Return Values
The output will be one column of VARCHAR2(320)
.
Usage Notes
-
You can call the function directly but Oracle recommends you use the
ashrpti.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 thel_sql_id
argument:l_sql_id => 'abcdefghij123'
Table 216-7 ASH_GLOBAL_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
l_data_src
Wildcards are not allowed for
l_data_src
as it is of numeric datatypeNo
-
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 onMODULE
"PAYROLL
" andACTION
"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
.
216.3.4 ASH_REPORT_ANALYTICS Function
This function returns the ASH Analytics active report.
Syntax
DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_ANALYTICS( dbid IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, begin_time IN DATE, end_time IN DATE, report_level IN VARCHAR2 DEFAULT NULL, filter_list IN VARCHAR2 DEFAULT NULL) RETURN CLOB;
Parameters
Table 216-8 ASH_REPORT_ANALYTICS Parameters
Parameter | Description |
---|---|
|
Database identifier. If its value is set to NULL, then the database identifier for the local database is used. Its default value is NULL. |
|
Instance number of the database for which the statistics are required. If its value is set to NULL, then the statistics for the local database are returned. Its default value is NULL. |
|
The start time of the interval for which the ASH report is required. |
|
The end time of the interval for which the ASH report is required. |
|
Describes the list of components to build. |
|
Describes the list of filters to apply. Its default value is NULL (no filters to apply). |
Return Values
Returns the ASH Analytics active report.
216.3.5 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, l_container IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_html_type_table PIPELINED;
Parameters
Table 216-9 ASH_REPORT_HTML Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
The 'begin time' |
|
The 'end time' |
|
Report level (currently not used) |
|
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 |
|
Session ID (see Usage Notes) |
|
SQL ID (see Usage Notes) |
|
Wait class name (see Usage Notes) |
|
Service name hash (see Usage Notes) |
|
Module name (see Usage Notes) |
|
Action name (see Usage Notes) |
|
Client ID for end-to-end backtracing (see Usage Notes) |
|
PL/SQL entry point (see Usage Notes) |
|
Can be used to specify a data source (see Usage Notes)
|
|
Name of the container for which report activity is limited. Valid values other than
Note: If while connected to a PDB you request information from another PDB this produces an empty report. |
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
ashrpti.sql
script which prompts users for the required information. -
By default, the report uses the begin and end time parameters (
l_btime
andl_etime
, respectively) to find all rows in that time range either from memory, or disk, or both. However, usingl_data_src
, one can explicitly specify one of those data sources. For example, to generate an ASH report on all rows betweenl_btime
andl_time
found in memory, usel_data_src => 1
Similarly, to generate a report on samples found only on disk, use
l_data_src => 2
-
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 thatsql_id
value to thel_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 onMODULE
"PAYROLL
" andACTION
"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 216-10 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
216.3.6 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, l_container IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_text_type_table PIPELINED;
Parameters
Table 216-11 ASH_REPORT_TEXT Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
The 'begin time' |
|
The 'end time' |
|
Report level (currently not used) |
|
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 |
|
Session ID (see Usage Notes) |
|
SQL ID (see Usage Notes) |
|
Wait class name (see Usage Notes) |
|
Service name hash (see Usage Notes) |
|
Module name (see Usage Notes) |
|
Action name (see Usage Notes) |
|
Client ID for end-to-end backtracing (see Usage Notes) |
|
PL/SQL entry point (see Usage Notes) |
|
Can be used to specify a data source (see Usage Notes)
|
|
Name of the container for which report activity is limited. Valid values other than
Note: If while connected to a PDB you request information from another PDB this produces an empty report. |
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
ashrpti.sql
script which prompts users for the required information. -
By default, the report uses the begin and end time parameters (
l_btime
andl_etime
, respectively) to find all rows in that time range either from memory, or disk, or both. However, usingl_data_src
, one can explicitly specify one of those data sources. For example, to generate an ASH report on all rows betweenl_btime
andl_time
found in memory, usel_data_src => 1
Similarly, to generate a report on samples found only on disk, use
l_data_src => 2
-
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 thatSQL_ID
value to thel_sql_id
argument:l_sql_id => 'abcdefghij123'
Table 216-12 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
l_data_src
Wildcards are not allowed for
l_data_src
as it is of numeric datatypeNo
-
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 onMODULE
"PAYROLL
" andACTION
"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
.
216.3.7 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 216-13 AWR_DIFF_REPORT_HTML Parameters
Parameter | Description |
---|---|
|
1st database identifier |
|
1st instance number |
|
1st beginning snapshot ID |
|
1st ending snapshot ID |
|
2nd database identifier |
|
2nd instance number |
|
2nd beginning snapshot ID |
|
2nd ending 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.
216.3.8 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 216-14 AWR_DIFF_REPORT_TEXT Parameters
Parameter | Description |
---|---|
|
1st database identifier |
|
1st instance number |
|
1st beginning snapshot ID |
|
1st ending snapshot ID |
|
2nd database identifier |
|
2nd instance number |
|
2nd beginning snapshot ID |
|
2nd ending 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.
216.3.9 AWR_EXP Procedure
This procedure extracts AWR data from the AWR schema and dump the information into a file. You can specify the snapshot range for the data that you want to extract.
Syntax
DBMS_WORKLOAD_REPOSITORY.AWR_EXP( dmpfile IN VARCHAR2 DEFAULT 'awrdat', dmpdir IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', dbid IN NUMBER DEFAULT NULL, bid IN NUMBER DEFAULT 1, eid IN NUMBER DEFAULT 1000000);
Parameters
Table 216-15 AWR_EXP Procedure Parameters
Parameter | Description |
---|---|
|
The prefix for the name of the extract dump file and log file. The name of the dump
file where all the data from the AWR table will be written is
dmpfile.dmp. The |
|
Name of the Directory Object for the file system directory where the extract dump file will be written. The list of Directory Objects can be queried using the
The default value is |
|
The database ID for the snapshots that you want to export. The default value is
|
bid |
The begin snapshot ID for snapshots to be exported. The default value is |
eid |
The end Snapshot Id for snapshots to be exported. The default value is |
216.3.10 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 216-16 AWR_GLOBAL_DIFF_REPORT_HTML Function Parameters
Parameter | Description |
---|---|
|
1st database identifier |
|
1st list of instance numbers. If set to |
bid1 |
1st beginning snapshot ID |
|
1st ending snapshot ID |
|
2nd database identifier |
|
2nd list of instance numbers to be included in report. If set to |
bid2 |
2nd beginning snapshot ID |
|
2nd ending snapshot ID |
Return Values
The output will be one column of VARCHAR2(1500)
.
216.3.11 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 216-17 AWR_GLOBAL_DIFF_REPORT_TEXT Functions Parameters
Parameter | Description |
---|---|
|
1st database identifier |
|
1st list of instance numbers. If set to |
bid1 |
1st beginning snapshot ID |
|
1st ending snapshot ID |
|
2nd database identifier |
|
2nd list of instance numbers to be included in report. If set to |
bid2 |
2nd beginning snapshot ID |
|
2nd ending snapshot ID |
Return Values
The output will be one column of VARCHAR2(320)
.
216.3.12 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 216-18 AWR_GLOBAL_REPORT_HTML Function Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
List of instance numbers to be included in report. If set to |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
Report level (currently not used) |
Return Values
The output will be one column of VARCHAR2
(1500)
.
216.3.13 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 216-19 AWR_GLOBAL_REPORT_TEXT Function Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
List of instance numbers to be included in report. If set to |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
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)
.
216.3.14 AWR_IMP Procedure
This procedure loads the AWR data from a dump file into the
SYS
schema.
Syntax
DBMS_WORKLOAD_REPOSITORY.AWR_IMP( dmpfile IN VARCHAR2 DEFAULT 'awrdat', dmpdir IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', new_dbid IN NUMBER DEFAULT NULL);
Parameters
Table 216-20 AWR_IMP Procedure Parameters
Parameter | Description |
---|---|
|
The prefix for the name of the dump file and log file. This file will be the source of the imported AWR data. The default value is |
|
The name of the Directory Object for the file system directory where the load dump file is located. The default value is |
|
The database ID to be used instead of existing database ID. |
216.3.15 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 216-21 AWR_REPORT_HTML Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
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(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.
216.3.16 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 216-22 AWR_REPORT_TEXT Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
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.
216.3.17 AWR_SET_REPORT_THRESHOLDS Procedure
This procedure configure specified report thresholds, including the number of rows in the report.
Syntax
DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS( top_n_events IN NUMBER DEFAULT NULL, top_n_files IN NUMBER DEFAULT NULL, top_n_segments IN NUMBER DEFAULT NULL, top_n_services IN NUMBER DEFAULT NULL, top_n_sql IN NUMBER DEFAULT NULL, top_n_sql_max IN NUMBER DEFAULT NULL, top_sql_pct IN NUMBER DEFAULT NULL, shmem_threshold IN NUMBER DEFAULT NULL, versions_threshold IN NUMBER DEFAULT NULL, top_n_disks IN NUMBER DEFAULT NULL, outlier_pct IN NUMBER DEFAULT NULL, outlier_cpu_pct IN NUMBER DEFAULT NULL);
Parameters
Table 216-23 AWR_SET_REPORT_THRESHOLDS Procedure Parameters
Parameter | Description |
---|---|
|
Number of most significant wait events to be included |
|
Number of most active files to be included |
|
Number of most active segments to be included |
|
Number of most active services to be included |
|
Number of most significant SQL statements to be included |
|
Number of SQL statements to be included if their activity is greater than that specified by |
|
Significance threshold for SQL statements between |
|
Shared memory low threshold |
|
Plan version count low threshold |
|
Number of cell disks with most I/O |
|
Percentage of maximum capacity before displaying outliers for Exadata sections |
|
Threshold for mean percentage CPU to display outliers |
User Notes
-
The effect of each setting depends on the type of report being generated as well as on the underlying AWR data. Not all settings are meaningful for each report type. Invalid settings (such as negative numbers) are ignored.
-
Settings are effective only in the context of the session that executes the
AWR_SET_REPORT_THRESHOLDS
procedure. For example, to get a report that lists top 12 segments as compared to the default, one can invoke as follows:DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS (top_n_segments=>12);
216.3.18 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 216-24 AWR_SQL_REPORT_HTML Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
SQL ID of statement to be analyzed |
|
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.
216.3.19 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 216-25 AWR_SQL_REPORT_TEXT Parameters
Parameter | Description |
---|---|
|
Database identifier |
|
Instance number |
|
Beginning snapshot ID |
|
Ending snapshot ID |
|
SQL ID of statement to be analyzed |
|
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.
216.3.20 CONTROL_RESTRICTED_SNAPSHOT Procedure
This procedure controls the AWR snapshot creation for a database in the restricted session mode. If this procedure is not used, then by default, the AWR snapshots cannot be created for a database in the restricted session mode. This procedure affects the local database on which it is executed.
Note:
To enable AWR snapshot creation for an Oracle RAC in the restricted session mode, this procedure must be executed on every database instance in the Oracle RAC.
Syntax
DBMS_WORKLOAD_REPOSITORY.CONTROL_RESTRICTED_SNAPSHOT( allow IN BOOLEAN);
Parameters
Table 216-26 CONTROL_RESTRICTED_SNAPSHOT Parameters
Parameter | Description |
---|---|
|
This parameter can have one of the following values:
|
216.3.21 CREATE_BASELINE Functions and 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 216-27 CREATE_BASELINE Function & Procedure Parameters
Parameter | Description |
---|---|
|
Start snapshot sequence number for the baseline. |
|
End snapshot sequence number for the baseline. |
|
Start time for the baseline. |
|
End time for the baseline. |
|
Name of baseline. |
|
Database Identifier for baseline. If |
|
Expiration in number of days for the baseline. If |
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.
216.3.22 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 216-28 CREATE_BASELINE_TEMPLATE Procedure Parameters
Parameter | Description |
---|---|
|
Start Time for the baseline to be created' |
|
End Time for the baseline to be created |
|
Name of baseline to be created |
|
Name for the template |
|
Expiration in number of days for the baseline. If |
|
Database ID for which the baseline template needs to be used. If |
|
Day of week that the baseline should repeat on. Specify one of the following values: |
|
Value of 0-23 to specify the Hour in the Day the baseline should start |
|
Duration (in number of hours) after hour in the day that the baseline should last |
|
Name for baseline prefix. When creating the baseline, the name of the baseline will be the prefix appended with the date information. |
216.3.23 CREATE_REMOTE_SNAPSHOT Function and Procedure
This function and procedure create a remote snapshot using the Remote Management Framework (RMF). The function returns the snapshot ID.
Syntax
Note:
A multitenant container database is the only supported architecture in Oracle Database 21c and later releases. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.
DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT( node_id IN NUMBER, flush_level IN VARCHAR2 DEFAULT 'BESTFIT'); DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT( node_id IN NUMBER, flush_level IN VARCHAR2 DEFAULT 'BESTFIT') RETURN NUMBER; DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT( node_name IN VARCHAR2, topology_name IN VARCHAR2 DEFAULT NULL, flush_level IN VARCHAR2 DEFAULT 'BESTFIT'); DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT( node_name IN VARCHAR2, topology_name IN VARCHAR2 DEFAULT NULL, flush_level IN VARCHAR2 DEFAULT 'BESTFIT') RETURN NUMBER;
Parameters
Table 216-29 CREATE_REMOTE_SNAPSHOT Parameters
Parameter | Description |
---|---|
|
RMF node identifier of the database for which the snapshot needs to be created. |
|
RMF node name of the database for which the snapshot needs to be created. |
|
RMF topology name of the database for which the snapshot needs to be created. |
|
The flush level can be one of the following:
|
Examples
This example creates a remote snapshot of the database having the RMF node identifier of 10
:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT(10);
If you query the DBA_HIST_SNAPSHOT
view after executing the above procedure, you will see a new snapshot record added to the Automatic Workload Repository (AWR).
216.3.24 CREATE_SNAPSHOT Function and Procedure
This function and procedure create a snapshot. The function returns the snapshot ID. If both, the database ID and the database name are not specified in this subprogram, then the snapshot is created for the local database on which the subprogram is executed.
Syntax
Note:
A multitenant container database is the only supported architecture in Oracle Database 21c and later releases. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT( flush_level IN VARCHAR2 DEFAULT 'BESTFIT', dbid IN NUMBER DEFAULT NULL, source_name IN VARCHAR2 DEFAULT NULL); DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT( flush_level IN VARCHAR2 DEFAULT 'BESTFIT', dbid IN NUMBER DEFAULT NULL, source_name IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
Parameters
Table 216-30 CREATE_SNAPSHOT Parameters
Parameter | Description |
---|---|
|
The flush level can be one of the following:
|
|
Database ID of the database for which the snapshot needs to be created. |
|
Name of the database for which the snapshot needs to be created. |
Examples
This example creates a snapshot of the local database with the flush level of ALL
:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
If you query the DBA_HIST_SNAPSHOT
view after executing the above procedure, you will see a new snapshot record added to the Automatic Workload Repository (AWR).
216.3.25 DROP_BASELINE Procedure
This procedure drops a previously-defined baseline.
Syntax
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE( baseline_name IN VARCHAR2, cascade IN BOOLEAN DEFAULT FALSE, dbid IN NUMBER DEFAULT NULL);
Parameters
Table 216-31 DROP_BASELINE Parameters
Parameter | Description |
---|---|
|
Name of baseline to drop from the system |
|
If |
|
Database ID for which the baseline needs to be dropped (defaults 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.
216.3.26 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 216-32 DROP_BASELINE_TEMPLATE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the template to remove |
|
Database ID for which the baseline template needs to be dropped. If |
216.3.27 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 216-33 DROP_SNAPSHOT_RANGE Procedure Parameters
Parameter | Description |
---|---|
|
Low snapshot id of snapshots to drop. |
|
High snapshot id of snapshots to drop. |
|
Database id (defaults 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.
216.3.28 LOCAL_AWR_DBID Function
This function returns the database identifier for the local AWR database.
Syntax
DBMS_WORKLOAD_REPOSITORY.LOCAL_AWR_DBID() RETURN NUMBER;
216.3.29 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 216-34 MODIFY_BASELINE_WINDOW_SIZE Procedure Parameters
Parameter | Description |
---|---|
|
New Window size for the default Moving Window Baseline, in number of days. |
|
Database ID (defaults 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. A moving window can be set to a maximum of 13 weeks.
216.3.30 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, tablespace_name IN VARCHAR2 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, tablespace_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 216-35 MODIFY_SNAPSHOT_SETTINGS Procedure Parameters
Parameter | Description |
---|---|
|
New retention time (in minutes). The specified value must be in the range of If If NOTE: The retention setting must be greater than or equal to the window size of the ' |
|
New interval setting between each snapshot, in units of minutes. The specified value must be in the range If If |
|
|
|
Database identifier in AWR for which to modify the snapshot settings. If |
|
Specify a user-defined tablespace for storing AWR data (snapshot data). If this parameter is not used, then AWR data is stored in the |
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.
216.3.31 PURGE_SQL_DETAILS Procedure
This procedure purges SQL details, specifically rows from WRH$_SQLTEXT
, WRH$_SQL_PLAN
, and WRH$_SQL_BIND_METADATA
that do not have corresponding rows (DBID
, SQL_ID
) in WRH$_SQLSTAT
.
The subprogram calls for the DBID for which to run the purge. If the DBID is not specified, the database DBID is used. You can constrain runtime by specifying the maximum number of rows to purge per table. If no maximum is specified, the subprograms tries to purge all applicable rows.
Syntax
DBMS_WORKLOAD_REPOSITORY.PURGE_SQL_DETAILS( numrows IN NUMBER DEFAULT NULL, dbid IN NUMBER DEFAULT NULL);
Parameters
Table 216-36 PURGE_SQL_DETAILS Procedure Parameters
Parameter | Description |
---|---|
|
Number of rows |
|
Database ID (default to local DBID) |
216.3.32 REGISTER_REMOTE_DATABASE Procedures
This procedure registers a remote database in the Automatic Workload Repository (AWR) using the Remote Management Framework (RMF).
Syntax
DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE( node_id IN NUMBER); DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE( node_name IN VARCHAR2, topology_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 216-37 REGISTER_REMOTE_DATABASE Parameters
Parameter | Description |
---|---|
|
RMF node identifier of the database that needs to be registered in the AWR. |
|
RMF node name of the database that needs to be registered in the AWR. |
|
RMF topology name of the database that needs to be registered in the AWR. |
Examples
This example registers the remote database having the RMF node identifier of 10
in the AWR:
EXECUTE DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE(10);
216.3.33 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.REMOVE_COLORED_SQL( sql_id IN VARCHAR2, dbid IN NUMBER DEFAULT NULL);
Parameters
Table 216-38 REMOVE_COLORED_SQL Procedure Parameters
Parameter | Description |
---|---|
|
13-character external SQL ID |
|
Optional dbid, defaults to Local |
216.3.34 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 216-39 RENAME_BASELINE Procedure Parameters
Parameter | Description |
---|---|
|
Old baseline name. |
|
New baseline name. |
|
Database ID for which the baseline needs to be renamed (defaults to local DBID). |
216.3.35 SELECT_BASELINE_DETAILS Function
This table function shows the values of the metrics corresponding to a baseline for a range of snapshots.
This table function returns an object of AWR_BASELINE_METRIC_TYPE Object Type.
Syntax
DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_DETAILS( l_baseline_id IN NUMBER, l_begin_snap IN NUMBER DEFAULT NULL, l_end_snap IN NUMBER DEFAULT NULL, l_dbid IN NUMBER DEFAULT NULL) RETURN awrbl_details_type_table PIPELINED;
Parameters
Table 216-40 SELECT_BASELINE_DETAILS Function Parameters
Parameter | Description |
---|---|
|
ID of the baseline for which the statistics need to be retrieved. Specifying the value 0 returns the statistics for the moving window baseline. |
|
Start snapshot sequence number for the baseline. |
|
End snapshot sequence number for the baseline. |
|
Database identifier for the baseline. If its value is set to |
216.3.36 SELECT_BASELINE_METRIC Function
This table function shows the values of the metrics corresponding to a baseline for all the snapshots.
This table function returns an object of AWR_BASELINE_METRIC_TYPE Object Type.
Syntax
DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRIC( l_baseline_name IN VARCHAR2, l_dbid IN NUMBER DEFAULT NULL, l_instance_num IN NUMBER DEFAULT NULL) RETURN awr_metric_type_table PIPELINED;
Parameters
Table 216-41 SELECT_BASELINE_METRIC Function Parameters
Parameter | Description |
---|---|
|
Name of the baseline for which the metrics need to be viewed. |
|
Database identifier for the baseline. If set to |
|
The instance number for which the metrics need to be viewed. If set to |
216.3.37 UNREGISTER_REMOTE_DATABASE Procedures
This procedure removes all the statistics, metadata, partitions, and so on of a remote database from the Automatic Workload Repository (AWR). After executing this procedure, the remote database cannot be used for any AWR operations, such as creating remote snapshots.
Syntax
DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE( node_id IN NUMBER, remote_check IN BOOLEAN DEFAULT TRUE); DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE( node_name IN VARCHAR2, topology_name IN VARCHAR2 DEFAULT NULL, remote_check IN BOOLEAN DEFAULT TRUE);
Parameters
Table 216-42 UNREGISTER_REMOTE_DATABASE Parameters
Parameter | Description |
---|---|
|
Identifier of the remote database whose data needs to be removed from the AWR. |
|
Name of the remote database whose data needs to be removed from the AWR. |
|
RMF topology name of the remote database. |
|
If set to If set to |
Examples
This example removes the AWR data related to the remote database having the database identifier of 10
:
EXECUTE DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE(10);
216.3.38 UPDATE_DATAFILE_INFO Procedure
This procedure updates the data file and tablespace information stored in the Automatic Workload Repository (AWR) with the current information in the database. This procedure is useful when a data file or a tablespace has been moved or renamed. As this change is not always captured in the next snapshot, AWR report may not show the most current information.
Syntax
DBMS_WORKLOAD_REPOSITORY.UPDATE_DATAFILE_INFO();
216.3.39 UPDATE_OBJECT_INFO Procedure
This procedure updates rows of WRH$_SEG_STAT_OBJ
table that represent objects in the local database. It attempts to determine the current names for all object belonging to the local database, except those with 'MISSING'
and/or 'TRANSIENT'
values in the name columns.
The amount of work performed at each invocation of this routine may be controlled by setting the input parameter.
Syntax
DBMS_WORKLOAD_REPOSITORY.UPDATE_OBJECT_INFO( maxrows IN NUMBER DEFAULT 0);
Parameters
Table 216-43 UPDATE_OBJECT_INFO Procedure Parameters
Parameter | Description |
---|---|
|
Maximum number of rows to be updated. Default= |