Oracle DBMS_WORKLOAD_REPOSITORY
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose API for managing the Workload Repository and performing operations such as managing snapshots and baselines for ASH and AWR reports.
AUTHID DEFINER
Background Process MMON - Automatic data purging every 7 days by default
Constants
Name Data Type Value
MAX_INTERVAL NUMBER 52560000 (100 years)
MIN_INTERVAL NUMBER 10 (10 minutes)
MAX_RETENTION NUMBER 52560000 (100 years)
MIN_RETENTION NUMBER 1440 (1 day)
Data Types --The following types are stand-alone objects owned by SYS: Not defined in the package header.

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_HTML_TYPE
AS OBJECT (output VARCHAR2(8000 CHAR));

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_HTML_TYPE_TABLE
AS TABLE OF AWRRPT_HTML_TYPE;

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_TEXT_TYPE
AS OBJECT (output VARCHAR2(80 CHAR));

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_TEXT_TYPE_TABLE
AS TABLE OF AWRRPT_TEXT_TYPE;

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_ROW_TYPE
AS OBJECT (
num_dfn AWRRPT_NUM_ARY,
vch_dfn AWRRPT_VCH_ARY,
clb_dfn AWRRPT_CLB_ARY);

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_INSTANCE_LIST_TYPE
AS TABLE OF NUMBER
Dependencies
AWRBL_DETAILS_TYPE AWRSQRPT_TEXT_TYPE DBMS_SWRF_INTERNAL
AWRBL_DETAILS_TYPE_TABLE AWRSQRPT_TEXT_TYPE_TABLE DBMS_SWRF_LIB
AWRBL_METRIC_TYPE DBA_HIST_BASELINE DBMS_SWRF_REPORT_INTERNAL
AWRBL_METRIC_TYPE_TABLE DBA_HIST_BASELINE_DETAILS DBMS_WORKLOAD_CAPTURE
AWRDRPT_TEXT_TYPE DBA_HIST_BASELINE_METADATA DBMS_WORKLOAD_REPLAY
AWRDRPT_TEXT_TYPE_TABLE DBA_HIST_SNAPSHOT MGMT_BSLN_INTERVALS
AWRRPT_HTML_TYPE DBA_HIST_SYSMETRIC_SUMMARY PLITBLM
AWRRPT_HTML_TYPE_TABLE DBMS_ASH_INTERNAL V$DATABASE
AWRRPT_INSTANCE_LIST_TYPE DBMS_MANAGEMENT_PACKS V$INSTANCE
AWRRPT_TEXT_TYPE DBMS_REPORT XMLTYPE
AWRRPT_TEXT_TYPE_TABLE DBMS_STANDARD  
Documented Yes
First Available 10gR1
Initialization Parameters
_awr_cdbperf_threshold _awr_mmon_cpuusage _awr_restrict_mode
_awr_corrupt_mode _awr_mmon_deep_purge_all_expired _awr_sql_child_limit
_awr_disabled_flush_tables _awr_mmon_deep_purge_interval _flush_plan_in_awr_sql
_awr_flush_threshold_metrics _awr_pdb_registration_enabled _remote_awr_enabled
_awr_flush_workload_metrics _awr_remote_target_dblink  
Security Model Owned by SYS with EXECUTE granted to
Source {ORACLE_HOME}/rdbms/admin/dbmsawr.sql

-- also the following scripts create the AWR schema objects
{ORACLE_HOME}/rdbms/admin/catawr.sql
{ORACLE_HOME}/rdbms/admin/catawrpd.sql
{ORACLE_HOME}/rdbms/admin/catawrtb.sql
{ORACLE_HOME}/rdbms/admin/catawrwv.sql
-- all must be run as SYSDBA
Subprograms
 
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', 428676178);

SELECT * FROM wrm$_colored_sql;

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

SELECT * FROM wrm$_colored_sql;
 
ASH_GLOBAL_REPORT_HTML (new 12.1)
Return the ASH Spot report in text format as one column of VARCHAR2(80) dbms_workload_repository.ash_global_report_html(
l_dbid         IN NUMBER,
l_inst_num     IN VARCHAR2,
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;
conn sys@pdbdev as sysdba

SELECT dbid
FROM v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_html(428676178, 1, SYSDATE-1, SYSDATE, l_wait_class=>'Other'));
 
ASH_GLOBAL_REPORT_TEXT (new 12.1)
Returns the ASH Spot report in html format as one column of VARCHAR2(500) dbms_workload_repository.ash_global_report_text(
l_dbid         IN NUMBER,
l_inst_num     IN VARCHAR2,
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 awrdrpt_text_type_table PIPELINED;
conn sys@pdbdev as sysdba

SELECT dbid
FROM v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(428676178, 1, SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));
 
ASH_REPORT_ANALYTICS (new 12.1)
Return the ASH (analytics) active report in HTML format dbms_workload_repository.ash_report_analytics(
dbid         IN NUMBER := NULL,
inst_id      IN NUMBER := NULL,
begin_time   IN DATE,
end_time     IN DATE,
report_level IN VARCHAR2 := NULL,
filter_list  IN VARCHAR2 := NULL)
RETURN CLOB;
SELECT dbid
FROM v$database;

SELECT dbms_workload_repository.ash_report_analytics(428676178, 1, SYSDATE-1/24, SYSDATE)
FROM dual;

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_ANALYTICS(428676178,1,SYSDATE-1/24,SYSDATE)
--------------------------------------------------------------------------------
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <base href="http://download.oracle.com/otn_software/"/>
  <script language="javascript" type="text/javascript">
   <!--
      var version = "12.1.0.1.0";

         document.write(';<script language="javascript" type="text/javascript" ';
+
                        ';src="emviewers/scripts/activeReportInit.js?'; +
                        Math.floor((new Date()).getTime()/(7*24*60*60*1000)) +
                       ';"></'; + ';script>';);
      -->
  </script>
 </head>
 <body onload="sendXML();">
  <script type="text/javascript">
       writeIframe();
  </script>
  <script id="fxtmodel" type="text/xml">
   <!--FXTMODEL-->
   <report db_version="12.1.0.1.0" cpu_cores="2" hyperthread="Y" con_id="1"
    con_name="CDB$ROOT" timezone_offset="-28800" elapsed_time="0.00" cpu_time="0.00"
    packs="2" encode="base64" compress="zlib">
   <report_id><![CDATA[/orarep/ash/viewer?begin_time=18:09:47 02/16/2014&dbid=4
28676178&end_time=19:09:47 02/16/2014&inst_id=1]]></report_id>
eAFtjjEOwyAQBHtewQ+OQwiwdeIryIhTRGFsYZr8PsFKLBfudnemWGq8b63HfWnL
yp3bEYSUlFPJwWhvnUXnCc4+QKlHj1+GBP845sSvUmMvKwf0s5pm46TSgBa0QkNw
40Pnmn/y9CBfVBA83KO05TcE8QEHIjjm
            </report>
   <!--FXTMODEL-->
  </script>
 </body>
</html>
 
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(428676178, 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(428676178, 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 as  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
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
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
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
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;
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;
See AWR Report demo linked at the bottom of the page
 
AWR_SET_REPORT_THRESHOLDS
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 format 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(428676178, 1, 1230, 1231, 'a01hp0psv0rrh'));

spool off
 
AWR_SQL_REPORT_TEXT
Display the AWR SQL report in TEXT format 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(428676178, 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.

It does so by modifying an undocumented initialization parameter as shown.
dbms_workload_repository.control_restricted_snapshot(allow IN BOOLEAN);
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv(';Instance';)
AND y.inst_id = userenv(';Instance';)
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = ';_';
AND lower(x.ksppdesc) like ';%awr%restrict%';
ORDER BY 1;

BEGIN
  dbms_workload_repository.control_restricted_snapshot(TRUE);
END;
/

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv(';Instance';)
AND y.inst_id = userenv(';Instance';)
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = ';_';
AND lower(x.ksppdesc) like ';%awr%restrict%';
ORDER BY 1;
 
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);
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', 428676178);

SELECT baseline_name, dbid
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)
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', 428676178);
  dbms_output.put_line(TO_CHAR(i));
END;
/

SELECT baseline_id, baseline_name
FROM dba_hist_baseline;
Overload 3 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);
exec dbms_workload_repository.create_baseline(SYSDATE-2/24, SYSDATE-1/24, 'UW_BASE', 428676178);
Overload 4 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;
DECLARE
 i dba_hist_baseline.baseline_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_baseline(SYSDATE-2/24, SYSDATE-1/24, 'UW_BASE', 428676178);
  dbms_output.put_line(TO_CHAR(i));
END;
/
 
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, 428676178);

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 *
* Recommend this be 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
WHERE dbid = (SELECT dbid FROM v$database);

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
WHERE dbid = (SELECT dbid FROM v$database);
Overload 2 dbms_workload_repository.modify_snapshot_settings(
retention IN NUMBER   DEFAULT NULL,
interval  IN NUMBER   DEFAULT NULL,
topnsql   IN VARCHAR2,
dbid      IN NUMBER   DEFAULT NULL);

Defaults
RETENTION 7 days = 10080 minutes
INTERVAL 60 minutes *
* Recommend this be reset to 15-30 min. maximum between snapshots
TOPNSQL
Users are allowed to specify the following values: ('DEFAULT', 'MAXIMUM', 'N')

Specifying 'DEFAULT' will revert the system back to the default behavior of Top 30 for level TYPICAL and Top 100 for level ALL.

Specifying 'MAXIMUM' will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number 'N' is equivalent to setting the Top N SQL with the NUMBER type.

Specifying 'N' will cause the system to flush the Top N SQL for each criteria. The 'N' string is converted into the number for Top N SQL.
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, 'MAXIMUM', 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 SQL 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(0));
 
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
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 Report
AWRINFO_UTIL
AWR Report
DBMS_MANAGEMENT_PACKS
Files of Interest
Packages
Startup Parameters

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-2014 Daniel A. Morgan All Rights Reserved