Oracle | DBMS_AQ_IND

Oracle DBMS_AQ_IND_MON Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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_QMON_CPU	SET_COALESCE
DELETE_AQMONITOR_TABLE	GET_SEG_STATS	SET_COALESCE_LEVEL
GET_CURR_INDEX_STATS	GET_SUB_ESTIMATE	SET_SUB_ESTIMATE
GET_DEQUEUE_EXECUTIONS	INITIALIZE_INDEX_STATS	UNSET_COALESCE
GET_ENQUEUE_EXECUTIONS

CHECK_AQ_INDEXES

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

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

TBD

COALESCE_ACTION

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

TBD

CREATE_AQMONITOR_TABLE

When implemented, will create an AQ monitor table

Not an exception. Still "Not implemented" in 23.8 and no table is created. 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

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

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

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');

      

      DBMS_AQ_IND_MON.GET_DEQUEUE_EXECUTIONS('TESTQ_TABLE')

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

                                                          0

GET_ENQUEUE_EXECUTIONS

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

SELECT dbms_aq_ind_mon.get_enqueue_executions('TESETQ)TABLE');

      

      
      DBMS_AQ_IND_MON.GET_ENQUEUE_EXECUTIONS('TESETQ)TABLE')

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

                                                           0

GET_INDEX_SIZE

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);

      

      DBMS_AQ_IND_MON.GET_INDEX_SIZE(82510)

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

                                      65536

GET_INDEX_SIZE_USED

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);

      

      DBMS_AQ_IND_MON.GET_INDEX_SIZE_USED(82510)

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

                                            8086

GET_LOGICAL_READS

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);

      

      DBMS_AQ_IND_MON.GET_LOGICAL_READS(82510)

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

                                             0

GET_QMON_CPU

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;

      

      GET_QMON_CPU

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

                 0

GET_SEG_STATS

Undocumented dbms_aq_ind_mon.get_seg_stats( i IN 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

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

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

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');

      

      DBMS_AQ_IND_MON.MONITOR_TABLE_ENTRY(USER,'QTEST')

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

                                                      0

POPULATE_AQMONITOR_TABLE

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

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

Undocumented dbms_aq_ind_mon.set_coalesce;

exec dbms_aq_ind_mon.set_coalesce;

      

      PL/SQL procedure successfully completed.

SET_COALESCE_LEVEL

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_SUB_ESTIMATE

Undocumented dbms_aq_ind_mon.set_sub_estimate(sub_count IN BINARY_INTEGER);

SELECT dbms_aq_ind_mon.get_sub_estimate;

      

      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;

      

      GET_SUB_ESTIMATE

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

                     5

UNSET_COALESCE

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 Daniel Morgan. Last Updated: