Oracle DBMS_WORKLOAD_REPOSITORY
Version 11.2.0.3
 
AWR Objects
Source {ORACLE_HOME}/rdbms/admin/dbmsawr.sql
First Availability 10.1
Background Process MMON - Automatic data purging every 7 days by default
Constants
Name Retention Data Type Value
MAX_INTERVAL 100 years NUMBER 52560000
MIN_INTERVAL 10 minutes NUMBER 10
MAX_RETENTION 100 years NUMBER 52560000
MIN_RETENTION 1 day NUMBER 1440
Data Types AWRRPT_TEXT_TYPE
AWRRPT_HTML_TYPE
AWRRPT_TEXT_TYPE_TABLE
AWRRPT_HTML_TYPE_TABLE
SYS AWRRPT_ROW_TYPE

CREATE OR REPLACE TYPE AWRRPT_INSTANCE_LIST_TYPE AS TABLE OF NUMBER;
Dependencies
dba_hist_baseline dba_hist_snapshot
awrrpt_html_type plitblm
awrrpt_html_type_table wrm$_baseline
awrrpt_text_type wrm$_snapshot
awrrpt_type_table wrm$_snap_error
dbms_swrf_lib wrm$_wr_control
dbms_swrf_report_internal
Scripts that create the AWR schema {ORACLE_HOME}/rdbms/admin/catawr.sql
{ORACLE_HOME}/rdbms/admin/catawrpd.sql
{ORACLE_HOME}/rdbms/admin/catawrtb.sql
{ORACLE_HOME}/rdbms/admin/catawrwv.sql
-- must be run as SYSDBA
 
ADD_COLORED_SQL
Routine to add a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). Capturing will occur if the SQL is found in the cursor cache at snapshot time. dbms_workload_repository.add_colored_sql(
sql_id IN VARCHAR2,
dbid   IN NUMBER DEFAULT NULL);
desc wrm$_colored_sql

SELECT * FROM wrm$_colored_sql;

SELECT dbid
FROM v$database;

SELECT sql_id
FROM gv$sql
WHERE rownum < 101;

exec dbms_workload_repository.add_colored_sql('5rygsj4dbw6jt', 1692970157);

SELECT * FROM wrm$_colored_sql;

exec dbms_workload_repository.remove_colored_sql('5rygsj4dbw6jt', 1692970157);

SELECT * FROM wrm$_colored_sql;
 
ASH_REPORT_HTML
Display the ASH report in HTML 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;
SELECT dbid
FROM v$database;

SELECT inst_id
FROM gv$instance;

SELECT sample_time
FROM gv$active_session_history
ORDER BY 1;

set pagesize 0
set linesize 121

spool c:\temp\ash_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.ash_report_html(1692970157, 1, SYSDATE-30/1440, SYSDATE-1/1440));

spool off
Alternative ASH HTML Report define report_type = 'html';
define begin_time = '-30'
define duration = '';
define report_name = 'c:\temp\ashrpt.html';
@?/rdbms/admin/ashrpt
Alternative ASH HTML Report define report_type = 'html';
define begin_time = '-30'
define duration = '';
define report_name = 'c:\temp\ashrpt.html';
@?/rdbms/admin/ashrpti
 
ASH_REPORT_TEXT
Display the ASH report in TEXT 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;
SELECT dbid
FROM v$database;

SELECT inst_id
FROM gv$instance;

SELECT sample_time
FROM gv$active_session_history
ORDER BY 1;

set pagesize 0
set linesize 121

spool c:\temp\ash_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.ash_report_text(1692970157, 1, SYSDATE-30/1440, SYSDATE-1/1440));

spool off
Alternative ASH Text Report define report_type = 'text';
define begin_time = '-30'
define duration = '';
define report_name = 'c:\temp\ashrpt.txt';
@?/rdbms/admin/ashrpt
Alternative ASH Text Report define report_type = 'text';
define begin_time = '-30'
define duration = '';
define report_name = 'c:\temp\ashrpt.txt';
@?/rdbms/admin/ashrpti
 
AWR_DIFF_REPORT_HTML
This table function displays the AWR Compare Periods Report in HTML format. The output is one column of VARCHAR2(5000). 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 awrrpt_html_type_table PIPELINED;
spool /stage/diffrep_node5.html

SELECT * FROM TABLE(
dbms_workload_repository.awr_diff_report_html(782247420, 5, 7492, 7503, 782247420, 5, 7664, 7675));

spool off
 
AWR_DIFF_REPORT_TEXT
This table function displays the AWR Compare Periods Report in TEXT format. The output is one column of VARCHAR2(240). 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;
spool /stage/diffrep_node6.html

SELECT * FROM TABLE(
dbms_workload_repository.awr_diff_report_text(782247420, 6, 7492, 7503, 782247420, 6, 7664, 7675));

spool off
 
AWR_GLOBAL_DIFF_REPORT_HTML (new 11.2.0.1)
Displays the Gobal AWR Compare Periods Report in HTML format. The output is one column of VARCHAR2(1500).

Overload 1		 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;
TBD
Overload 2

Encapsulated in awrgdrpt.sql, awrgdrpi.sql, and awrgdinp.sql		 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;
select dbid from v$database;

set feedback off
set heading on
set linesize 1500
set termout on
set trim on
set trimspool on
set veri off
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set trimspool on
set trim on
spool c:\temp\demo.html

SELECT * FROM TABLE(dbms_workload_repository.awr_global_diff_report_html(2497516142, '1', 65, 70, 2497516142, '1', 71, 75));

spool off
 
AWR_GLOBAL_DIFF_REPORT_TEXT (new 11.2.0.1)
Displays the Global AWR Compare Periods Report in text format. The output is one column of VARCHAR2(320)

Overload 1		 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;
TBD
Overload 2 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;
select dbid from v$database;

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set trimspool on
set trim on
spool c:\temp\demo.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_global_diff_report_text(2497516142, '1', 65, 70, 2497516142, '1', 71, 75));

spool off
 
AWR_GLOBAL_REPORT_HTML (new 11.2.0.1)
Displays the AWR report in HTML

Overload 1		 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;
TBD
Overload 2 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;
SELECT dbid FROM v$database;

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set trimspool on
set trim on
spool c:\temp\demo.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_global_report_html(2497516142, '1', 65, 70, 0));

spool off
 
AWR_GLOBAL_REPORT_TEXT (new 11.2.0.1)
Displays the AWR report in Text

Overload 1		 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 awrrpt_html_type_table PIPELINED;
TBD
Overload 2 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 awrrpt_html_type_table PIPELINED;
SELECT dbid FROM v$database;

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set trimspool on
set trim on
spool c:\temp\demo.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_global_report_text(2497516142, '1', 65, 70, 0));

spool off
 
AWR_REPORT_HTML
Display the AWR report in HTML 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;

awrrpt_text_type_table is VARCHAR2(150)
See AWR Report demo linked at the bottom of the page
 
AWR_REPORT_TEXT
Display the AWR report in ASCII text 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;

awrrpt_text_type_table is VARCHAR2(80)
See AWR Report demo linked at the bottom of the page
 
AWR_SET_REPORT_THRESHOLDS (new 11.2.0.1)
Allows configuring of specified report thresholds. Allows control of the number of report rows 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);
exec dbms_workload_repository.awr_set_report_thresholds(9, 8, 7, 6, 5, 4, 3, 2, 1);

-- used by $ORACLE_HOME/rdbms/admin/awrgdrpi.sql and awrgrpti.sql
 
AWR_SQL_REPORT_HTML
Display the AWR SQL report in HTML 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;
SELECT dbid FROM v$database;

SELECT inst_id
FROM gv$instance;

set pagesize 0
set linesize 121
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT sql_id
FROM gv$active_session_history
WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE);

spool c:\temp\awr_sql_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(1692970157, 1, 1230, 1231, 'a01hp0psv0rrh'));

spool off
 
AWR_SQL_REPORT_TEXT
Display the AWR SQL report in TEXT 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 awrsqrpt_text_type_table PIPELINED;
SELECT dbid FROM v$database;

SELECT inst_id
FROM gv$instance;

set pagesize 0
set linesize 121
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT sql_id
FROM gv$active_session_history
WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE);

spool c:\temp\awr_sql_rpt.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_text(1692970157, 1, 1230, 1231, 'a01hp0psv0rrh'));

spool off
 
CONTROL_RESTRICTED_SNAPSHOT
Controls if AWR snapshots are allowed to occur even if the restricted session mode has been enabled for the database. TRUE allows snapshot capture in restricted session mode. dbms_workload_repository.control_restricted_snapshot(allow IN BOOLEAN);
BEGIN
  dbms_workload_repository.control_restricted_snapshot(TRUE);
END;
/
 
CREATE_BASELINE
Creates a baseline returns the baseline_id

Overload 1		 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;
SELECT dbid FROM v$database;

set linesize 121
col startup_time format a40

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

set serveroutput on

DECLARE
 i dba_hist_baseline.baseline_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_baseline(1199, 1207,
  'UW_BASE', 1692970157);
  dbms_output.put_line(TO_CHAR(i));
END;
/

SELECT baseline_id, baseline_name
FROM dba_hist_baseline;
Overload 2 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);
SELECT dbid FROM v$database;

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.create_baseline(1199, 1207, 'UW_BASE', 1692970157);

SELECT baseline_name, dbid
FROM dba_hist_baseline;
 
CREATE_BASELINE_TEMPLATE
Creates a Baseline Template for a single time period. There will be a MMON task that will use these inputs to create a Baseline for the time period when the time comes.

Overload 1		 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 DEFAULT NULL,
dbid          IN NUMBER DEFAULT NULL);
desc dba_hist_baseline_template

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.create_baseline_template(SYSDATE+1/1440, SYSDATE+5/1440, 'UW_BASE2', 'UW_TEMPLATE', 1);

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;
Overload 2 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 DEFAULT 35,
dbid                 IN NUMBER DEFAULT NULL);
TBD
 
CREATE_SNAPSHOT
Create snapshot and return snapshot ID

Overload 1		 dbms_workload_repository.create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN NUMBER;

Flush Levels
ALL
TYPICAL
set linesize 121
col begin_interval_time format a30
col end_interval_time format a30

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;

set serveroutput on

DECLARE
 i dba_hist_snapshot.snap_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_snapshot;
  dbms_output.put_line(TO_CHAR(i));
END;
/

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;
Overload 2 dbms_workload_repository.create_snapshot(
flush_level IN VARCHAR2 DEFAULT 'TYPICAL');
 
Flush Levels
ALL
TYPICAL
col begin_interval_time format a30
col end_interval_time format a30

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;

exec dbms_workload_repository.create_snapshot;

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;
 
DROP_BASELINE
Drop a baseline dbms_workload_repository.drop_baseline(
baseline_name IN VARCHAR2,
cascade       IN BOOLEAN DEFAULT FALSE,
dbid          IN NUMBER  DEFAULT NULL);
 
Cascade
False Drop baseline but not snapshots
True Drops baseline and snapshots
SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.drop_baseline('UW_BASE', FALSE, 1692970157);

SELECT baseline_name, dbid
FROM dba_hist_baseline;
 
DROP_BASELINE_TEMPLATE
Drops a Baseline Template dbms_workload_repository.drop_baseline_template(
template_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL);
SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;

exec dbms_workload_repository.drop_baseline_template('UW_TEMPLATE');

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;
 
DROP_SNAPSHOT_RANGE
Drop a range of snapshots dbms_workload_repository.drop_snapshot_Range(
low_snap_id  IN NUMBER,
high_snap_id IN NUMBER
dbid         IN NUMBER DEFAULT NULL);
set linesize 121
col startup_time format a40

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

exec dbms_workload_repository.drop_snapshot_range(1105, 1199);

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;
 
MODIFY_BASELINE_WINDOW_SIZE
Modifies the window size for the default moving window baseline

Installation default is 8 days		 dbms_workload_repository.modify_baseline_window_size(
window_size IN NUMBER,
dbid        IN NUMBER DEFAULT NULL);
set linesize 121
col baseline_name format a30

SELECT dbid, baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline;

exec dbms_workload_repository.modify_baseline_window_size(5);

SELECT dbid, baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline;

exec dbms_workload_repository.modify_baseline_window_size(8);
 
MODIFY_SNAPSHOT_SETTINGS
Modifies the interval between snapshots and/or the retention of snapshots in the repository

Overload 1
Note: Some of this functionality also exists in DBMS_MANAGEMENT_PACKS
MODIFY_AWR_SETTINGS proc		 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);
 
Defaults
Retention 7 days = 10080 minutes
Interval 60 minutes *
* Reset to 15-30 min. maximum between snapshots
set linesize 121
col retention format a20
col snap_interval format a20

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;

SELECT dbid
FROM v$database;

exec dbms_workload_repository.modify_snapshot_settings((24*60*10), 20, 1000, 1701481905);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;
Overload 2 dbms_workload_repository.modify_snapshot_settings(
retention IN NUMBER   DEFAULT NULL,
interval  IN NUMBER   DEFAULT NULL,
topnsql   IN VARCHAR2 DEFAULT NULL,
dbid      IN NUMBER   DEFAULT NULL);
 
Defaults
Retention 7 days = 10080 minutes
Interval 60 minutes *
* Reset to 15-30 min. maximum between snapshots
set linesize 121
col retention format a20
col snap_interval format a20

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;

SELECT dbid
FROM v$database;

exec dbms_workload_repository.modify_snapshot_settings(14400, 20, '1001', 1701481905);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;
 
PURGE_SQL_DETAILS
Purges rows from the AWR SQL details tables
(WRH$_SQLTEXT and WHR$_SQL_PLAN) that are no longer required		 dbms_workload_repository.purge_sql_details(
numrows IN NUMBER DEFAULT NULL,
dbid    IN NUMBER DEFAULT NULL);
SELECT dbid FROM v$database;

exec dbms_workload_repository.purge_sql_details(10, 2497516142);
 
REMOVE_COLORED_SQL
Routine to remove a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). dbms_workload_repository.remove_colored_sql(
sql_id IN VARCHAR2,
dbid   IN NUMBER DEFAULT NULL);
See ADD_COLORED_SQL Demo Above
 
RENAME_BASELINE
Rename a baseline dbms_workload_repository.rename_baseline(
old_baseline_name IN VARCHAR2,
new_baseline_name IN VARCHAR2,
dbid              IN NUMBER DEFAULT NULL);
SELECT dbid, baseline_name, baseline_type
FROM dba_hist_baseline;

exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE2');

SELECT dbid, baseline_name, baseline_type
FROM dba_hist_baseline;

exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE');
 
SELECT_BASELINE_DETAILS
Display baseline statistics dbms_workload_repository.select_baseline_metrics(
l_baseline_id IN NUMBER,
l_beg_snap IN NUMBER DEFAULT NULL,
l_end_snap IN NUMBER DEFAULT NULL,
l_dbid     IN NUMBER DEFAULT NULL)
RETURN awrbl_details_type_table PIPELINED;
SELECT dbid, baseline_id, baseline_name, baseline_type
FROM dba_hist_baseline;

set linesize 121
col start_snap_time format a30
col end_snap_time format a30

SELECT *
FROM TABLE(dbms_workload_repository.select_baseline_details(1));
 
SELECT_BASELINE_METRIC
Display metric stats for a baseline 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 awrbl_metric_type_table PIPELINED;
SELECT dbid, baseline_id, baseline_name, baseline_type
FROM dba_hist_baseline;

set pagesize 0
set linesize 121

SELECT *
FROM TABLE(dbms_workload_repository.select_baseline_metric(0));
 
UPDATE_OBJECT_INFO (new in 11.2.0.2)
Updates rows of WRH$_SEG_STAT_OBJ table that represent objects in the local database dbms_workload_repository.update_object_info(maxrows IN NUMBER DEFAULT 0);
exec dbms_workload_repository.update_object_info(120000);
 
Related Topics
Active Session History
ASH
AWR Report
DBMS_MANAGEMENT_PACKS
Files of Interest
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved