Oracle DBMS_AQ_IND

Oracle DBMS_AQ_IND_MON Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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