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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_AQ
DBMS_AQADM
DBMS_AQADM_INV
DBMS_AQADM_VAR
DBMS_AQELM
DBMS_AQIN
DBMS_AQ_BQVIEW
DBMS_AQ_EXP_CMT_TIME_TABLES
DBMS_AQ_EXP_DEQUEUELOG_TABLES
DBMS_AQ_EXP_HISTORY_TABLES
DBMS_AQ_EXP_INDEX_TABLES
DBMS_AQ_EXP_QUEUE_TABLES
DBMS_AQ_EXP_TIMEMGR_TABLES
DBMS_AQ_EXP_ZECURITY
DBMS_AQ_IMPORT_INTERNAL
DBMS_AQ_IMP_ZECURITY
DBMS_AQ_SYS_EXP_ACTIONS
DBMS_AQ_SUB
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx