Oracle | DBMS_AWR

Oracle DBMS_AWR_ANALYTICS Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose AWR Analytics Report API

Support for log based replication (proj 17779) DBMS_AWR_ANALYTICS package is used for analytic functions
-- reading existing AWR data accessible through views/tables that generate no UNDO/REDO and can be replicated.

AUTHID DEFINER

Dependencies

DBMS_AUTO_TASK

DBMS_AWR_ANALYTICS_LIB

Documented No

Exceptions

Error Code	Reason
ORA-15900	AWR Analytics operatoin failed: feature is disabled
ORA-13509	error encountered during processing an AWR table
ORA-13516	AWR Operation failed: Request does not exist

First Available 20c

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsawrrptapp.sql
{ORACLE_HOME}/rdbms/admin/prvtawrrptapp.plb

Subprograms

AWR_REPORT_REQUEST	FETCH_REPORT	GENERATE_REPORT
CHECK_REPORT_STATUS

AWR_REPORT_REQUEST (new 23c)

Generate a request for an AWR report in text, HTML, or Active-HTML dbms_awr_analytics.awr_report_request( l_dbid IN NUMBER, l_inst_num IN NUMBER, l_bid IN NUMBER, l_eid IN NUMBER, l_options IN NUMBER DEFAULT 0, l_format IN VARCHAR2 DEFAULT 'html', l_await IN VARCHAR2 DEFAULT 'true') RETURN NUMBER; PRAGMA SUPPLEMENTAL_LOG_DATA(AWR_REPORT_REQUEST, UNSUPPORTED);

SELECT dbid FROM v$database;

      

            DBID

      ----------

      1405253007

      

      SELECT MAX(snap_id)-12 AS BEG_SNAP_ID, MAX(snap_id)-1 AS END_SNAPID

      FROM dba_hist_snapshot;

      

      BEG_SNAP_ID END_SNAPID

      ----------- ----------

              209        220

      

      SELECT dbms_awr_analytics.awr_report_request(1405253007, 1, 209, 220);

             *

      ORA-13509: error encountered during processing an AWR table

CHECK_REPORT_STATUS (new 23c)

Undocumented dbms_awr_analytics.check_report_status(report_id IN NUMBER) RETURN VARCHAR2;

SELECT dbms_awr_analytics.check_report_status(2);

             *

      ORA-13516: AWR Operation failed: Request does not exist

FETCH_REPORT (new 23c)

Fetch repot contents oafter a request with AWR_REPORT_REQUEST API dbms_awr_analytics.fetch_report(report_id IN NUMBER) RETURN CLOB;

SELECT dbms_awr_analytics.fetch_report(1);

             *

      ORA-13516: AWR Operation failed: Request does not exist

GENERATE_REPORT

Presumably generates an AWR Analytics Report if the feature is enabled dbms_awr_analytics.generate_report( dbid IN NUMBER, beg_snap IN NUMBER, end_snap IN NUMBER, format IN VARCHAR2 DEFAULT 'HTML', options IN CLOB DEFAULT NULL) RETURN CLOB;

SELECT dbid FROM v$database;

      

            DBID

      ----------

      1939439852

      

      SELECT MAX(snap_id)-12 AS BEG_SNAP_ID, MAX(snap_id)-1 AS END_SNAPID

      FROM dba_hist_snapshot;

      

      BEG_SNAP_ID END_SNAPID

      ----------- ----------

              340        351

      

      DECLARE

       retVal CLOB;

      BEGIN

        retVal := dbms_awr_analytics.generate_report(2140826538, 340, 351, 'TEXT', RetVal);

        dbms_output.put_line(retVal);

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-15900: AWR Analytics operation failed: feature is disabled

      ORA-06512: at "SYS.DBMS_AWR_ANALYTICS", line 3

      ORA-06512: at "SYS.DBMS_AWR_ANALYTICS", line 44

      ORA-06512: at line 4