Oracle | DBMS_AQ_IND

Oracle DBMS_AQ_IND_MON 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 Advanced Queuing Index Monitoring

AUTHID DEFINER

Data Types sys.aq$_index_monitor

Dependencies

AQ$_BND_ARRAY	DBA_QUEUE_TABLES	DBMS_SPACE
AQ$_INDEX_MONITOR	DBMS_AQADM	GV$INSTANCE
DBA_INDEXES	DBMS_AQADM_SYS	GV$SQL
DBA_OBJECTS	DBMS_ASSERT	WRH$_SESS_TIME_STATS
DBA_QUEUES	DBMS_OUTPUT

Documented No

First Available 20c

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

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

Subprograms

CHECK_AQ_INDEXES	GET_INDEX_SIZE	MONITOR_TABLE_ENTRY
CHECK_ONE_AQ_INDEX	GET_INDEX_SIZE_USED	POPULATE_AQMONITOR_TABLE
COALESCE_ACTION	GET_LOGICAL_READS	PRINT_AQMONITOR_TABLE
CREATE_AQMONITOR_TABLE	GET_OUTPUT_LOG	SET_COALESCE
DELETE_AQMONITOR_TABLE	GET_QMON_CPU	SET_COALESCE_LEVEL
GET_CURR_INDEX_STATS	GET_SEG_STATS	SET_OUTPUT_LOG
GET_DEQUEUE_EXECUTIONS	GET_SUB_ESTIMATE	SET_SUB_ESTIMATE
GET_ENQUEUE_EXECUTIONS	INITIALIZE_INDEX_STATS	UNSET_COALESCE

CHECK_AQ_INDEXES (new 20c)

Monitor AQ Indexes dbms_aq_ind_mon.check_aq_indexes( schema IN VARCHAR2, qname IN VARCHAR2, force_coalesce IN BOOLEAN, create_monitor_record IN BOOLEAN);

TBD

CHECK_ONE_AQ_index (new 20c)

Monitor a single AQ index dbms_aq_ind_mon.check_one_aq_index(i IN sys.aq$_index_monitor) RETURN BOOLEAN;

TBD

COALESCE_ACTION (new 20c)

Undocumented dbms_aq_ind_mon.coalesce_action(i IN sys.aq$_index_monitor);

TBD

CREATE_AQMONITOR_TABLE (new 20c)

When implemented, will create an AQ monitor table

Still "Not implemented" in 21.1 dbms_aq_ind_mon.create_aqmonitor_table;

exec dbms_aq_ind_mon.create_aqmonitor_table;

      ERROR: Not implemented

      

      PL/SQL procedure successfully completed.

DELETE_AQMONITOR_TABLE (new 20c)

Drops the AQ monitoring table dbms_aq_ind_mon.delete_aqmonitor_table( schema IN VARCHAR2, qname IN VARCHAR2);

CREATE OR REPLACE TYPE message_t AS OBJECT (

      id     NUMBER,

      source VARCHAR2(4000));

      /

      

      BEGIN

        dbms_aqadm.create_queue_table('testq_table', 
      'message_t');

        dbms_aqadm.create_queue('testq', 'testq_table');

        dbms_aq_ind_mon.delete_aqmonitor_table(USER, 'TESTQ');

      END;

      /

      

      PL/SQL procedure successfully completed.

GET_CURR_INDEX_STATS (new 20c)

Undocumented dbms_aq_ind_mon.get_curr_index_stats( owner IN VARCHAR2, name IN VARCHAR2, curr_index_size OUT NUMBER, curr_index_size_used OUT NUMBER);

DECLARE

       cis  NUMBER;

       cisu NUMBER;

      BEGIN

        dbms_aq_ind_mon.get_curr_index_stats(USER, 'TESTQ', cis, cisu);

        dbms_output.put_line(TO_CHAR(cis));

        dbms_output.put_line(TO_CHAR(cisu));

      END;

      /

      get_curr_index_stats: error ORA-00942: table or view does not exist

      0

      0

GET_DEQUEUE_EXECUTIONS (new 20c)

Returns the number of dequeues executed dbms_aq_ind_mon.get_dequeue_executions(QTABLE_NAME IN VARCHAR2) RETURN NUMBER;

SELECT dbms_aq_ind_mon.get_dequeue_executions('TESTQ_TABLE')

      FROM dual;

      

      DBMS_AQ_IND_MON.GET_DEQUEUE_EXECUTIONS('TESTQ_TABLE')

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

                                                          0

GET_ENQUEUE_EXECUTIONS (new 20c)

Returns the number of enqueue exectuions dbms_aq_ind_mon.get_enqueue_executions(QTABLE_NAME IN VARCHAR2) RETURN NUMBER;

SELECT dbms_aq_ind_mon.get_enqueue_executions('TESETQ)TABLE')

      FROM dual;

      

      
      DBMS_AQ_IND_MON.GET_ENQUEUE_EXECUTIONS('TESETQ)TABLE')

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

                                                           0

GET_INDEX_SIZE (new 20c)

Returns the size of an index in bytes dbms_aq_ind_mon.get_index_size(index_object_id IN BINARY_INTEGER) RETURN NUMBER;

SELECT dbms_aq_ind_mon.get_index_size(82510)

      FROM dual;

      

      DBMS_AQ_IND_MON.GET_INDEX_SIZE(82510)

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

                                      65536

GET_INDEX_SIZE_USED (new 20c)

Returns the size of an index in bytes that have been used dbms_aq_ind_mon.get_index_size_used(index_object_id IN BINARY_INTEGER) RETURN NUMBER;

SELECT dbms_aq_ind_mon.get_index_size_used(82510)

      FROM dual;

      

      DBMS_AQ_IND_MON.GET_INDEX_SIZE_USED(82510)

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

                                            8086

GET_LOGICAL_READS (new 20c)

Returns the number of logical reads from queue table monitoring dbms_aq_ind_mon.get_logical_reads(index_OBJECT_ID IN BINARY_INTEGER) RETURN NUMBER;

SELECT dbms_aq_ind_mon.get_logical_reads(82510)

      FROM dual;

      

      DBMS_AQ_IND_MON.GET_LOGICAL_READS(82510)

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

                                             0

GET_OUTPUT_LOG (new 20c)

Returns the name of the index monitoring log dbms_aq_ind_mon.get_output_log RETURN VARCHAR2;

SELECT dbms_aq_ind_mon.get_output_log

      FROM dual;

      

      GET_OUTPUT_LOG

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

      aqindexmon_report.log

GET_QMON_CPU (new 20c)

Returns the amount of QMON process CPU dbms_aq_ind_mon.get_qmon_cpu RETURN BINARY_INTEGER;

SELECT dbms_aq_ind_mon.get_qmon_cpu

      FROM dual;

      

      GET_QMON_CPU

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

                 0

GET_SEG_STATS (new 20c)

Undocumented dbms_aq_ind_mon.get_seg_stats( I IN PL/SQL RECORD SYS AQ$_index_MONITOR, total_FULL_index_SIZE OUT NUMBER, total_partial_index_SIZE OUT NUMBER, total_index_BLOCKS OUT NUMBER, total_partial_index_BLOCKS OUT NUMBER);

TBD

GET_SUB_ESTIMATE (new 20c)

Returns the Sub Estimate which is not documented

The default value is 2 dbms_aq_ind_mon.get_sub_estimate RETURN BINARY_INTEGER;

See SET_SUB_ESTIMATE demo below

INITIALIZE_INDEX_STATS (new 20c)

Resets index stats of the Queue table to zero (0) dbms_aq_ind_mon.initialize_index_stats( schema IN VARCHAR2, qtname IN VARCHAR2, index_ob_id IN NUMBER, base_num_dequeues OUT NUMBER, base_logical reads OUT NUMBER, base_num_enqueues OUT NUMBER);

DECLARE

       bnd NUMBER;

       blr NUMBER;

       bne NUMBER;

      BEGIN

        dbms_aq_ind_mon.initialize_index_stats(USER, 'TESTQ', 82510, bnd, blr, bne);

        dbms_output.put_line(TO_CHAR(bnd));

        dbms_output.put_line(TO_CHAR(blr));

        dbms_output.put_line(TO_CHAR(bne));

      END;

      /

      0

      0

      0

MONITOR_TABLE_ENTRY (new 20c)

Appears to count the number of entries in the monitoring table dbms_aq_ind_mon.monitor_table_entry( schema IN VARCHAR2, qname IN VARCHAR2, RETURN BINARY_INTEGER;

SELECT dbms_aq_ind_mon.monitor_table_entry(USER, 'QTEST')

      FROM dual;

      

      DBMS_AQ_IND_MON.MONITOR_TABLE_ENTRY(USER,'QTEST')

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

                                                      0

POPULATE_AQMONITOR_TABLE (new 20c)

Undocumented dbms_aq_ind_mon.populate_aqmonitor_table( schema IN VARCHAR2, qname IN VARCHAR2);

exec dbms_aq_ind_mon.populate_aqmonitor_table(USER, 
      'QTEST');

      

      PL/SQL procedure successfully completed.

PRINT_AQMONITOR_TABLE (new 20c)

Undocumented dbms_aq_ind_mon.print_aqmonitor_table( schema IN VARCHAR2, qname IN VARCHAR2);

exec dbms_aq_ind_mon.print_aqmonitor_table(USER, 'QTEST');

      

      PL/SQL procedure successfully completed.

SET_COALESCE (new 20c)

Undocumented dbms_aq_ind_mon.set_coalesce;

exec dbms_aq_ind_mon.set_coalesce;

      

      PL/SQL procedure successfully completed.

SET_COALESCE_LEVEL (new 20c)

Undocumented dbms_aq_ind_mon.set_coalesce_level(lvl IN BINARY_INTEGER);

exec dbms_aq_ind_mon.set_coalesce_level(2);

      

      PL/SQL procedure successfully completed.

SET_OUTPUT_LOG (new 20c)

Undocumented dbms_aq_ind_mon.set_output_log(log_file IN VARCHAR2);

TBD

SET_SUB_ESTIMATE (new 20c)

Undocumented dbms_aq_ind_mon.set_sub_estimate(sub_count IN BINARY_INTEGER);

SELECT dbms_aq_ind_mon.get_sub_estimate

      FROM dual;

      

      GET_SUB_ESTIMATE

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

                     2

      

      exec dbms_aq_ind_mon.set_sub_estimate(5);

      

      PL/SQL procedure successfully completed.

      

      SELECT dbms_aq_ind_mon.get_sub_estimate

      FROM dual;

      

      GET_SUB_ESTIMATE

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

                     5

UNSET_COALESCE (new 20c)

Undocumented dbms_aq_ind_mon.unset_coalesce;

exec dbms_aq_ind_mon.unset_coalesce;

      

      PL/SQL procedure successfully completed.

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated:

DBSecWorx