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 (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 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx