Abdul hafeez kalsekar -- OCS/OCE/OCP: March 2023

Seeing issues related to queuing below article is documented to have some handy steps for troubleshooting queuing issues

AQ Components

The four main components of AQ are:

1. Message - A message consists of message content, or payload, which can be specified using typed or raw data and message attributes or control information.

2. Message Queue – Messages are stored in queues and these queues act as “postal boxes” where different applications can look for “mail” in the form of messages. Thus, when one application wants to contact certain applications for certain tasks, it can leave messages in these queues, and the receiving applications will be able to find these messages for processing.

3. Message Interfaces – AQ supports enqueue, dequeue, and propagation operations that integrate seamlessly with existing applications by supporting popular standards. AQ messages can be created, queried, propagated and consumed using popular application programming interfaces (API) such as PL/SQL, C/C++, Java, Visual Basic (through Oracle Objects for OLE), Python, Node.js, and ODP.NET. AQ provides support for the Java Message Service 1.1 (JMS) API that allows Java applications to utilize the message queuing functionality.

4. Message Handling – AQ supports rule-based routing of messages according to data in the message payload or attributes. Additionally, message transformations can be applied to messages to re-format data before the messages are automatically delivered to target applications or subscribers. Oracle Database 19c can also exchange AQ messages with IBM MQ and TIBCO/Rendezvous through the Oracle Messaging Gateway.

Terms To Understand :

1) Queue

2) Queue tables

3) Propagations

4) Subscribers

Understand queue types :

1) buffered queue

2) Sharded queue / Non-Sharded Queues

3) Persistent queue

4) single consumer queue and multiple consumers

Parameter Influencing Advanced queuing :

Main database parameter is aq_tm_processes . However below are other parameters that influence advanced queuing

aq_tm_processes

processes

job_queue_processe

timed_statistics

SGA_TARGET

streams_pool_size

undo_retention

_client_enable_auto_unregister

shared_pool_size

Objects Created with Advanced Queues ( What Objects Are Created When Creating a Queue Table ? (Doc ID 224027.1) )

<QUEUE_TABLE_NAME>, TABLE. Is the queue table itself and will contain 1 row per message in the queue table.

AQ$_<QUEUE_TABLE_NAME>_E, QUEUE. It is default exception queue for any queue defined on the queue table.

AQ$<QUEUE_TABLE_NAME>, VIEW. The definition of the view depends if the queue table is a single consumer or a multiple consumer queue table. It will contain one row per message and subscriber/recipient.

AQ$_<QUEUE_TABLE_NAME>_I, INDEX when the queue table is a single consumer queue table and as Index-Organized Table (IOT) when it is a multi-consumer table. It is used to control dequeuing operations, in the case of multiconsumer queue tables will contain 1 row per message not consumer per subscriber/recipient.

AQ$_<QUEUE_TABLE_NAME>_T, INDEX when the queue table is a single consumer queue table and as an IOT when it is a multi-consumer table. It is used for Time based operations and managed by QMON process and will contain 1 row per message requiring work.

AQ$_<QUEUE_TABLE_NAME>_F, VIEW . Created to manage conditional dequeues. This view is only created on releases 10G and upwards.

Objects created only for multiple consumers queue tables

AQ$_<QUEUE_TABLE_NAME>_S, TABLE. Keep information about the subscribers.

AQ$_<QUEUE_TABLE_NAME>_H, IOT. Keep historical data and contains one row per message per consumer.

AQ$_<QUEUE_TABLE_NAME>_N, SEQUENCE, used to assign subscriber ids.

AQ$<QUEUE_TABLE_NAME>_S, VIEW. Based on AQ$_<QUEUE_TABLE_NAME>_S, it references subscribers and transformations.

AQ$_<QUEUE_TABLE_NAME>_V, EVALUATION CONTEXT, required to evaluate rules assigned to subscribed properly. Only available since 9.2.

AQ$_<QUEUE_TABLE_NAME>_G, IOT. Called AQ$_<queue_table_name>_NR on releases 9i and not existing on releases 8.1. It is the signature IOT and do not have implementation currently.

AQ$_<QUEUE_TABLE_NAME>_C, IOT. Used with transactionally grouped Multi-consumer Queues with a commit time order

AQ$_<QUEUE_TABLE_NAME>_L, table. Created on release 11.2 onwards to keep track of dequeue operations.

Migrate Old Queue Tables

You can migrate old queues in your Oracle Database. Any queues that don’t have compatible set to 10.0, you can migrate to the newest compatible setting:

SQL> select queue_table, compatible from user_queue_tables;

begin

dbms_aqadm.migrate_queue_table(..., compatible => '10.0.0');

end;

Sharded Queues

A sharded queue increases enqueue-dequeue throughput, especially across Oracle RAC instances, because messages from different enqueue sessions

are allowed to be dequeued in parallel. Each shard of the queue is ordered based on enqueue time within a session and ordering across shards is

best-effort. Sharded queues automatically manage table partitions so that enqueuers and dequeuers do not contend among themselves.

In addition, sharded queues use an in-memory message cache to optimize performance and reduce the disk and CPU overhead of enqueues and dequeues.

Buffered Queues :

One of the benefit of using buffered messages is that they remain in memory and are not stored on disks.

A direct consequence of that is that buffered messages are not logged and as we've seen previously can be lost.

The devil being in the details, the memory allocated for the Streams Pool, that store the messages, is not unlimited and compete with other memory pools.

To prevent that memory to increase without limit Oracle offers several mechanisms; One is Publisher Flow Control that prevents messages from being enqueued

when the messages are not dequeued fast enough. Another one is the ability for messages to spill on disks in the queue table

--> Sample of creating buffered queue

create or replace procedure demo_enqueue(p_hexamsg varchar2) is

enqueue_options DBMS_AQ.enqueue_options_t;

message_properties DBMS_AQ.message_properties_t;

recipients DBMS_AQ.aq$_recipient_list_t;

message_handle RAW(16);

message RAW(10);

begin

message := hextoraw(p_hexamsg);

recipients(1) := sys.aq$_agent('SUBSCRIBER1', 'MYQUEUE', NULL);

recipients(2) := sys.aq$_agent('SUBSCRIBER2', 'MYQUEUE', NULL);

message_properties.recipient_list := recipients;

enqueue_options.visibility := dbms_aq.immediate;

enqueue_options.delivery_mode := dbms_aq.buffered;

dbms_aq.enqueue(

queue_name => 'MYQUEUE',

enqueue_options => enqueue_options,

message_properties => message_properties,

payload => message,

msgid => message_handle);

commit;

end;

--> Check buffered queques

alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

col queue_name format a20

col startup_time format a18

col num_msgs format 999,999

col spill_msgs format 999,999

set lines 100

select queue_name

, startup_time

, sysdate

, num_msgs

, spill_msgs

from v$buffered_queues

;

set lines 100

col queue_name format a20

col subscriber_name format a15

col subscriber_type format a15

col startup_time format a18

col total_dequeued_msg format 999,999

select queue_name

, subscriber_name

, subscriber_type

, startup_time

, total_dequeued_msg

from v$buffered_subscribers

;

Creating Indexes on a Queue Table

Creating an index on a non-sharded queue table is useful if you meet these conditions.

Dequeue using correlation ID

An index created on the column corr_id of the underlying queue table AQ$_QueueTableName expedites dequeues.

Dequeue using a condition

This is like adding the condition to the where-clause for the SELECT on the underlying queue table.

An index on QueueTableName expedites performance on this SELECT statement.

Troubleshooting AQ :

1) Restart queue process and queue monitoring

aq_tm_processes --> make it 0

kill existing q0 process .

ps -ef|grep qmn

ps -ef|grep q00

oracle aq_tm_processes --> make it 1

ps -ef | grep -i e00 | grep -i dbname -- kill it

-- Perform the below to reset the qmon process:

alter system set "_aq_stop_backgrounds"=TRUE;

alter system set "_aq_stop_backgrounds"=FALSE;

2) Restart job queue processes

3) Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.

4) Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.

5) Turn on propagation tracing at the highest level using event 24040, level 10

6) . Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

7) Generate TFA as per 1905796.1

$TFA_HOME/bin/tfactl diagcollect -srdc dbaqmon

8) Generate AQ & MGW Health Check using Configuration Script ( Doc ID 1193854.1 )

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/aq-mgw-health-check-and-configuration.html

Also generate Hanganalyze and system state .

9) Check *qm0*.trc in trace directory .

10) Restart Time Managers from root container

EXEC dbms_aqadm.stop_time_manager;

EXEC dbms_lock.sleep(120); -- sleep is must

EXEC dbms_aqadm.start_time_manager;

11) Restart queue process

Begin

dbms_aqadm.stop_queue ('<schema name>.<queue name>'); ;

END ;

Begin

dbms_aqadm.start_queue ( queue_name =>'QUEUENAME' ) ;

END ;

Tracing event 10852 for Aq as per (Doc ID 1451920.1) and Note 1365655.1 when Dequeue by msgid of a message in WAIT

connect / as sysdba

oradebug setospid 30998710

oradebug unlimit

oradebug tracefile_name

oradebug event 10046 trace name context forever, level 12

oradebug event 10852 trace name context forever, level 9

oradebug event trace[AQ_DEQ] disk high

oradebug dump errorstack 3

--wait 1 minute

oradebug dump errorstack 3

--wait 1 minute

oradebug dump errorstack 3

oradebug event 10046 trace name context off

oradebug event 10852 trace name context off

oradebug event trace[AQ_DEQ] off

conn / as sysdba

alter system set event='10852 trace name context forever, level 16384' scope=spfile;

alter system set event='10852 trace name context forever, level 32' scope=spfile;

How to Enable/Diasble queue

col desitnation for a25

select QNAME,DESTINATION,SCHEDULE_DISABLED from dba_queue_Schedules where destination='DB_link';

exec dbms_aqadm.DISABLE_PROPAGATION_SCHEDULE(QUEUE_NAME=>'&Enter_SchemaName_QueueName',DESTINATION=>'&Enter_Destination');

exec dbms_aqadm.ENABLE_PROPAGATION_SCHEDULE(QUEUE_NAME=>'&Enter_SchemaName_QueueName',DESTINATION=>'&Enter_Destination');

exec dbms_aqadm.unschedule_propagation(QUEUE_NAME=>'&Enter_SchemaName_QueueName',DESTINATION=>'&Enter_Destination');

exec dbms_aqadm.schedule_propagation(QUEUE_NAME=>'&Enter_SchemaName_QueueName',DESTINATION=>'&Enter_Destination');

Views :

SELECT

a.owner,

a.name,

a.queue_type,

a.queue_table,

a.retention,

a.enqueue_enabled,

a.dequeue_enabled,

b.waiting,

b.ready,

b.expired,

b.total_wait,

b.average_wait

FROM

dba_queues a,

v$aq b

WHERE a.qid = b.qid

AND a.name = 'EXAMPLE_QUEUE'

set linesize 600

SELECT j.inst_id ||','|| j.con_id ||','|| j.queue_table_id ||','||

t.schema||','|| t.name ||','|| j.status ||','||SYS_EXTRACT_UTC(SYSTIMESTAMP)||','||

j.next_service_time ||','|| j.window_end_time ||','||

j.total_runs ||','|| j.tmgr_rows_processed ||','||

j.last_tmgr_processing_time ||','|| j.deqlog_rows_processed ||','||

j.last_deqlog_processing_time

FROM gv$persistent_qmn_cache j, system.aq$_queue_tables t

WHERE j.queue_table_id = t.objno

AND t.schema = 'AQUSER'

AND t.name = 'TESTQTABLE';

set linesize 600

SELECT j.inst_id ||','|| j.con_id ||','|| j.queue_table_id ||','||

t.schema||','|| t.name ||','|| j.status ||','||

j.next_service_time ||','|| j.window_end_time ||','||

j.total_runs ||','|| j.tmgr_rows_processed ||','||

j.last_tmgr_processing_time ||','|| j.deqlog_rows_processed ||','||

j.last_deqlog_processing_time

FROM gv$persistent_qmn_cache j, system.aq$_queue_tables t

WHERE j.queue_table_id = t.objno

AND t.schema = 'GTXNPI_ESHPOC'

AND t.name like 'TB_DSP_AP_QUEUE%'

order by t.name;

alter session set nls_date_format='Mon dd yyyy hh24:mi:ss';

set pages 9999 lines 192

select sysdate from dual;

select * from global_name;

select queue, msg_state, count(*), min(enq_time), max(enq_time) from <OWNERq$<QUEUE_TABLE_NAME> group by queue, msg_state;

select queue,msg_state,count(*) from A$$JMS_QUEUE_TABLE group by queue,msg_state;

SELECT owner, name, retention FROM all_queues ;

SELECT msg_state, MIN(enq_time) min_enq_time, MAX(deq_time) max_deq_time, count(*) FROM abdul_queue GROUP BY msg_state;

select TOTAL_NUMBER

from DBA_QUEUE_SCHEDULES

where QNAME=’<source_queue_name>’;

select* from QUEUE_PRIVILEGES ;

SELECT name, queue_type, waiting, ready, expired

FROM dba_queues q

JOIN v$aq v ON q.qid = v.qid

WHERE q.name = 'queue_name' ;

select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.QUEUE_TABLE group by q_name, state;

select MSG_STATE ,CONSUMER_NAME QUEUE,MSG_ID,DELAY from OWNER.AQ$queue_table;

set line 100

col Parameter for a50

col "Session Value" for a20

col "Instance Value" for a20

SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm in ( '_client_enable_auto_unregister', '_emon_send_timeout' )

-- to check if dequeue is happening

select queue_id,shard_id,priority,ENQUEUED_MSGS,dequeued_msgs, enqueued_msgs-dequeued_msgs backlog from gv$aq_sharded_subscriber_stat order by queue_id, shard_id, priority;

Check Message State and Destination. Find the queue table for a given queue

select QUEUE_TABLE

from DBA_QUEUES

where NAME = &queue_name;

Check for messages in the source queue with

select count (*)

from AQ$<source_queue_table>

where q_name = 'source_queue_name';

Check for messages in the destination queue.

select count (*)

from AQ$<destination_queue_table>

where q_name = 'destination_queue_name';

select * from GV$AQ ;

select * from aq.aq$objmsgs80_qtab ;

select * from dba_queues where qid=14140;

SELECT * FROM SYS.ALL_QUEUES ;

select * from DBA_QUEUE_SCHEDULES ;

COLUMN OWNER HEADING 'Owner' FORMAT A10

COLUMN NAME HEADING 'Queue Name' FORMAT A28

COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22

COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT

FROM DBA_QUEUES q, DBA_QUEUE_TABLES t

WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND

q.QUEUE_TABLE = t.QUEUE_TABLE AND

q.OWNER = t.OWNER;

DBA_QUEUE_TABLES: All Queue Tables in Database

The DBA_QUEUE_TABLES view contains information about the owner instance for a queue table. A queue table can contain multiple queues. In this case, each queue in a queue table has the same owner instance as the queue table. Its columns are the same as those in ALL_QUEUE_TABLES.

DBA_QUEUES: All Queues in Database The DBA_QUEUES view specifies operational characteristics for every queue in a database. Its columns are the same as those ALL_QUEUES.

DBA_QUEUE_SCHEDULES: All Propagation Schedules The DBA_QUEUE_SCHEDULES view describes all the current schedules in the database for propagating messages.

QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege The QUEUE_PRIVILEGES view describes queues for which the user is the grantor, grantee, or owner. It also shows queues for which an enabled role on the queue is granted to PUBLIC.

AQ$Queue_Table_Name: Messages in Queue Table

The AQ$Queue_Table_Name view describes the queue table in which message data is stored. This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.

***********************

1) Check queue details:

SELECT queue_table, owner, name, enqueue_enabled, dequeue_enabled FROM all_queues;

2) Check current queue size:

SELECT COUNT(*) as message_count FROM queue_table_name;

3) Check if there are any error messages in the queue:

SELECT COUNT(*) as error_count FROM queue_table_name WHERE MSG_STATE = 'ERROR';

4) Check queue status:

SELECT queue_table, owner, name, enqueue_enabled, dequeue_enabled FROM all_queues WHERE queue_table = 'your_queue_table_name';

SELECT q.Queue_Table, q.State, q.Total_Queue_Size FROM dba_queues q;

SELECT q.queue_table, q.queue_type, q.max_retries, q.retry_delay, q.user_comment, t.name, t.queue_to_queue_time

FROM all_queues q, all_queue_tables t

WHERE q.name = t.queue

AND q.queue_table = '<your_queue_table>';

5) Check for any backlogs in the queue:

SELECT COUNT(*) as backlog_count FROM queue_table_name WHERE MSG_STATE = 'READY';

SELECT COUNT(*) FROM queue_table WHERE queue_name = 'your_queue_name';

6) Check the oldest message in a queue:

SELECT min(DEQUEUE_MSGID), min(enq_time) FROM queue_table WHERE queue_name = 'your_queue_name';

7) Check the queue table for any messages stuck in the queue:

SELECT * FROM <queue_table> WHERE ENQ_TIME IS NOT NULL AND DEQ_TIME IS NULL;

8) Check if there are any lock . Check if any errors in alert log

9) Check if queues are buffered (in-memory) or persistent (on disk in a queue_table).

SQL> select * from v$buffered_queues;

10) . Check if queue_to_queue or queue_to_dblink is used.

col destination for a15

col session_id for a15

set line 200

select qname,destination,session_id,process_name,schedule_disabled,instance, current_start_time

from dba_queue_schedules order by current_start_time desc,schedule_disabled desc ;

11) Check if queues are propagating data at all or are slow?

select TOTAL_NUMBER from DBA_QUEUE_SCHEDULES where QNAME='&queue_name';

12) . Check queue errors and also find out associated Queue table

set linesize 140;

column destination format a25;

column last_error_msg format a35;

column schema format a15

select schema,

qname,

destination,

failures,

last_error_date,

last_error_time,

last_error_msg

from dba_queue_schedules

where failures != 0;

select QUEUE_TABLE from DBA_QUEUES where NAME ='&queue_name';

13) Check what queue is supposed to do

column qname format a40

column user_comment format a40

column last_error_msg format a40

column destination format a25

select distinct a.schema || '.' || a.qname qname

,a.destination

,a.schedule_disabled

,b.user_comment

from dba_queue_schedules a, dba_queues b

where a.qname=b.name;

14) Check if Queues are disabled.

select schema || '.' || qname,

destination,

schedule_disabled,

last_error_msg

from dba_queue_schedules

where schedule_disabled='Y';

15) If queue is DISABLED...enable it using following

select 'exec dbms_aqadm.enable_propagation_schedule(''' || schema || '.' || qname || ''', ''' || destination || ''');'

from dba_queue_schedules

where schedule_disabled ='Y';

16) Check performance of each queue.

col last_run_date for a40

col qname for a25

col NEXT_RUN_DATE for a40

col seconds for 9999

set line 200

select qname,

last_run_date,

NEXT_RUN_DATE,

total_number MESSAGES,

total_bytes/1024 KBYTES,

total_time SECONDS,

round(total_bytes/(total_time+0.0000001)) BYTES_PER_SEC, process_name

from dba_queue_schedules

order by BYTES_PER_SEC;

17 ) Check if propagation has been set correctly

Check that the propagation schedule has been created and that a job queue process has been assigned. Look for the entry in DBA_QUEUE_SCHEDULES and SYS.AQ$_SCHEDULES for your schedule. For 10g and below, check that it has a JOBNO entry in SYS.AQ$_SCHEDULES, and that there is an entry in DBA_JOBS with that JOBNO. For 11g and above, check that the schedule has a JOB_NAME entry in SYS.AQ$_SCHEDULES, and that there is an entry in DBA_SCHEDULER_JOBS with that JOB_NAME. Check the destination is as intended and spelled correctly.

10.2 Check if a Process_Name has been assigned to a queue, if no process_name is assigned...schedule is not currently executing. You may need to execute this statement no. of times to verify if a process is being allocated.

10.3 if a process_name is assigned and schedule executing but failing...Refer to step 8 for errors.

10.4 Check if queue tables exists in sys

SQL> select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED

from DBA_QUEUES where owner='SYS'

and QUEUE_TABLE like '%PROP_TABLE%';

If the %PROP_NOTIFY queue is not enabled for enqueue or dequeue, it should be so enabled using DBMS_AQADM.START_QUEUE. However, the exception queue AQ$_AQ$_PROP_TABLE_E should not be enabled for enqueue or dequeue.

10.5 Check that the remote queue the propagation is transferring messages to exists and is enabled for enqueue

If the AQ_PROP_NOTIFY queue is not enabled for enqueue or dequeue, it should be so enabled using DBMS_AQADM.START_QUEUE. However, the exception queue AQ$_AQ$_PROP_TABLE_E should not be enabled for enqueue or dequeue.

***********************

-- check wait time for queue in gv$aq

-- check ENQUEUE_ENABLED, DEQUEUE_ENABLED from DBA_QUEUES to see queue type

-- check primary and secomday instance for queue in dba_queue_tables

-- check state , enq_time , deq_time from owner.queue_table_namse

-- check message count, MSG_STATE from owner.AQ$Queue_Table_Name;

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE> DIAG - INFO </title> -

<STYLE TYPE='TEXT/CSS'></STYLE>"

SET ECHO ON

spool AQ_Diag.html

set pagesize 120

set linesize 180

column global_name format a20

column current_scn format 99999999999999999999

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

alter session set nls_timestamp_format='dd-mon-yyyy hh24:mi:ss';

select global_name, sysdate from global_name;

select * from gv$version;

select inst_id, instance_number, instance_name, host_name, startup_time, status from gv$instance order by inst_id;

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') "Current Container" FROM DUAL;

select con_id, name, cdb, current_scn from gv$database order by inst_id;

select * from gv$containers order by inst_id,con_id;

select * from gv$pdbs order by inst_id,con_id;

select QUEUE_TABLE from DBA_QUEUE_TABLES where QUEUE_TABLE like '%PROP_TABLE%' and OWNER = 'SYS';

select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED from DBA_QUEUES where owner='SYS' and QUEUE_TABLE like '%PROP_TABLE%';

select * from gv$aq where ready >100 ;

SELECT q.name, s.*

FROM dba_queues q, v$aq s

WHERE q.qid = s.qid

select msg_id, msg_state, delay,enq_timestamp, delay_timestamp from owner.AQ$Queue_Table_Name;

select MSG_ID,MSG_STATE,DELAY,ENQ_TIME

from USER01.AQ$queue_table

where MSG_STATE='WAIT'

order by ENQ_TIME;

select msg_state, retry_count, delay, count(*)

from aq$<queue_table_name>

group by msg_state, retry_count, delay;

select owner, queue_table, type, RECIPIENTS from dba_queue_tables where

queue_table='ABDUL_QUEUE';

SELECT queue_table, owner_instance, primary_instance, secondary_instance

FROM dba_queue_tables

WHERE owner = 'ABDUL' AND queue_table = 'ABDUL_QUEUE';

select count(*), state, q_name from ABDUL.ABDUL_QUEUE

group by q_name, state;

select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE

group by q_name, state;

select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE

where deq_time < (sysdate - 1)

group by q_name, state;

select trunc(enq_time,'HH'), q_name, state, count(*), max(deq_time) from ABDUL.ABDUL_QUEUE

group by trunc(enq_time,'HH'),q_name, state;

select * from dba_queue_subscribers where queue_table='ABDUL_QUEUE';

spool off

SET MARKUP HTML OFF

SET ECHO ON

***********************

Possible message states in Oracle Advanced Queuing

Message state mainly are below however detailed states are listed in table

READY – message is available to be dequeued

WAITING – availability for dequeuing is delayed

EXPIRED – message has timed out and been moved to exception queue

PROCESSED – message has been consumed by all consumers

Value	Name	Meaning
0	READY	The message is ready to be processed, i.e., either the delay time of the message has passed or the message did not have a delay time specified
1	WAITING or WAIT	The delay specified by message_properties_t.delay while executing dbms_aq.enqueue has not been reached.
2	RETAINED OR PROCESSED	The message has been successfully processed (dequeued) but will remain in the queue until the retention_time specified for the queue while executing dbms_aqadm.create_queue has been reached.
3	EXPIRED	The message was not successfully processed (dequeued) in either 1) the time specified by message_properties_t.expiration while executing dbms_aq.enqueue or 2) the maximum number of dequeue attempts (max_retries) specified for the queue while executing dbms_aqadm.create_queue.
4	IN MEMORY	User-enqueued buffered message
7	SPILLED	User-enqueued buffered message spilled to disk
8	DEFERRED	Buffered messages enqueued by a Streams Capture process
9	DEFERRED SPILLED	Capture-enqueued buffered messages that have been spilled to disk
10	BUFFERED EXPIRED	User-enqueued expired buffered messages

Performance Views

Oracle provides these views to monitor system performance and troubleshooting.

V$AQ_MESSAGE_CACHE_STAT: Memory Management for Sharded Queues

V$AQ_SHARDED_SUBSCRIBER_STAT: Sharded Queue Subscriber Statistics

V$AQ_MESSAGE_CACHE_ADVICE: Simulated Metrics

V$AQ_REMOTE_DEQUEUE_AFFINITY: Dequeue Affinity Instance List

V$PERSISTENT_QUEUES: All Active Persistent Queues in the Instance

V$PERSISTENT_SUBSCRIBERS: All Active Subscribers of the Persistent Queues in the Instance

V$PERSISTENT_PUBLISHERS: All Active Publishers of the Persistent Queues in the Instance

V$BUFFERED_QUEUES: All Buffered Queues in the Instance.

V$BUFFERED_SUBSCRIBERS: Subscribers for All Buffered Queues in the Instance

V$BUFFERED_PUBLISHERS: All Buffered Publishers in the Instance

V$PERSISTENT_QMN_CACHE: Performance Statistics on Background Tasks for Persistent Queues

V$AQ: Number of Messages in Different States in Database

V$AQ_BACKGROUND_COORDINATOR: Performance Statistics for AQ's Master Background Coordinator Process (AQPC)

V$AQ_JOB_COORDINATOR: Performance Statistics per Coordinator

V$AQ_NONDUR_REGISTRATIONS: Non-Durable Registrations

V$AQ_SERVER_POOL: Performance Statistics for all Servers

V$AQ_CROSS_INSTANCE_JOBS: Cross Process Jobs Description

V$AQ_NONDUR_REGISTRATIONS: Non-Durable Registrations

V$AQ_NOTIFICATION_CLIENTS: Secure OCI Client Connections

V$AQ_SUBSCRIBER_LOAD: Durable Subscribers

V$AQ_NONDUR_SUBSCRIBER: Non-Durable Subscribers

V$AQ_NONDUR_SUBSCRIBER_LWM: LWM of Non Durable Subscriber

V$AQ_MESSAGE_CACHE: Performance Statistics

Known Issues :

1) Issue 1

AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC after restart / patching :

Queue table "ABDUL_QUEUE" is AQ queue table owned by user "USER01" and view "AQ$ABDUL_QUEUE" is created when creating queue table "ABDUL_QUEUE"

and returns read only data from "ABDUL_QUEUE" with more meaningful/readable values for columns .

During restart queue process goes in hung state and primary_instance gets de-assigned .Check if any hung message detected in *qm0*.trc files

This can be fixed by altering queue

execute dbms_aqadm.alter_queue_table (queue_table => 'ABDU_QUEUE',primary_instance => 2,secondary_instance => 1);

select MSG_ID,MSG_STATE,DELAY,ENQ_TIME

from USER01.AQ$ABDUL_QUEUE

where MSG_STATE='WAIT'

order by ENQ_TIME;

select owner, queue_table, object_type, owner_instance, primary_instance, secondary_instance

from dba_queue_tables where queue_table='ABDUL_QUEUE';

AQ Messages Not Changing Status from DELAY to READY Or READY to PROCESSED In RAC Databases (Doc ID 2879519.1)

2) Issue 2 :

Sharded Queues : Sharded AQ:RAC: MESSAGES in READY when SHARD OWNERSHIP HAS CHANGED.

select distinct MSG_PRIORITY from owner.aq_table ;

select * from GV$AQ_CACHED_SUBSHARDS ;

select * from GV$AQ_REMOTE_DEQUEUE_AFFINITY;

select * from SYS.aq$_queue_shards;

select INST_ID,JOB_ID,QUEUE_NAME,JOB_STATE,MSGS_SENT,OWNER_INSTANCE_ID from GV$AQ_CROSS_INSTANCE_JOBS;

3) Jms listener invoker failed

we might see this error in application logs if queue are not started

Purge Advanced Queue Table if auto purge not happening

1-)Identifiy how many data you will purge. If you want purge all off them you can easily purge without condition.

select owner,name,queue_table,retention

from dba_queues

where owner='OWNER_AQ'

and queue_table='QUEUE_TABLE_T'

and queue_type='NORMAL_QUEUE';

select count(1)

from OWNER_AQ.AQ$QUEUE_TABLE_T

where msg_state=’PROCESSED’ and enq_time<to_date(’16/10/2017 17:05:30′,’dd/mm/yyyy hh24:mi:ss’);

2.1-) Purge queue table with conditions.

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

po.block := TRUE;

DBMS_AQADM.PURGE_QUEUE_TABLE(

queue_table => 'OWNER_AQ.QUEUE_TABLE_T',

purge_condition => 'qtview.queue = ''QUEUE_TABLE_Q''

and qtview.enq_timestamp < to_date(''16/10/2017 17:05:30'',''dd/mm/yyyy hh24:mi:ss'') and qtview.msg_state = ''PROCESSED''', purge_options => po);

END;

2.2-) Purge queue table without conditions. With this option you will gain space like truncate command on table .

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

dbms_aqadm.purge_queue_table('Queue_Table_Name', NULL, po);

END;

2.3-) If your dataset is big it can be executed like that. Condition may change.

begin

FOR i IN 1..15

LOOP

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

po.block := TRUE;

DBMS_AQADM.PURGE_QUEUE_TABLE(

queue_table => ‘OWNER_AQ.QUEUE_TABLE_T’,

purge_condition => ‘qtview.queue = ”QUEUE_TABLE_Q”

and qtview.enq_timestamp < to_date(”’||i*2||’/09/2017 05:05:30”,”dd/mm/yyyy hh24:mi:ss”) and qtview.msg_state = ”PROCESSED”’, purge_options => po);

END;

commit;

dbms_lock.sleep(20);

END LOOP;

end;

References :

1) https://www.oracle.com/a/otn/docs/database/aq-db19c-technicalbrief.pdf

2) https://www.oracle.com/a/otn/docs/database/jmssq-db19c-technicalbrief.pdf

https://documentation.braintribe.com/Content/customer/oeamtc/oeamtcdocu/Troubleshooting/Oracle%20Advanced%20Queues%20(AQ).htm

4) https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#ADQUE0100

5) https://docs.oracle.com/database/121/STRMS/strms_qpmon.htm#STRMS908

After import of queue: ORA-25226 dequeue failed queue not enabled for dequeue (Doc ID 1290221.1)

https://www.oracle.com/docs/tech/database/jmssq-db19c-technicalbrief.pdf

https://www.morganslibrary.org/reference/demos/aq_demo_rac.html

8) What Objects Are Created When Creating a Queue Table ? (Doc ID 224027.1)

9) https://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_intro.htm#ADQUE2435

Abdul hafeez kalsekar -- OCS/OCE/OCP

Saturday, March 11, 2023

Troubleshooting advanced queue issues in Oracle 19c database