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