Oracle | MGMT_RESPONSE

Oracle MGMT_RESPONSE Version 21c

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 Utilities

AUTHID DEFINER

Dependencies

DBA_USERS	MGMT_HISTORY	MGMT_SNAPSHOT
DBMS_OUTPUT	MGMT_HISTORY_SQL	MGMT_SNAPSHOT_SQL
DUAL	MGMT_LATEST	MGMT_TEMPT_SQL
MGMT_BASELINE	MGMT_LATEST_SQL	V$INSTANCE
MGMT_BASELINE_SQL	MGMT_RESPONSE_CAPTURE_ID	V$SQLAREA
MGMT_CAPTURE	MGMT_RESPONSE_CONFIG	V$SYSSTAT
MGMT_CAPTURE_SQL	MGMT_RESPONSE_SNAPSHOT_ID

Documented Yes: See the Oracle Database Plug-in Metric Reference Manual

First Available Not known

Security Model Owned by DBSNMP with EXECUTE granted to the OEM_MONITOR role.

Source {ORACLE_HOME}/rdbms/admin/response.plb

Subprograms

CALC_METRIC	GETVERSION	PURGE_OLD_STATS
CAPTURE_STATS	GET_LATEST_CURS	RESET_CAPTURE_STATS
CREATE_BASELINE	GET_METRIC_CURS	RESET_SNAPSHOT_STATS
DELETE_BASELINE	GET_TIME_STATS	SAVE_TO_HISTORY
DELETE_SQL_FROM_BASELINE	PURGE_OLD_HISTORY	TAKE_V$SQL_SNAPSHOT
GETDURATION

CALC_METRIC

Undocumented mgmt_response.calc_metric(metric OUT NUMBER);

DECLARE

       outVal NUMBER;

      BEGIN

        dbsnmp.mgmt_response.calc_metric(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      -.1

      

      PL/SQL procedure successfully completed.

CAPTURE_STATS

Undocumented mgmt_response.capture_stats;

exec dbsnmp.mgmt_response.capture_stats;

      in capture_stats

      

      PL/SQL procedure successfully completed.

CREATE_BASELINE

Undocumented mgmt_response.create_baseline( snap_id_1 IN NUMBER, n_stmts IN NUMBER);

SELECT COUNT(*) FROM dba_sql_plan_baselines;

      

       COUNT(*)

      ---------

              0

      

      SELECT MAX(snap_id)

      FROM dba_hist_snapshot;

      

      MAX(SNAP_ID)

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

              3509

      

      exec dbsnmp.mgmt_response.create_baseline(3509, 2);

      BEGIN dbsnmp.mgmt_response.create_baseline(3509, 2); END;

      *

      ERROR at line 1:

      ORA-01403: no data found

      ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 199

      ORA-06512: at line 1

      

      SELECT COUNT(*) FROM dba_sql_plan_baselines;

      

       COUNT(*)

      ---------

              0

DELETE_BASELINE

Undocumented mgmt_response.delete_baseline;

exec dbsnmp.mgmt_response.delete_baseline;

      

      PL/SQL procedure successfully completed.

DELETE_SQL_FROM_BASELINE

Undocumented mgmt_response.delete_sql_from_baseline(hash IN NUMBER);

TBD

GETDURATION

Returns the number of seconds between the start and end dates mgmt_response.getDuration( start_date IN TIMESTAMP, end_date IN TIMESTAMP) RETURN NUMBER;

SELECT dbsnmp.mgmt_response.getDuration(SYSDATE-1, SYSDATE)

      FROM dual;

      

      
      DBSNMP.MGMT_RESPONSE.GETDURATION(SYSDATE-1,SYSDATE)

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

                                                    86400

GETVERSION

Undocumented mgmt_response.getVersion RETURN VARCHAR2;

SELECT dbsnmp.mgmt_response.getVersion

      FROM dual;

      

      GETVERSION

      ----------

      11.1.0.0.0

GET_LATEST_CURS

Undocumented mgmt_response.get_latest_curs(metric_curs OUT REF CURSOR);

TBD

GET_METRIC_CURS

Undocumented mgmt_response.get_metric_curs(metric_curs OUT REF CURSOR);

TBD

GET_TIME_STATS

Undocumented mgmt_response.get_time_stats( db_time OUT NUMBER, cpu_time OUT NUMBER, application_wait_time OUT NUMBER, cluster_wait_time OUT NUMBER, user_io_wait_time OUT NUMBER, concurrency_wait_time OUT NUMBER);

DECLARE

       dbt  NUMBER;

       cput NUMBER;

       awt  NUMBER;

       clwt NUMBER;

       uiwt NUMBER;

       cowt NUMBER;
      

      BEGIN

        dbsnmp.mgmt_response.get_time_stats(dbt, cput, awt, clwt, uiwt, cowt);

        dbms_output.put_line(dbt);

        dbms_output.put_line(cput);

        dbms_output.put_line(awt);

        dbms_output.put_line(clwt);

        dbms_output.put_line(uiwt);

        dbms_output.put_line(cowt);

      END;

      /

      88895027

      9327221

      6952

      0

      92725

      10811

      

      PL/SQL procedure successfully completed.

PURGE_OLD_HISTORY

Undocumented mgmt_response.purge_old_history;

exec dbsnmp.mgmt_response.purge_old_history;

      in purge_old_history

      row_count = 0

      cap_id = -1

      

      PL/SQL procedure successfully completed.

PURGE_OLD_STATS

Undocumented mgmt_response.purge_old_stats;

exec dbsnmp.mgmt_response.purge_old_stats;

      

      PL/SQL procedure successfully completed.

RESET_CAPTURE_STATS

Undocumented mgmt_response.reset_capture_stats;

exec dbsnmp.mgmt_response.capture_stats;

      in capture_stats

      

      PL/SQL procedure successfully completed.

      

      exec dbsnmp.mgmt_response.reset_capture_stats;

      

      PL/SQL procedure successfully completed.

RESET_SNAPSHOT_STATS

Undocumented mgmt_response.reset_snapshot_stats(snap_id_1 IN NUMBER);

exec dbsnmp.mgmt_response.reset_snapshot_stats(1);

      

      PL/SQL procedure successfully completed.

SAVE_TO_HISTORY

Undocumented mgmt_response.save_to_history;

exec dbsnmp.mgmt_response.save_to_history;

      in save_to_history

      BEGIN dbsnmp.mgmt_response.save_to_history; END;

      *

      ERROR at line 1:

      ORA-01403: no data found

      ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 767

      ORA-06512: at line 1

TAKE_V$SQL_SNAPSHOT

Undocumented mgmt_response.take_v$sql_snapshot(min_execs IN NUMBER);

exec dbsnmp.mgmt_response.take_v$sql_snapshot(2);

      in take_v$sql_snapshot

      

      PL/SQL procedure successfully completed.

      

      exec dbsnmp.mgmt_response.take_v$sql_snapshot(4);