Saturday, March 11, 2023

Troubleshooting advanced queue issues in Oracle 19c database

 

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


Or 

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

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'><!--BODY {background: ffffc6} --></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




ValueNameMeaning
0READYThe 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
1WAITING or WAITThe delay specified by message_properties_t.delay while
executing dbms_aq.enqueue has not been reached.
2RETAINED OR PROCESSEDThe 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.
3EXPIREDThe 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.
4IN MEMORYUser-enqueued buffered message
7SPILLEDUser-enqueued buffered message spilled to disk
8DEFERREDBuffered messages enqueued by a Streams Capture process
9DEFERRED SPILLEDCapture-enqueued buffered messages that have been spilled to disk
10BUFFERED EXPIREDUser-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

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

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

7) 
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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)