Oracle DBMS_WORKLOAD_REPOSITORY
Version 11.2
 
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  
File 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;
TBD
 
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(
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;
TBD
 
AWR_GLOBAL_DIFF_REPORT_HTML (new 11.2)

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)

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)

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)

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)

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 (new 11.1.0.7)
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
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;

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(14400, 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));
 
Related Topics
Active Session History
AWR Report
Files of Interest
 
Morgan's Library Page Footer
This is site maintained by Dan Morgan. Last Updated: Contact Us Legal Notices & Terms of Use  Privacy Statement