Skip to Main Content
  • Questions
  • Advanced Queuing & PL/SQL Notification

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jens.

Asked: March 21, 2003 - 2:38 pm UTC

Last updated: October 09, 2012 - 2:28 pm UTC

Version: 9.2

Viewed 50K+ times! This question is

You Asked

hello tom,

I hope you don't get angry now, if I "redirect" you to </code> http://asktom.oracle.com/pls/ask/f?p=4950:61:166184132340407832::::P61_ID:442119935770 <code>

there is MY MOST important question. TODAY I've got your book - but unfortunately I haven't found hints or even solutions for THAT problem in it (yet).
could you P L E A S E show me the way completing my sample (or giving any other useful help)? that would be very, very kind of you.

thanks a lot for all your support.

and we said...

Here is an example of an asyncronous dequeue of a message from a queue using PLSQL.

(just a reminder -- i've got a life too, sometimes camping without a laptop and not concocting examples is something I do too. I did say I was sort of busy and would try to get you an example when I could)...

Anyway -- here it is:



aq@ORA920> @connect "/ as sysdba"

sys@ORA920> drop user aq cascade;
User dropped.

sys@ORA920> CREATE USER aq IDENTIFIED BY aq;
User created.

sys@ORA920> GRANT CONNECT, RESOURCE, aq_administrator_role TO aq;
Grant succeeded.

sys@ORA920> GRANT EXECUTE ON dbms_aq TO aq;
Grant succeeded.

sys@ORA920> GRANT EXECUTE ON dbms_aqadm TO aq;
Grant succeeded.

sys@ORA920> begin
2 dbms_aqadm.grant_system_privilege
3 ('ENQUEUE_ANY','AQ',FALSE);
4 dbms_aqadm.grant_system_privilege
5 ('DEQUEUE_ANY','AQ',FALSE);
6 end;
7 /
PL/SQL procedure successfully completed.

sys@ORA920> @connect AQ/AQ

aq@ORA920> CREATE type aq.Message_typ as object
2 ( subject VARCHAR2(30), text VARCHAR2(80));
3 /
Type created.

that'll be our message payload..


aq@ORA920> begin
2 DBMS_AQADM.CREATE_QUEUE_TABLE
3 ( queue_table => 'aq.objmsgs80_qtab',
4 queue_payload_type => 'aq.Message_typ',
5 multiple_consumers => TRUE );
6
7 DBMS_AQADM.CREATE_QUEUE
8 ( queue_name => 'MSG_QUEUE',
9 queue_table => 'aq.objmsgs80_qtab');
10
11 DBMS_AQADM.START_QUEUE
12 ( queue_name => 'MSG_QUEUE');
13 end;
14 /
PL/SQL procedure successfully completed.

aq@ORA920> create procedure enqueue_msg( p_msg in varchar2 )
2 as
3 enqueue_options dbms_aq.enqueue_options_t;
4 message_properties dbms_aq.message_properties_t;
5 message_handle RAW(16);
6 message aq.message_typ;
7 BEGIN
8 message := message_typ('NORMAL MESSAGE', p_msg );
9 dbms_aq.enqueue(queue_name => 'msg_queue',
10 enqueue_options => enqueue_options,
11 message_properties => message_properties,
12 payload => message,
13 msgid => message_handle);
14 end;
15 /
Procedure created.

a simple procedure to enqueue a message onto that queue

aq@ORA920> create table message_table( msg varchar2(4000) );
Table created.

we'll use that table to let our procedure 'communicate' with us -- our dequeue procedure that is called automagically


aq@ORA920> create or replace procedure notifyCB( context raw,
2 reginfo sys.aq$_reg_info,
3 descr sys.aq$_descriptor,
4 payload raw,
5 payloadl number)
6 as
7 dequeue_options dbms_aq.dequeue_options_t;
8 message_properties dbms_aq.message_properties_t;
9 message_handle RAW(16);
10 message aq.message_typ;
11 BEGIN
12 dequeue_options.msgid := descr.msg_id;
13 dequeue_options.consumer_name := descr.consumer_name;
14 DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
15 dequeue_options => dequeue_options,
16 message_properties => message_properties,
17 payload => message,
18 msgid => message_handle);
19 insert into message_table values
20 ( 'Dequeued and processed "' || message.text || '"' );
21 COMMIT;
22 END;
23 /
Procedure created.

aq@ORA920> begin
2 dbms_aqadm.add_subscriber
3 ( queue_name => 'aq.msg_queue',
4 subscriber => sys.aq$_agent( 'recipient', null, null ) );
5 end;
6 /
PL/SQL procedure successfully completed.

aq@ORA920> BEGIN
2 dbms_aq.register
3 ( sys.aq$_reg_info_list(
4 sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
5 DBMS_AQ.NAMESPACE_AQ,
6 'plsql://AQ.notifyCB',
7 HEXTORAW('FF')) ) ,
8 1 );
9 end;
10 /
PL/SQL procedure successfully completed.

and that should do it -- ready to go -- and we test:



aq@ORA920> select * from message_table;
no rows selected

nothing up my sleeve...

aq@ORA920> exec enqueue_msg( 'This is a test....' );
PL/SQL procedure successfully completed.

aq@ORA920> commit;
Commit complete.

commit needed, we don't commit in the enqueue procedure..

aq@ORA920> pause

aq@ORA920> select * from message_table;

MSG
-------------------------------------------
Dequeued and processed "This is a test...."


And there it is.



Rating

  (135 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tom, I REALLY NEED your help.

A reader, March 22, 2003 - 3:03 pm UTC

I've no idea of how to get a PL/SQL procedure notfified, and I'm not sure if I've understood the concept and process of notification at all. procedure has to be "registered" - that's clear. but I thought enqueuing a message is the event on wich the procedure is invoked. so I don't understand the purpose of "post" then. I don't unterstand the callback function's signature. there are more question than answers to me. could you please give me a hand and make it clear?

Tom Kyte
March 23, 2003 - 5:16 pm UTC

P L E A S E give me time -- i have your question. It is in my queue.

A reader, March 24, 2003 - 12:06 pm UTC

first of all I have to apologize for being so impatient ( when I saw some answers on newer questions). I really didn't want to be unkind.
thanks a lot for that sample. its far better than any other sample I've found so far (in addition the only complete one). now I can "play around" to get it in more detail. btw: could you suggest special sources that cover this? thanks.

Tom Kyte
March 24, 2003 - 12:19 pm UTC

otn.oracle.com
- discussion forums
- products
- database
- Oracle Advanced Queueing

A reader, March 24, 2003 - 6:03 pm UTC

it's me again, tom. now I'm trying to use POST. so first I changed namespace of registration to anonymous:

DBMS_AQ.Register(SYS.AQ$_Reg_Info_List(SYS.AQ$_Reg_Info('AQ.MSG_QUEUE:RECIPIENT', DBMS_AQ.NAMESPACE_ANONYMOUS, 'lsql://AQ.NotifyCB', HEXTORAW('FF'))), 1);

knowing why 'hextoraw('FF')' - taken from your sample - is appropriate for 'context' parameter (and when it is not) is one of the things I haven't found in any documentation so far. the same is true for parameter 'payload' of POST as well. I expected to be able to get "Message_Typ" utilized instead of that raw ...

subscriptions for multiconsumer queues contain schema, queue name and consumer name, namespace has to be anonymous in order to get notifified via POST. so after all I posted:

declare
pl SYS.AQ$_POST_INFO_LIST := SYS.AQ$_POST_INFO_LIST();

begin
pl.Extend(1);
pl(pl.COUNT) := SYS.AQ$_POST_INFO('AQ.MSG_QUEUE:RECIPIENT', DBMS_AQ.NAMESPACE_ANONYMOUS, 'plsql://AQ.notifyCB', HEXTORAW('FF'));

DBMS_AQ.POST(pl, 1);
end;

but finally nothing happened - so there are some questions left: shouldn't the registered callback be invoked? should POST have placed a message with content 'FF' hex in the given queue? and what 's the difference to enqueue then? is it about the namespaces - what is their purpose in general? what went wrong? why using the default NULL as value for 'payload' within AQ$_POST_INFO object terminats the session during POST?

Tom Kyte
March 24, 2003 - 8:07 pm UTC

what are you trying to use POST, what's the goal there?

I mean -- what is the problem you are trying to solve...

A reader, March 25, 2003 - 2:24 am UTC

first of all I just try to understand the difference between notification on enqueuing events and post.
the purpose of post (and anonymous subscriptions as well) is not clear to me. generally speaking I want to know, when and how to use them (instead ot the other?).

Tom Kyte
March 25, 2003 - 8:04 am UTC

for this -- i'll suggest you try out the discussion forums on otn.oracle.com. I am not expert in AQ at this level.

A reader, March 26, 2003 - 3:55 am UTC

thanks, tom.

now - with your help - I'm at least quite sure, that I didn't miss any important source of information (it 's hard to believe). hope that somebody out there can give me a hand on those topics ...

A reader, March 30, 2003 - 8:59 am UTC

hello tom,

I'm now trying to propagate messages from one queue to another. therefore I modified the above sample like that:

connect "/ as sysdba"

drop user aq cascade;
CREATE USER aq IDENTIFIED BY aq;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aq;
GRANT EXECUTE ON dbms_aq TO aq;
GRANT EXECUTE ON dbms_aqadm TO aq;

begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQ',FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQ',FALSE);
end;
/

connect AQ/AQ
CREATE type aq.Message_typ as object(subject VARCHAR2(30), text VARCHAR2(80));
/

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aq.objmsgs80_qtab',
queue_payload_type => 'aq.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUE',
queue_table => 'aq.objmsgs80_qtab');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUE');
end;
/

-- ADDED: Setup additional queue to propagate messages to
begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aq.objmsgs80_qtabX',
queue_payload_type => 'aq.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUEX',
queue_table => 'aq.objmsgs80_qtabX');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUEX');
end;
/

create or replace procedure enqueue_msg(p_msg in varchar2)
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aq.message_typ;
recipients DBMS_AQ.aq$_recipient_list_t;

BEGIN
-- ADDED
recipients(1) := SYS.aq$_agent('RECIPIENT', null, null);
message_properties.recipient_list := recipients;

message := message_typ('NORMAL MESSAGE', p_msg );
dbms_aq.enqueue(queue_name => 'msg_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/

begin enqueue_msg('This is a test....'); commit; end;
/

-- ADDED: Create loopback database link
create database link AQ.LoopBack connect to AQ identified by AQ using 'ORCL';

-- ADDED: Setup scheduling for messages
begin DBMS_AQADM.Schedule_Propagation(Queue_Name => 'MSG_QUEUE',
Destination => 'AQ.LOOPBACK',
Start_Time => sysdate,
Latency => 0);
end;
/

-- Check scheduling: Everything checked out OK.
select * from user_queue_schedules;

begin enqueue_msg('This should be propagated.'); commit; end;
/

-- Check scheduling: Neither error nor action reported
select * from user_queue_schedules;

begin
DBMS_AQADM.stop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue_table('aq.objmsgs80_qtab');
DBMS_AQADM.stop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue_table('aq.objmsgs80_qtabX');
end;
/

I ran this script several times and examined both queues via enterprise manager console, expecting all messages from queue MSG_QUEUE to be propagated to MSG_QUEUEX.

but there was no content shown for the second queue. user_queue_schedules didn't report any problems, but sometimes enterprise manager did: although the db-link works very well "advanced queues/administration/topology" reported "db-link with errors" (as a red dotted line) - but in addition 0 as error-count. this only occured, if a db-link was given for parameter DESTINATION. otherwise a message like "no propagation has been established for queues within this database" appeared, even if there was one (and this could be seen under "propagation plans" as well).

now, I've once again no idea, how to go on. could you please help once more?

thank you.

Tom Kyte
April 25, 2003 - 12:44 pm UTC

I asked Sean Dillon, a technologist on my team who knows AQ, to take a look at this and here's what he had to say:
---------

So there are few things we can address. First of all, queue propagation doesn't happen immediately. There is a bit of delay when you enqueue the first message into a queue that has an enabled queue schedule. Once the first message goes, it's very fast because the job queue process is waiting for the next message. So the script above, even if everything was correct, would not see the message get propagated because you immediately stop and drop the queues before they have an opportunity to propagate.

When deciding how you will propagate a message from queue A to queue B, you have a couple of choices. You can either specify a subscriber on the queue, which means all message queued to the message should be send, you can specify a rule-based subscriber on the queue, which means messages that conform to the rule will be sent, or you can specify a recipient list in the message itself. In your example above, you are specifying a recipient list. This means the only way Oracle knows who the message is intended for is via the recipient list. You can schedule propagation (as you have done), but this doesn't mean all the messages will be propagated (as you have seen :-)).

What I have done is added a parameter to your enqueue_msg procedure for the agent address. When you are instantiating an aq$_agent for the recipient_list, I pass this parameter in the ADDRESS field of the aq$_agent object. This lets you enqueue to a null address or to a remote queue. Then, when I enqueue the 'This is a test....' message, it will never be propagated because it's agent address is NULL. When I enqueue the 'This should be propagated.' message, it WILL BE propagated because I pass 'aqtest.MSG_QUEUEX@aqtest.loopback' as the address.

Here's the complete example, with edits:

----------------------
-- SMD: i moved this up so my queues would exists AFTER the example had run.
-- SMD: it will fail the first time you run this script.
connect aqtest/aqtest

begin
DBMS_AQADM.stop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue_table('aqtest.objmsgs80_qtab');
DBMS_AQADM.stop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue_table('aqtest.objmsgs80_qtabX');
end;
/

connect sys/o9isb9 as sysdba
drop user aqtest cascade;

CREATE USER aqtest IDENTIFIED BY aqtest;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aqtest;
GRANT EXECUTE ON dbms_aq TO aqtest;
GRANT EXECUTE ON dbms_aqadm TO aqtest;

begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
end;
/

connect aqtest/aqtest
CREATE type aqtest.Message_typ as object(subject VARCHAR2(30), text VARCHAR2(80));
/

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aqtest.objmsgs80_qtab',
queue_payload_type => 'aqtest.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUE',
queue_table => 'aqtest.objmsgs80_qtab');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUE');
end;
/

-- ADDED: Setup additional queue to propagate messages to
begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aqtest.objmsgs80_qtabX',
queue_payload_type => 'aqtest.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUEX',
queue_table => 'aqtest.objmsgs80_qtabX');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUEX');
end;
/

-- SMD: you'll see there is now a second parameter, as discussed above:
create or replace procedure enqueue_msg(p_msg in varchar2,
p_add in varchar2 default null)
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aqtest.message_typ;
recipients DBMS_AQ.aq$_recipient_list_t;
BEGIN
-- ADDED
-- SMD: here's where the parameter is used
recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
message_properties.recipient_list := recipients;

message := message_typ('NORMAL MESSAGE', p_msg );
dbms_aq.enqueue(queue_name => 'msg_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/

-- SMD: this msgs is meant for THIS queue and will not be propagated.
begin enqueue_msg('This is a test....'); commit; end;
/

-- ADDED: Create loopback database link
create database link aqtest.LoopBack connect to aqtest identified by aqtest using 'funk92.us.oracle.com';

-- ADDED: Setup scheduling for messages
begin DBMS_AQADM.Schedule_Propagation(Queue_Name => 'MSG_QUEUE',
Destination => 'aqtest.LoopBack',
Start_Time => sysdate,
Latency => 0);
end;
/

-- Check scheduling: Everything checked out OK.
select * from user_queue_schedules;

-- SMD: this msgs is meant for the MSG_QUEUEX queue and WILL BE propagated.
begin enqueue_msg('This should be propagated.', 'aqtest.MSG_QUEUEX@aqtest.LoopBack'); commit; end;
/

-- Check scheduling: Neither error nor action reported
select * from user_queue_schedules;
----------------------------------

I think this is what you're looking for:

aqtest@FUNK92> select t1.cnt, t2.cnt
2 from (select count(*) cnt from objmsgs80_qtab) t1,
3 (select count(*) cnt from objmsgs80_qtabx) t2
4 /

CNT CNT
---------- ----------
1 1

Hope that helps!

_smd_

A reader, April 26, 2003 - 7:25 am UTC

thank you tom for propagating my enqueued message to sean. I would appreciate it soooo much if your book (the B E S T! I've ever read) would have covered my favorite topics such as AQ and XML and fine grained auditing as well - looking forward to the 9i edition ...

thank you very very very much sean for the solution and the explanations you gave. I didn't recognize that agent-type as part of the problem at all ...

did you "get" it all from ORACLE's docs? can you recommend BOOKS for AQ and XML ORACLE features which provide an insight into those issues in the same fashion like tom's famous expert-one-on-one-book does?

just one question answered the next one pops up: now I'm wondering how one would get a CERTAIN queue (if there are many) addressed on the remote site. there must be a way to achieve this, but how?

Tom Kyte
April 28, 2003 - 10:13 am UTC

Hi Sean here again,

You'll notice in the code sample I sent, when we constructed the user agent we specified the queue name on the remote site:

begin enqueue_msg('This should be propagated.',
'aqtest.MSG_QUEUEX@aqtest.LoopBack'); commit; end;
/

aqtest.MSG_QUEUEX@aqtest.LoopBack basically said 'propagate to the MSG_QUEUEX queue that belongs to the AQTEST schema in the database identified by link aqtest.LoopBack.'

Yes, I extracted all this information from the Oracle documentation. I don't know of any commercial books covering AQ, but Steve Muench has a great O'Rielly book on Oracle's XML Developer's Kits. One thing Steve didn't mention is anything to do with Oracle XML DB (due to the fact that his book was written in the 8i time frame). I would recommend you check the Oracle documentation (avail. on OTN) on these technologies as a first step, anyway :-).

Advanced Developer's Guide - Advanced Queues
XML API Reference
XML Database Developer's Guide
XML Developer's Kits Guide

Hope that helps!

A reader, April 28, 2003 - 12:29 pm UTC

thank you, sean.

Is Advanced Queuing implemented using DBMS_PIPE?

Oracle Fan, July 05, 2003 - 1:01 am UTC

Hi Tom,

I would like to know what technology works underneath Advanced Queuing. Is it implemented using DBMS_PIPE? Or are these two related to each other in some way?

It will be interesting to know what other Oracle components are implemented using DBMS_PIPE, such as Workflow, DBMS_JOB, Streams, XML Gateway, etc.


Thanks for your effort.

Tom Kyte
July 05, 2003 - 9:43 am UTC

pipes are just a lightweight IPC mechanism. they are not really used heavy duty in any of those things. It is just a utility.

message does not get dequeued

KU, September 16, 2003 - 6:02 pm UTC

I followed your example code of plsql notification. I am sure I didn't make any mistake anywhere. The plsql notification doesn't seem to be occurring. My aq_tm_processes parameter = 1 and job_queue_processes = 1.
(In init.ora, no spfile is present).
Db version is 9.2.0.4 on NT, job_queue_interval parm is obsolete(default 5secs).
I can see the messages in the queue table with the recepient name ('RECEPIENT').
Any pointer as to non-notification, please?


non-notification issue cleared

KU, September 24, 2003 - 2:28 pm UTC

I recreated the message_table with 'tablespace' option as I read in a forum. After this the plsql notification started working.
Thanks anyway,
KU


PL/SQL notifications does not work on SE ?

Sasa, October 20, 2003 - 7:40 am UTC

Hi Tom,

PL/SQL notifications for AQ does not work on SE?

Thanks.



Tom Kyte
October 20, 2003 - 8:45 am UTC

never tried -- don't see it listed separately in the new features guide.

are you getting "an error" -- that would be useful to see.

Don't get an error

Sasa, October 20, 2003 - 10:05 am UTC

I dont get any error but message is not dequeued and not inserted into message_table.

Same time I tried on EE and it works fine.
Must say I tested in single consumer enviroment but everything else is just as in your example.
Thanks



Tom Kyte
October 20, 2003 - 10:56 am UTC

aq_tm_processes is set to what?

any messages in the alert log?

Seems some mistake in DB

Sasa, October 21, 2003 - 4:08 am UTC

Hi Tom,

aq_tm_process is set to 1.

Found this is in alert log this morning(it seems that our DB will have to check this):

Corrupt block relative dba: 0x00400864 (file 1, block 2148)
Bad check value found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x00400864
last change scn: 0x0000.24a7f902 seq: 0x1 flg: 0x06
consistency value in tail: 0xf9020601
check value in block header: 0x3c7a, computed block checksum: 0x4
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x00400864 (file 1, block 2148) found valid data
Tue Oct 21 09:46:59 2003
Restarting dead background process EMN0
EMN0 started with pid=13

Thanks

In AQ, can I use a varray type as a payload? (8.1.7)

Mariano, April 30, 2004 - 4:09 pm UTC

Let's say I want to enqueue a varray with 20 items (just numbers). Every example I read take a record type as payload parameter, when I replicate them but use a varray type as payload it fails on compilation time.
As example,
create or replace type t$lookup_cst is varray(10) of number;
/
begin
sys.dbms_aqadm.create_queue_table(
queue_table => 'AQ$CODIFICATIONS',
queue_payload_type => 'T$LOOKUP_CST',
multiple_consumers => false);
sys.dbms_aqadm.create_queue(queue_name => 'CODIF$QUEUE',
queue_table => 'AQ$CODIFICATIONS');
sys.dbms_aqadm.start_queue(queue_name => 'CODIF$QUEUE');
end;
/
create or replace procedure enqueue_codification_request(p$array in t$lookup_cst) is
v$enq_opts dbms_aq.enqueue_options_t;
v$mess_typ dbms_aq.message_properties_t;
v$handler raw(16);
begin
dbms_aq.enqueue(queue_name => 'CODIF$QUEUE',
enqueue_options => v$enq_opts,
message_properties => v$mess_typ, payload => p$array,
msgid => v$handler);
end enqueue_codification_request;
/
The compilation error is PLS-00306: wrong number or types of arguments in call to 'ENQUEUE'.
Any tip, thanks in advance.-


dbms_job server process in infinite loop with dequeue

bob, May 28, 2004 - 10:25 am UTC

This sample was great. Thanks for taking the time to do that.

Is there anything obviously negative about having a dbms_job act as a server process in an infinite loop with a dequeue in that loop. Dequeue by default waits for a new message to queue, so I don't know internally how wasteful waiting in a "wait forever" mode of a dequeue might be. I assume it is better than continually polling with a no-wait dequeue and an infinite loop in a dbms_job seems pretty convenient for a server process. Most people seem to write their own server processes listening/polling for events in infinite loops anyway, so explicitly coding one in a dbms_job seems like an okay approach to me.


Tom Kyte
May 28, 2004 - 11:52 am UTC

...
Is there anything obviously negative about having a dbms_job act as a server
process in an infinite loop with a dequeue in that loop.
.....

yes, makes the server that much harder to shutdown. jams a job queue forever. not what it was designed to do.


the example isn't in a "wait forever", it works like a job -- get a message and database runs your job.


use AQ as it would be the appropriate technology for the job at hand.

example was just a starting point..

bob, May 28, 2004 - 4:22 pm UTC

I didn't use the callback aspect of your example,but it was a great starting point.

I want to process one order at a time (for system resource reasons), and various applications are intermittently submitting orders that must queue up to be processed.

The order enqueues a message, I have a process that manually dequeues and processes messages in an infinite loop in a procedure and I submitted that procedure as a job to keep it running all the time in the background. The dequeue call in the job sits in a wait forever mode until a message arrives so it isn't chewing up cpu cycles loop, and I assume it is just awaiting notification. (jamming up a single slot in a job queue as you mentioned).

I didn't specifically didn't use the callback, because I didn't want every message enqueued to start a new process via the callback.

What do you suggest as a alternative?

a sql*plus script running in the background that calls the procedure that dequeues in the loop that checks for a flag each loop to see if it is time to stop gracefully?

You said:
"use AQ as it would be the appropriate technology for the job at hand. "

I am not sure if you are suggesting here that aq wasn't appropriate. It was my understanding that the problem I was attempting to solve with a queue is exactly what I believe the docs describe as the appropriate use of AQ

AQ facilitates..
"Applications that do not have the resources to handle multiple unprocessed messages arriving simultaneously from external clients or from programs internal to the application."

Have a good Memorial Day weekend and as always I appreciate your forum for answering our questions and allowing us to express ideas for your review and suggestions.

Tom Kyte
May 28, 2004 - 8:01 pm UTC

it won't be a new process - it's the AQ processes that do this.

short of that, write a plsql procedure that does what you want and use sqlplus as you suggested (but aq notification is what you are looking for. no muss, no fuss, nothing to start)


you would either

a) write an external program in whatever language that dequeues/processes loops
b) write a stored procedure that AQ will call to deal with the message.

Advanced Queues Work fine here

Puneet Mathur, May 29, 2004 - 6:41 am UTC

Wow tom,
i need your support on Advanced Queues.
I have an application which requires to keep changed records in memory when they are UDI means Updated, Inserted and Deleted.

So could i use Advanced Queues through Triggers to build a queue and then receive it via a Java Procedure JDBC API call to fetch them in the application.

Application is pure java based but as a DBA can i give this backend support to the application.
The application needs to cache records while interacting withother application calls.

Tom Kyte
May 29, 2004 - 11:28 am UTC

sorry, insufficient details. I don't understand the "caching" requirement here.

aq notification?

bob, May 29, 2004 - 6:51 pm UTC

Tom,

sorry to beat this to death, but I feel I am not understanding your feedback.

I don't understand how notification could work in the case where the message dequeue rate and subsequent processing needs to happen at a controlled rate. Notification would continually send notifications to a new callback procedure for each and every message arrival, which would in turn start processing on each message as it arrives.

My limited imagination sees the following scenarios.

1. A client application running in the background polls a queue for new messages on an interval, or continually sits in a dequeue waiting state for the arrival of a new message.. or (client could be any language capable of interacting with the AQ)

2. A notification on the arrival of a message in the queue starts a callback which than would need to call a process manager procedure which would act as another work queue to verify that work was finished on the previous messages and ready to start new messages. If it was ready, processing could begin, if not, it would have to tell the processor that there was "X" # of messages waiting for dequeue.

#2 seems like a lot of work and an additional moving piece just to simply avoid sitting in a dequeue waiting state forever awaiting the arrival of a message.

But where my confusion lies, is that I think you were suggesting notification/registration could easily handle the X at a time requirement for processing. I contend that notification requires that I write a process manager to keep track of how many notifications I have received, and which ones still need to be processed because they couldn't be immediately processed at the time the notification was received. Am I wrong about that?

Tom Kyte
May 30, 2004 - 10:38 am UTC

there are only so many queue processes.

they either

a) see the message in the queue and do the notification of your process. when your process calls "dequeue", they go into limbo. They are woken up out of this limbo by an AQ dequeue process

or

b) see the message in the queue and see that the target is a stored procedure. They then run said stored procedure.


I'm not following all of the nuances of your conversation but either or works. If you want it "simple", and the target of your message is going to be PLSQL, use that approach. If the target of your message is going to be java, c, whatever -- use the other.

Can AQ talk to JMS Queues and how

karma, May 30, 2004 - 9:36 am UTC

I need to make AQ talk and propogate messages to JMS Queues. Is it possible ? If so how ? Any examples pointers?

Can AQ talk to JMS Queues and how

karma, May 30, 2004 - 9:37 am UTC

I need to make AQ talk and propogate messages to JMS Queues. Is it possible ? If so how ? Any examples pointers?

Tom Kyte
May 30, 2004 - 10:47 am UTC

Sushanta

Sushanta Panda, June 16, 2004 - 10:44 am UTC

Tom,
I have to made a KPI tool.The job of the KPI tool is to log the statistics whenever run a process(for an e.g Processes like recruitment of a new employee,giving hike to employee, generate pay-slip of an employee).So whenever any process is run the KPI tool will log the statistics in a master table like this

Event id Event Name Date User Sub-Event
-------- --------------------------- ----- ---- ---------
1 Emp recruitred successfully. 1-1-04 SCOTT 123
1 Emp recruitred Failed 2-1-04 SCOTT 234
2 Giving Hike to Employee Succe 4-4-04 SUS 765
2 Giving Hike to Employee Succe 4-4-04 SUS 765
3 Generate Payslip Failed 4-4-04 SCOTT 123

Where
Event ID : Unique for each Processes
Event Name : Process Name
Date : Date at wich the Event is being occured
User : Which is the User who run the process
Sub Event : Any data froim the Process
(Such as Employee number whenever recruitment
of a new employee)

Here the various process are written in PL/SQL in procedure and functions, where we have to put the KPI tool, so whenever the process completes we have to log the statistics in the master table.
Please suggest how to built the KPI tool so that it has minimal impact of performance.
We have 3 different options to built the KPI tool

1) Autonomous Transaction
But the problem in the autonomous transaction it will not work across DBlink, because we have to store the log statistics on different database.

2) Enqueue or dequeue
3) By writing the log statistics in a file whenever the process run and we make a different process which will insert the information into a table by reading from the file.

Tom is there any option available which will best in to built the KPI tool. Which one will be best.


Thanks And Best Regards
Sushanta



Tom Kyte
June 16, 2004 - 1:09 pm UTC

1) no idea why autonomous transactions would even come into play. Consider autonomous transactions to be generally evil things to be avoided. I have yet to see them used "properly". I don't see how they even apply here at all.

2) not sure where this comes from either.

3) no no no.


this looks like you want to write a simple stored procedure that takes some inputs (the row values) and simply "inserts"

databases were born to join
databases were born to write to.

this is a simple, single insert. nothing more, nothing less.




Sushanta

Sushanta, June 17, 2004 - 1:01 am UTC

Tom,
We are using
1) autonomous transaction, because during the running of the process, if it fails,
then the entire transaction done by the process is rollback (as per our functionalty),
so our insert statement into the master table is also rollback, because we call the
stored procedure(where the insert statement is placed) inside the process,
but in this case also we have to log the statistics (in any case whether the transaction
completes successfully or not) with failure.

Problem: Autonomous transaction will not work across the dblink, because we log the statistics
in the database B, where as our processes are running in database A.

2) Because of "autonomous transaction is not working across dblink", "dbms_aq" comes into picture.
Because even if the entire transaction of the process "ROLLBACK", our information is not rollback
because it is already queued (from the start of the process)

3) Similary writing into the file and read from the file and insert into the table by another process
is comes into the picture.
However you say 3 no "no no no", so it is a bad idea to use.


Tom because of the problem which is mention in (1), we could not use a single insert.
Also could you please suggest is there is any better options available.
Also if the problem mention in point (1) is not there then "single insert" is the
best solution.

Thanks And Best Regards
Sushanta

Tom Kyte
June 17, 2004 - 9:35 am UTC

1) proper error handling obviates the need for this.

begin
process;
commit & log success;
exception
when ....
rollback;
log failure;
end;

2) aq is transactional as well. it would either commit your transaction then and their or not (and hence would rollback with everything else)

3) error handling, all this is error handling.

Sushanta

Sushanta, June 17, 2004 - 11:20 am UTC

Hi Tom,
Thanks For ur valuable time & co-operation. Please tell
me if
(1)
i have to choose between "EnQueue and DeQueue" and
"File system" (writing to a file and then read) which
will be better from the performane,scalability and
maintanance point of view, obiously right now i donot
have what is requirement to use either, but for knowledge
and usebility point of view.
(2)
Where should "DeQueue and Enqueue" should be used(One
generic example)
(3)
Where should the "dbms_utility" ie the file system is
used

Thanks And Best Regards
Sushanta

Tom Kyte
June 17, 2004 - 2:08 pm UTC

1) option 3 -- do it via error handling. as i said, enqueue/dequeue are transactional. they'll either commit your work OR will be rolled back. don't see where you are going with that thought.

files -- not a chance, not a chance.

begin
try the thing;
log success;
commit;
exception
when others
then
rollback;
log failure;
commit;
RAISE;
end;

that is what you are looking for.

Sushanta

Sushanta, June 18, 2004 - 7:52 am UTC

Tom,
Thanks a lot for ur response. I have a question on "enQueue and Dequeue". As you said "enQueue and Dequeue"
is transactional and it will either commit the whole transaction when commit is issued by the process (where the
"enQueue and Dequeue" process is called) or rollback the whole transaction whene the process is rollback .
However in the document in Oracle-9i it is mention that "enQueue and Dequeue" has an "visibility option"
where it mention 2 parameters "ON_COMMIT" and "IMMEDIATE"
which is defined as below


ON_COMMIT: The enqueue is part of the current transaction. The operation is complete when the transaction commits. This setting is the default.

IMMEDIATE: The enqueue is not part of the current transaction. The operation constitutes a transaction on its own. This is the only value allowed when enqueuing to a non-persistent queue.

From this when the parameter is "IMMEDIATE", the data which is "Enqueue" is independent of the process transaction and will not altered with commit or rollback of the process transaction.

Thanks And Best Regards
Sushanta

Tom Kyte
June 18, 2004 - 10:52 am UTC

did you see "non persistent queue" there. do you understand what "non persistent" means.


non persistent means "data can go poof, never be processed, just disappears -- and that is OK with me"


now what.

AQ a replacement of DBMS_PIPE

Peter Janne, June 21, 2004 - 6:06 pm UTC

Tom,
Is AQ a replacement of DBMS_PIPE?

Your reply is appreciated.

Tom Kyte
June 21, 2004 - 8:56 pm UTC

no, dbms_pipe is a simple FIFO "IPC" thing. AQ has alot more sophistication and capabilities.

importing queue tables

A reader, August 09, 2004 - 9:40 pm UTC

Tom,
2 questions
1) Would appreciate apprecite your comments on copying queue tables from once schema to another or from one db to another.

2) Can we use the export/Import utilities to export/import queue tables? What other metadata tables need to be exported/imported in addition to the tables below
AQ$_<queue_table>_I (the dequeue IOT)
AQ$_<queue_table>_T (the time-management IOT)
AQ$_<queue_table>_S (the subscriber table)
AQ$_<queue_table>_H (the history IOT)

Thank you for your time.


Tom Kyte
August 10, 2004 - 7:31 am UTC

1) if you mean the schema definition -- just run the scripts, that'll work.

if you mean the data -- i believe you have to enqueue it in the new queue -- it would not be an insert into as select -- these tables are "special"

2) import/export handle queue objects naturally. you should not need to do anything special -- just export the schema.

does the queue-tables slways grow?

Hannibal, August 18, 2004 - 4:08 am UTC

Tom,

a short added question about the tables on Advqnced queues - the qtabs. Does the QTab always grow? I have a customer-size where i run a AQ but when i did run my space_usage script

select segment_name, segment_type, tablespace_name, bytes, blocks, extents, next_extent
from dba_segments where owner=UPPER(USER) order by &1;

I get thid for one of my qtabs.

klassx43@KXXL> @space_usage bytes
SEGMENT_NAME TYPE TABLESPACE BYTES BLOCKS EXTENTS NEXT_EXTENT

------------------------------ ------------ ------------------------------ ------------ ---------- -------- ------------

...

PK_TRANSPORT_ORDER_PROPERTY INDEX KLASSX43I 34,603,008 4,224 33 1,048,576
WMW2KLASSX_QTAB TABLE KLASSX43 392,167,424 47,872 374 1,048,576
------------ ----------
sum 684,720,128 83,584

see, the qtab is by far the biggest consumer of space in my tablespace. How can this happen? The AQ is constantly written by one process and there is an other process constantly consuming the messages. This process must have stopped for weeks or months to make the AQ-Tab grow to this size. But I cant find any other explanation for such a huge qtab. Am i missing something? Are the tables on AQ's special in there storage requirements? Is there someting more to consider when dealing with AQ's?

thx

Hannibal

Tom Kyte
August 18, 2004 - 8:21 am UTC

that table is just 375 meg? it would not take much to have a table grow to that size. space is used and reused in queue tables as would be any other table.

why do you think it would take weeks or months -- unless you generate like one 2k message per day or something? I mean, 375m is just not "that large" is it?




A reader, August 25, 2004 - 4:55 pm UTC

Tom I went thru the Q's in Oracle, but puzzled with a question. Why do we need it? My understanding is that it can be used for some kind of inter session / process communication.

Please explain.

Thank you

Tom Kyte
August 26, 2004 - 9:18 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96587/qintro.htm#65422 <code>

Queueing is a generalized technique for building loosely coupled systems.

Rather than build a system whereby 5 databases must be updated in a single transaction (tightly coupled) -- meaning the system probably is never functioning since at any time, one of the 5 databases might be unavailable, whatever -- you can use something like message queues to "register your intent to update 4 other databases, whilst updating the local database". When you commit that, the request to update the other 4 databases will be delivered AT LEAST once and AT MOST once to the target systems. They each process independently, in the background.


Queueing is a technique to "apparently make the system go faster". Say you have a process that takes 15 seconds to execute. Normally, you make the end users wait for it -- they watch an hour glass for extended periods of time. If you introduce queuing, they now wait for a message to be pushed onto a queue (very fast). In their opinion, the system that was dog slow yesterday is infinitely fast today because they no longer wait 15 seconds per operation. It still takes 15 seconds, they just need not wait for it.

Queueing allows you to scale your system. Suppose you have a "really intensive operation". You have noted that your machine is capable of doing 5 of these at the same time in a performant manner. However, if you run 6, 7, 8, .... of them at the same time, your system really starts thrashing and grinds to a halt. So, with queueing, you control the number of dequeuers -- you can make it so that only 4 of these things can go simultaneously, the "system" runs alot smoother.


and so on....

AQ...Why to subscribe

Chakravarthi Ayyalasomayajula, October 22, 2004 - 11:17 pm UTC

Sir,

I searched on internet and your site is the only site, which explained subscription, registration and notification with an example.

This is very very useful in whatever I was looking for. We want to implement Oracle AQ with JBoss, Oracle being v 8.1.7.4.0 and with JBoss v 4.0.

Thank you so much again,
Chakravarthi

Advanced Queuing & PL/SQL Notification --- SYS user

Richard, November 18, 2004 - 10:08 am UTC

Hello Tom

I've noticed that when we use notification the code is always executed as SYS database user. We are using Oracle Applications and the code must be executed as APPS user due to Applications initialization.
I could not find anything about this in the documentation.
Is there a way to do this?

Tom Kyte
November 18, 2004 - 10:54 am UTC

plsql runs with definers rights -- the procedure can only run as the definer (with privs of the definer) regardless of what "select user from dual" says -- the procedure is run with the base privileges of the OWNER of the procedure.

PL/SQL notification

A reader, January 24, 2005 - 12:31 pm UTC

Tom,

I have tried the same example given by you relating to asynchronous dequeue of a message from PL/SQL. Looks like the procedure doesn't get called. In my database the value of aq_tm_processes is set to 0. Will this cause the PL/SQL procedure not to be notified?

Thanks

Tom Kyte
January 24, 2005 - 2:03 pm UTC

you need something to dequeue and process the messages.

Practical use for Advanced Queuing?

Robert, February 17, 2005 - 3:53 pm UTC

Hi Tom,

(Version 8.1.7.4 and maybe 9.2.0.5)

This post refers back to the question/answer from August 25, 2004 in this thread by 'A reader'.

You said 'Queuing' (I understand 'Oracle Advanced Queuing') is used to implement an application which exists across multiple databases that are 'loosely coupled'.

A colleague of mine says that AQ is for handling messages and workflow prerequisites among different processes, applications, etc.... And indeed this seems like what the documentation and examples seem to indicate.

But I am thinking AQ is a valid method of keeping the data in multiple databases in sync, while at the same time allowing the databases to be loosely coupled... so that if one of the databases is down, the entire system doesn't become unusable (i.e.: using the AQ queues to hold data from one database which would be used to update another database.)

Am I correct?... if so, can you point me to a good example (I have spent some time skimming the documentation).
If I am not correct... what is 'the' method for accomplishing this goal.

Thank you,

Robert.

Tom Kyte
February 17, 2005 - 7:02 pm UTC

we use AQ for replication and streams as a building block technology -- so you are correct that it could be used for that (because we do!)

to keep multiple databases in sync however, please do not reinvent a very old wheel. There is basic replication, advanced replication, and in 9ir2 and up a new technology called streams. documentation for both on otn.oracle.com

Thanks!

Robert, February 18, 2005 - 12:23 am UTC


DBMS_AQADM.Schedule_Propagation seems to break dequeueing

Ian, February 18, 2005 - 10:23 am UTC

Tom

Using your original example and Sean's propagation example I was able to get all the various elements going - but not all at the same time. If I did not have propagation scheduled then the dequeueing worked - but if I had propagation scheduled the the dequeueing stopped working.

Any ideas or is it TAR time?

BTW - Thanks for the examples - saved a lot of RTFM.

Example below on AIX 5.2

Regards

Ian

> sqlplus $PW

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 18 15:56:28 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create database link apps.xxcb_c00_repository connect to apps identified by <password redacted> using '<sid redacted>';
  2
Database link created.

SQL> CREATE type apps.xxcb_c00_message_typ as object(subject VARCHAR2(30), text
VARCHAR2(80));
/
  2    3
Type created.

SQL> begin
  2    DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'apps.xxcb_c00_outbound_qtab',
  3                                  queue_payload_type =>  'apps.xxcb_c00_message_typ',
  4                                  multiple_consumers => TRUE);
  5    DBMS_AQADM.CREATE_QUEUE(queue_name => 'XXCB_C00_OUTBOUND_QUEUE',
  6                            queue_table => 'apps.xxcb_c00_outbound_qtab');
  7    DBMS_AQADM.START_QUEUE(queue_name => 'XXCB_C00_OUTBOUND_QUEUE');
end;
/
  8    9

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> begin
  2    DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'apps.xxcb_c00_inbound_qtab',
  3                                  queue_payload_type =>  'apps.xxcb_c00_message_typ',
  4                                  multiple_consumers => TRUE);
  5    DBMS_AQADM.CREATE_QUEUE(queue_name => 'XXCB_C00_INBOUND_QUEUE',
  6                            queue_table => 'apps.xxcb_c00_inbound_qtab');
  7    DBMS_AQADM.START_QUEUE(queue_name => 'XXCB_C00_INBOUND_QUEUE');
  8  end;
/
  9

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> create or replace procedure xxcb_c00_enqueue_inbound_msg( p_msg in varchar2 )
  2  as
  3   enqueue_options dbms_aq.enqueue_options_t;
  4   message_properties dbms_aq.message_properties_t;
  5   message_handle RAW(16);
  6   message apps.xxcb_c00_message_typ;
  7  BEGIN
  8     message := xxcb_c00_message_typ('NORMAL MESSAGE',  p_msg );
   dbms_aq.enqueue(queue_name => 'XXCB_C00_INBOUND_QUEUE',
                   enqueue_options => enqueue_options,
                   message_properties => message_properties,
                   payload => message,
  9   10   11   12   13                     msgid => message_handle);
 14  end;
/
 15
Procedure created.

SQL> create or replace procedure xxcb_c00_enqueue_outbound_msg(p_msg in varchar2,
  2                                          p_add in varchar2 default null)
  3  as
  4    enqueue_options dbms_aq.enqueue_options_t;
  5    message_properties dbms_aq.message_properties_t;
  6    message_handle RAW(16);
  7    message apps.xxcb_c00_message_typ;
  8    recipients DBMS_AQ.aq$_recipient_list_t;
  9  BEGIN
 10    recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
 11    message_properties.recipient_list := recipients;
 12
 13    message := xxcb_c00_message_typ('NORMAL MESSAGE',  p_msg );
 14    dbms_aq.enqueue(queue_name => 'XXCB_C00_OUTBOUND_QUEUE',
 15                    enqueue_options => enqueue_options,
 16                    message_properties => message_properties,
 17                    payload => message,
 18                    msgid => message_handle);
 19  end;
 20  /

Procedure created.

SQL> create table xxcb_c00_aq_messages( msg varchar2(4000) );

Table created.

SQL> create or replace procedure xxcb_c00_get_aq( context raw,
  2                                        reginfo sys.aq$_reg_info,
  3                                        descr sys.aq$_descriptor,
  4                                        payload raw,
  5                                        payloadl number)
  6  as
  7   dequeue_options dbms_aq.dequeue_options_t;
  8   message_properties dbms_aq.message_properties_t;
 message_handle RAW(16);
 message apps.xxcb_c00_message_typ;
  9   10   11  BEGIN
   dequeue_options.msgid := descr.msg_id;
 12   13     dequeue_options.consumer_name := descr.consumer_name;
 14     DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
 15                     dequeue_options => dequeue_options,
 16                     message_properties => message_properties,
 17                     payload => message,
 18                     msgid => message_handle);
 19     insert into xxcb_c00_aq_messages values
 20     ( 'Dequeued and processed "' || message.text || '"' );
 21     COMMIT;
 22  END;
 23  /

Procedure created.

SQL> begin
    dbms_aqadm.add_subscriber
    ( queue_name => 'apps.XXCB_C00_INBOUND_QUEUE',
      subscriber => sys.aq$_agent( 'RECIPIENT', null, null ) );
end;
/
  2    3    4    5    6
PL/SQL procedure successfully completed.

SQL> BEGIN
  2      dbms_aq.register
  3      ( sys.aq$_reg_info_list(
  4          sys.aq$_reg_info('apps.XXCB_C00_INBOUND_QUEUE:RECIPIENT',
  5                            DBMS_AQ.NAMESPACE_AQ,
  6                           'plsql://apps.xxcb_c00_get_aq',
  7                            HEXTORAW('FF')) ) ,
  8        1 );
  9  end;
 10  /

PL/SQL procedure successfully completed.

Send a test message directly to the inbound queue

SQL> begin xxcb_c00_enqueue_inbound_msg('Test in Inboud Queue ...'||TO_CHAR(sysdate,'HH24:MI:SS')); commit; end;
/
  2
PL/SQL procedure successfully completed.

SQL> select *
from xxcb_c00_aq_messages
  2    3
SQL> /

MSG
--------------------------------------------------------------------------------
Dequeued and processed "Test in Inboud Queue ...15:58:24"


So we know the dequeueing mechanism works OK

Now switch on Propagation

SQL> begin DBMS_AQADM.Schedule_Propagation(Queue_Name  => 'XXCB_C00_OUTBOUND_QUEUE',
  2                                        Destination => 'apps.LoopBack',
  3                                        Start_Time  => sysdate,
  4                                        Latency     => 0);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Send a message to the outbound queue

SQL> begin xxcb_c00_enqueue_outbound_msg('This should be propagated.'||TO_CHAR(sysdate,'HH24:MI:SS'),
'apps.XXCB_C00_INBOUND_QUEUE@apps.LoopBack'); commit; end;
/
  2    3
PL/SQL procedure successfully completed.

SQL> select *
from xxcb_c00_aq_messages
  2    3
SQL> /

MSG
--------------------------------------------------------------------------------
Dequeued and processed "Test in Inboud Queue ...15:58:24"

Message is not dequeued

But it has been propagated to the inbound queue

SQL> select user_data
  2  from xxcb_c00_inbound_qtab
  3
SQL> /

USER_DATA(SUBJECT, TEXT)
--------------------------------------------------------------------------------
XXCB_C00_MESSAGE_TYP('NORMAL MESSAGE', 'This should be propagated.15:58:57')


SQL> select TO_CHAR(sysdate,'HH24:MI:SS') from dual;

TO_CHAR(
--------
16:00:04

SQL> select *
from xxcb_c00_aq_messages
  2    3
SQL>
SQL> /

MSG
--------------------------------------------------------------------------------
Dequeued and processed "Test in Inboud Queue ...15:58:24"

Still not there

So switch off Propagation

SQL> begin DBMS_AQADM.UnSchedule_Propagation(Queue_Name  => 'XXCB_C00_OUTBOUND_QUEUE',
                                      Destination => 'apps.LoopBack');
end;
/
  2    3    4
PL/SQL procedure successfully completed.

SQL> select *
from xxcb_c00_aq_messages
  2    3
SQL> /

MSG
--------------------------------------------------------------------------------
Dequeued and processed "Test in Inboud Queue ...15:58:24"

Still not dequeued - so send another message directly to the inbound queue

SQL> begin xxcb_c00_enqueue_inbound_msg('Test in Inboud Queue ...'||TO_CHAR(sysdate,'HH24:MI:SS')); commit; end;
/
  2
PL/SQL procedure successfully completed.

SQL> select *
from xxcb_c00_aq_messages
  2    3
SQL> /

MSG
--------------------------------------------------------------------------------
Dequeued and processed "Test in Inboud Queue ...15:58:24"
Dequeued and processed "This should be propagated.15:58:57"
Dequeued and processed "Test in Inboud Queue ...16:01:00"

SQL>

And now it's dequeued.

This was in the alert log 

kwqjaq: Exception Occured
kwqjschupd: schedule not found, unschedule assumed
*** 2005-02-18 15:58:58.539
ORA-12012: error on auto execute of job 5354
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5642
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 6067
ORA-06512: at "SYS.DBMS_AQADM", line 895
ORA-06512: at line 1

 

It's OK - found the problem

Ian, February 18, 2005 - 11:17 am UTC

Tom

Found this on Metalink (eventually).

Problem statement:

AQ PL/SQL NOTIFICATION DOES NOT WORK WHEN JOB_QUEUE_PROCESSES<4

So I bumped it up from 2 to 4 and guess what - it works!

Cheers

Ian

problem With AQ

veeresh, February 24, 2005 - 12:08 am UTC

Hi Tom,
I have tried your example and that is working fine.
1. Payload can be RAW or object Type . Suppose I have created one type of varchar2(32000), In this case i could not able to create queue table.
2.If I am creating type as CLOB then Thru Interconnect I am not able to generate a xml file with help of AQ adapter.

How i can solve this issue?

Tom Kyte
February 24, 2005 - 5:35 am UTC

1) would be to use a clob

2) would be - i don't know, I'm not familar with interconnect myself.

Notification does not seem to work

A reader, March 11, 2005 - 6:33 pm UTC

Tom,

I took the sample code and created everything as per the code. I can see that Enqueue works and I see messages in the Queue_table. But, the notification to Dequeue process (notifyCB) does not seem to work.

We have Oracle 9.2.0.1.0.
job_queue_processes 4
aq_tm_processes 1.
Would you please let me know how do I start identifying the problem ? Thanks.


Tom Kyte
March 12, 2005 - 10:06 am UTC

if you set the aq_tm_processes higher?

Re Notification does not seem to work

A reader, March 14, 2005 - 8:11 am UTC

To get Notification working on 9.2.0.4.0 with Propagation we had to set job_queue_processes=4 and aq_tm_processes=5

We got this from a Metalink note (Bug).

Hope this helps.

Regards

Ian

Notification still does not work

A reader, March 15, 2005 - 12:05 pm UTC

Tom,

aq_tm_processes is set to 5 in our init.ora.
We are using SPFILE, as per our DBAs.
The notification still does not work. Any other pointers ?.



Tom Kyte
March 15, 2005 - 9:02 pm UTC

so, you say "in the init.ora", "we are using spfile"

those two do not go together.

show parameter aq_tm

shows what?

Notification - help

A reader, March 17, 2005 - 12:49 pm UTC

Tom, I was going to say we do NOT use SPFILE. Sorry.

There are a couple of more things we have tried.
We tried running your sampe code in another database instance running the same version of Oracle. It works just fine. Aq_tm_processes=6 and job_queue_processes=4.
In 'dev1' instance that has been giving us trouble,
e-mail notification works ( sample code from the Metalink test case (Note:225749.1)), but not the PL/SQL procedure notification. Both database instances have Oracle 9.2.0.1.0.
Would you please let us know what else can be done ?. Thanks.

Tom Kyte
March 17, 2005 - 2:01 pm UTC

please contact support if you are sure the parameters are set (show parameter should be used to confirm that the database was bounced after the changes to the init.ora)

AQ notification in 8.17

A reader, March 25, 2005 - 2:07 pm UTC

Tom,

My question is related with the original question. I made a copy of your "notifyCB" procedure in my 8.17 environment but when I compiled the procedure , it gave me "PLS-00201: identifier 'SYS.AQ$_REG_INFO' must be declared" error. When I asked my DBA to look into, he replied me back that this object does not exist in 8i but it exist in 10G.

I neeed to call a pl/sql procedure after a message is enqueued in a queue, how would I do it in 8.1.7 environment?

Thanks for your help.

Suhail

Tom Kyte
March 25, 2005 - 6:51 pm UTC

this was a new feature in 9i -- plsql based callbacks.


</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96587/qwnew.htm#969817 <code>

Might be helpful..

Jeff, March 25, 2005 - 8:53 pm UTC

Metalink note:

235397.1
AQ Notification

AQ notification to OID

Suhail, March 29, 2005 - 9:23 am UTC

Tom,

I need to update my OID entries if there is any chnages in the database tables. Some time LDAP server is not running so I am planning to use AQ to notify OID for any chnages. Currently I am using Database trigger to update entries of a node in OID realtime and it works OK. However, I would prefer to use AQ, could you give me some insight how to use AQ to update OID entries. I am using DBMS_LDAP package to update the entries attributes. I found some info about creating an Agent at </code> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10785/aq_opers.htm#sthref759 <code>
but since there is no example , I could not continue further.

Thanks



Tom Kyte
March 29, 2005 - 10:32 am UTC

You use AQ to queue messages.

You need to write the thing that dequeues a message and processes it. OID isn't expecting any messages from you, so you need to write a small piece of software to dequeue message, process message against OID.

AQ & OID

Suhail, March 29, 2005 - 10:55 am UTC

Thanks for your quick reply. Correct me if I am wrong, I can customize your notifyCB procedure to first dequeue and then within that procedure I will have my DBMS_LDAP code to populate OID's entries. What I donot understand is how would I register a Agent for OID just like you have shown a "reciepient" in your example. Could you give me some code about how to create an agent and register it for OID which is defined in the link </code> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10785/aq_opers.htm#sthref759 <code>

Tom Kyte
March 29, 2005 - 11:12 am UTC

I've never used that, not really sure what it is for myself.

But, you don't need to register an agent for OID, the recipient is your plsql routine, which in turn would do whatever it needs in OID using dbms_ldap.

AQ & OID

A reader, March 29, 2005 - 11:50 am UTC

Thanks Tom,

I will do as per your suggestion. I would appreciate if you can find internally about the usages of package DBMS_AQ.BIND_AGENT . I could not find any code , example in Metalink too.

Suhail

OID & AQ

Suhail, March 29, 2005 - 2:01 pm UTC

Thanks , your example works great. I can use AQ to notify to OID using PL/SQL. I have two more questions:

1) How would I do the other way around, ie if an entry changes in OID , I would like to notify to some other applications ( 3rd part portals , custom Oracle applications etc) about the changes. Would I be able to enque messages from OID to an AQ's table in this case?

2)Is there a way to look up enterprise-wide queuing information--queues, subscriptions, and events--from one location-ie Oracle Internet Directory? If answer is yes then how would I go for designing it?

Thanks

Tom Kyte
March 29, 2005 - 3:06 pm UTC

1) you would probably be using ldiff at that point (but you are getting way out of my realm of knowledge as far as managing ldap goes...)

2) not that I am aware of, but again -- not an expert on the directory stuff.

thanks

Suhail, March 29, 2005 - 4:08 pm UTC

Thanks for all your help. I am too not an expert in LDAP, trying to learn from OTN forums and your site and manuals. I think OID provisioning solutions might help me but I donot have that software as I am using OID 9i ( db version and not 9iAS version), </code> http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96574/odip_pro.htm <code>

So, in a nutshell, I have not a concrete answer but it looks like either I have to use LDIF ( as you suggested) or probably write some OID pluggins.

Thanks for all your help. When are you schedule to come to Albany, NY, would like to meet you again?

Thanks and another question

Ora_User, April 12, 2005 - 4:50 pm UTC

Tom,

We have some questions for you. We will appreciatte your help sooo much.

- 1 oracle server 9i SE that modifies a table
- 1 oracle server 9i SE that needs to know the mofication in the other server.

We have thinking to do that with queues. We have one producer and one remote consumer.

In the example that you show before, we see multiple_consumers = TRUE.

Question 1: Is it neccesary for this case?

We follow the example correctly, but when we do:

select t1.cnt, t2.cnt
from (select count(*) cnt from objmsgs80_qtab) t1,
(select count(*) cnt from objmsgs80_qtabx) t2;

... we have:

CNT CNT
---------- ----------
2 0

We do alter system set job_queue_processes=5; but ... don't work.

Question 2: Is there anything that we forgot???

If we think that work ...

Question 3: Which is the next step? ... User aqtest must dequeue from MSG_QUEUEX? ... nothing more?

Thank you so much for your help.





Tom Kyte
April 13, 2005 - 8:49 am UTC

what does the other server need to know for? could a simple read only materialized view (snapshot) be what you are looking for.

yes ... I know

A reader, April 13, 2005 - 5:46 pm UTC

Hi Tom.

We need to know when in the first server there is a insert or an update of some fields (1 table) to update other table in the second server.

It's a requirement of the "client" to use advanced queue. We must do that.

Thank you so much.

Tom Kyte
April 13, 2005 - 7:30 pm UTC

problem is, AQ isn't for you.
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96531/ch5_edit.htm#89162 <code>

you have SE.

You might be able to use dbms_job if this is low volumes of data. You would create a "msg" table:

create table msg ( JOBID number primary key, <data that you would have queued> );


and a trigger:

create trigger after update or whatever on that_table for each row
declare
l_job number;
begin
dbms_job.submit( l_job, 'background_job(JOB);' );
insert into msg( job, ... ) values ( l_job, ..... );
end;
/


the background job stored procedure would query out the msg, process it and delete it.


You would not necessarily have the jobs run "in commit order", but they would all eventually run.

*test test test*, test to destruction, if you think you are going to jam the job queues full of stuff, test it.

Multiple calls to DBMS_AQ within *one* PL/SQL callback?

max, April 17, 2005 - 4:32 am UTC

hi tom,

we 'd like to use PL/SQL notification in conjunction with transactional queues (message grouping).

thus the PL/SQL routine registered to dequeue incoming messages should process at least *all* messages of a transaction at once (if not all messages for all transactions) instead of just one message per callback. so DBMS_AQ.Dequeue is called multiple times by that routine within two nested loops - the outer for transactions, the inner for messages of transaction.

it seems to work very well. the notified procedure picks up all the messages (transaction by transaction), inserts the data into the destination table and commits before exit.

do you see anything wrong here - should the callback routine dequeue only *one* message at a time?

Tom Kyte
April 17, 2005 - 8:42 am UTC

As long as the dbms_aq calls you are making do not have implicit commits (documentation should say), it would be a single transaction, yes.

Is PL/SQL notification a good choice at all?

max, April 18, 2005 - 5:40 pm UTC

we 've added some logging to the PL/SQL routine registered for notification. the logged content of v$session and dba_jobs as well as the value retrieved from a sequence showed that a new session and several DBMS-jobs got created for each invocation of that registered PL/SQL routine.

in our test case 1 session enqueued 2 messages with priority 2 within the first transaction. then the session got suspended for 15 seconds (DBMS_Lock.Sleep). after that the session enqueued another 3 messages with priority 1 with a second transaction.

the 2 priority-2-messages got dequeued first by the first call to the registered PL/SQL routine, and that call created 2 DBMS-jobs. the second invocation processed the 3 remaining priority-1-messages and resulted in 3 additional DBMS-jobs. each job has been run only once (DBA_Jobs.Interval => NULL). DBA_Jobs.What contained sys.dbms_aqadm_sys.register_driver()), and for some of those generated jobs DBA_Jobs.This_Date and DBA_Jobs.This_Sec remained empty (but why?) ...

is our observation valid that each message is processed by a new single-run-DBMS-job and that each invocation of the registered PL/SQL routine results in creation/termination of a new session?

should one avoid PL/SQL notification for that reason?

Tom Kyte
April 18, 2005 - 6:55 pm UTC

the job queues are quite scalable (they can go up to 1000 at a time), something needs to run them and the job queues are designed to do that.

short of writing your own application to dequeue and process the message that is, you can either write the code or let the database do it.

Measure it and see if it is "better than good enough" for you, if not -- approach two, if so, great.

Did i get you right?

max, April 20, 2005 - 2:25 am UTC

it seems as if you are not convinced that the solution provided "out of the box" is (in most cases) the best (e.g. most performant) one can get here, right?

we just thought that the PL/SQL procedure registered for notification would act more or less like a "interrupt routine": loaded once, run many times - invoked in the event of an incoming message.

in fact i'm facing some serious discussions with some colleagues of mine who have already chosen the DIY way and compared both approaches. although i'm not sure yet, what/how they 've implemented and measured, it 's clear enough that creating/terminating sessions and jobs all the time for message processing (which is what i 've observed) would take a considerable additional amount of time -- and can be considered overhead as long as there are no pros to do so. do you know any?

another thing is that there are user defined object types used for persistency within the queue tables, which is another thing you don't recommend ...

could you give some advice on how to tune a AQ based solution: what to look for, take into consideration; features/options (not) to use?

Tom Kyte
April 20, 2005 - 6:55 am UTC

I'm convinced that in life in general we trade off "ease of use" for "some performance implication" yes.


the pros are -- no left over junk from a previous execution, no leaks from previous executions, least amount of code.

you have to test -- as with all things. look at something like streams, it uses in memory (SGA) queues and if the instance fails, it repopulates them from the redo log. why? because it wants highest volume so they wrote alot of extra code to make it so they could use non-persistent queues, they were will to live with "upon restart, repopulate stuff we didn't do yet". More code, faster.



Thanks for your help

Ora_User, April 20, 2005 - 3:18 pm UTC

Muchas gracias por la ayuda. (Thank you for your help).

and how about that?

max, April 21, 2005 - 1:49 am UTC

do you think it could be worth trying to use NON-persistent queues instead to gain some performance boost?
since the notified PL/SQL procedure progates incoming messages to tables anyway there 's no need to store them within queue tables. but on the other hand: if messages need to be "buffered" in case dequeuing is slower than enqueuing ...

btw: how can one inspect the content of those non-persistent queues?



Tom Kyte
April 21, 2005 - 6:17 am UTC

benchmark it.

if only i could ...

max, April 22, 2005 - 9:29 am UTC

taken from documentation for DBMS_AQADM.CREATE_NP_QUEUE:

<quote>
You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the OCI notification mechanism. You cannot invoke the listen call on a nonpersistent queue.
</quote>

i didn't even know that i never been "in touch" with OCI.
can one use that from within PL/SQL at all?

Tom Kyte
April 22, 2005 - 10:45 am UTC

i do not believe so, the oci program would call the plsql routine.

If you don't want to use the builtin "call my plsql", you need to write a client that "calls your plsql"

how about DBMS_AQ.Listen instead?

max, April 23, 2005 - 4:28 pm UTC

after some further reading and testing i'm really not sure anymore whether PL/SQL notification is a good choice at all (or one should take DBMS_AQ.Listen into consideration).

taken from metalink note 225810.1

<quote>
When a callback is detected a message is placed in SYS.AQ_SRVNTFN_TABLE_Q waiting to be dequeued, and a job is submitted through the Oracle job queue to execute the callback function. Each job is submitted in the order that the callback was fired, i.e., the next date for the job is set to be the time that the callback was raised for the message.

When messages are enqueued within the same second, i.e., the enqueue time is the same for both messages, a step number is implemented to ensure that the order of the messages is maintained on dequeue.

However, when the jobs for the callback are submitted on the job queue, the step number for those messages that were enqueued in the same second is effectively lost. This is further exacerbated when messages are enqueued in the same transaction.

Therefore, when a job queue process is allocated a job from the job queue list, it may run the callback functions for messages that were enqueued in the same second, but will not maintain FIFO.

However, there is a work around that can be implemented in the callback function itself; it is based on following premises:

1. A callback function processes all current messages on a queue, not just the message that the callback function was invoked for, i.e., dequeue the messages, and not use the information that is passed into the callback function.

2. A callback function is a point of serialisation, i.e., the process body of the callback function is mutually exclusive for all other invocations of that callback function.

3. No other process is to dequeue messages from the queue.

Point 1 ensures that the messages are dequeued in the correct order. Point 2 ensures that no other callback function invocation can dequeue messages from a queue whilst the current invocation is dequeuing messages.
</quote>

the sample given in that document uses locks with "MaxWait" to prevent concurrent sessions/jobs to dequeue. jobs that are unable to establish the requested lock get blocked waiting for the blocking lock to be released ... which in turn wouldn't occur before all messages in the queue are processed. one could decide to immediately terminate the callback routine in case it was unable to establish the requested lock in order to get rid of those unwanted sessions/jobs. by doing this in a test case (with message grouping, queuing priority) i 've received the following output from the callback routine registered for PL/SQL notification:

MsgID TxnID DMLTime LogTxt SID Serial#
----- ----- --------------- --------------------------------- --- -------
1 16:35:09,336000 begin processing 22 216
2 16:35:09,346000 begin processing 28 276
3 16:35:09,346000 begin processing 17 328
4 16:35:09,356000 lock request 28 276
5 1 16:35:09,356000 begin transaction 28 276
6 16:35:09,366000 lock request 17 328
7 16:35:09,366000 lock request 22 216
8 1 16:35:09,366000 begin message (Prio1Msg1) 28 276
9 1 16:35:09,376000 end message (Prio1Msg1) 28 276
10 16:35:10,367000 lock request timeout 17 328
11 16:35:10,367000 end processing 17 328
12 16:35:10,367000 begin processing 17 328
13 16:35:10,367000 lock request 17 328
14 16:35:10,377000 lock request timeout 22 216
15 16:35:10,377000 end processing 22 216
16 16:35:10,377000 begin processing 22 216
17 16:35:10,377000 lock request 22 216
18 1 16:35:10,387000 begin message (Prio1Msg2) 28 276
19 1 16:35:10,387000 end message (Prio1Msg2) 28 276
20 16:35:11,379000 lock request timeout 22 216
21 16:35:11,379000 end processing 22 216
22 16:35:11,389000 lock request timeout 17 328
23 16:35:11,389000 end processing 17 328
24 1 16:35:11,399000 end transaction 28 276
25 2 16:35:11,399000 begin transaction 28 276
26 2 16:35:11,399000 begin message (Prio2Msg1) 28 276
27 2 16:35:11,399000 end message (Prio2Msg1) 28 276
28 2 16:35:12,400000 begin message (Prio2Msg2) 28 276
29 2 16:35:12,410000 end message (Prio2Msg2) 28 276
30 2 16:35:13,411000 begin message (Prio2Msg3) 28 276
31 2 16:35:13,411000 end message (Prio2Msg3) 28 276
32 2 16:35:14,413000 end transaction 28 276
33 3 16:35:14,413000 begin transaction 28 276
34 3 16:35:14,413000 end processing 28 276

the message processing looks OK except ...

from my current point of view those NOP-sessions/-jobs (submitted by ORACLE "on behalf" of registered PL/SQL callback) are a waste of resources.

is there any way to avoid such new sessions/jobs until the processing callback has been finished?


Tom Kyte
April 23, 2005 - 5:04 pm UTC

Like I've said -- the plsql callback implementation the plsql callback implementation.

Can you do it more efficiently writing your own code? Absolutely.

But that is probably true of most things.

Yes, if I had a high volume system -- where scaling was mandatory -- i would most likely choose to not do this.

If I had to process a couple 10's or 100's of messages an hour, this would be something to give serious consideration to.

max, May 10, 2005 - 1:14 am UTC

by tracing ORACLE's DBMS_AQ.Dequeue we 've observed that message retrieval is split into two phases: the 1st step is to acquire the messages to be processed from the queue table. the 2nd step is to read out the message payload (via rowids).
our do-it-yourself-dequeue (with similar object type payload column) *combined* both steps and seemed to be slower for that reason. in our case we were able to gain some performance boost by just splitting the "read message" the same way since this resulted into an index range scan for step one as opposed to an additional table access by index rowid to retrieve the payload during the "locate message" phase.

do we really just benefit from that change of the access path, or is there any other reason why ORACLE's implementation divided the "read message out" into those abovementioned two steps?

Tom Kyte
May 10, 2005 - 8:07 am UTC

I don't know, never went into the "why did you do that".



And it 's getting even more confusing ...

max, May 10, 2005 - 9:45 am UTC

please take a closer look at the following tkprof'ed trace:

TKPROF: Release 9.2.0.6.0 - Production on Di Mai 10 13:32:42 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: C:\Programme\Oracle\admin\ORCL9i\udump\orcl9i_ora_4076_dqapi.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

alter session set events '10046 trace name context forever, level 12'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 292

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

select metadata
from
kopm$ where name='DB_FDO'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID KOPM$
1 INDEX UNIQUE SCAN I_KOPM1 (object id 351)

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

declare
EXC_NO_MSG exception ;
pragma exception_init( EXC_NO_MSG, -25228 ) ;
v_recDeQOpts DBMS_AQ.Dequeue_Options_t ;
v_recMsgProps DBMS_AQ.Message_Properties_t ;
v_rawMsgHndl raw( 16 ) ;
v_objMsg MyObjType ;
v_objTmp MyObjType ;
begin
v_recDeQOpts.Wait := 1 ;
DBMS_AQ.Dequeue( 'MyQueue', v_recDeQOpts, v_recMsgProps, v_objMsg, v_rawMsgHndl ) ;
v_objTmp := v_objMsg ;
commit ;
DBMS_AQ.Dequeue( 'MyQueue', v_recDeQOpts, v_recMsgProps, v_objMsg, v_rawMsgHndl ) ;
v_objTmp := v_objMsg ;
commit ;
DBMS_AQ.Dequeue( 'MyQueue', v_recDeQOpts, v_recMsgProps, v_objMsg, v_rawMsgHndl ) ;
v_objTmp := v_objMsg ;
commit ;
exception
when EXC_NO_MSG then NULL ;
end ;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.07 0 54 0 0
Execute 1 0.02 0.11 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.19 0 54 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 292

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, tab.priority,
tab.delay, tab.expiration, tab.retry_count, tab.exception_qschema,
tab.exception_queue, tab.chain_no, tab.local_order_no, tab.enq_time,
tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no,
tab.sender_name, tab.sender_address, tab.sender_protocol,
tab.dequeue_msgid
from
AQ.MYQTAB tab where q_name = :1 and state = :2 order by q_name, state,
enq_time, step_no, chain_no, local_order_no for update skip locked


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 3 0.01 0.07 30 136 6 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 0.09 30 136 6 3

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.02 0.02
db file scattered read 6 0.00 0.03
********************************************************************************

select user_data
from
AQ.MYQTAB where rowid = :1 and (enq_tid = :2 or enq_tid is NULL) and
step_no = :3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.01 0.06 0 0 0 0
Fetch 3 0.00 0.00 0 3 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.06 0 3 0 3

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

delete from AQ.MYQTAB
where
rowid = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.01 0 3 15 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 3 15 3

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

select user#
from
sys.user$ where name = 'OUTLN'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)

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

COMMIT


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.02 0 0 0 0
Execute 3 0.01 0.00 0 0 3 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.02 0.02 0 0 3 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 292 (recursive depth: 1)
********************************************************************************

alter session set SQL_TRACE = FALSE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 292



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.07 0 54 0 0
Execute 3 0.02 0.11 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 0.19 0 54 0 1

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
log file sync 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.01 0.02 0 0 0 0
Execute 12 0.03 0.10 0 3 18 3
Fetch 8 0.01 0.07 30 143 6 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 0.05 0.20 30 146 24 11

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.02 0.02
db file scattered read 6 0.00 0.03

6 user SQL statements in session.
5 internal SQL statements in session.
11 SQL statements in session.
********************************************************************************
Trace file: C:\Programme\Oracle\admin\ORCL9i\udump\orcl9i_ora_4076_dqapi.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
6 user SQL statements in trace file.
5 internal SQL statements in trace file.
11 SQL statements in trace file.
9 unique SQL statements in trace file.
191 lines in trace file.

the test case consists of an anonymous PL/SQL block which calls DBMS_AQ.DEQUEUE three times and each call is followed by a commit. as one can see from the queue table fetches three messages got dequeued during the test run.

but the SELECT against the queue table for retrieving the messages to be processed (without extracting the payload) is *executed* only *once* (per PL/SQL block) instead of *once per call* to DBMS_AQ.DEQUEUE. furthermore it seems as if the that cursor (for update) gets remains open für multiple subsequent fetches across (intermediate) commits ...

i don't understand what 's going on here.
but i'm quite sure YOU do ;o) do you?

Tom Kyte
May 10, 2005 - 10:37 am UTC

so they opened a cursor to get the messages to process once, and each time you asked for a message, they just fetched the "next row".



psuedo code:


if ( cursor is not open )
then
open it
end if;

fetch a row
if (data found)
then
get payload
delete message
return
else
close cursor
return no more
end if



max, May 10, 2005 - 2:01 pm UTC

yes, sure, that 's obvious.

but how did "they" avoid the fetch-across-commit (autonomous transaction)?

and: one can observe the same exec once, fetch many with *multiple* anonymous PL/SQL blocks. do they "share" that dequeue cursor then?

Tom Kyte
May 10, 2005 - 2:31 pm UTC

who said they are even using your transaction ;)


but please - do not over analyze the internals, they are what they are. they can, will, do and have changed.

please - believe, it 's really *not* just for fun ...

max, May 10, 2005 - 3:04 pm UTC

don't we have to understand some of those details in order to accomplish your "you can either write the code" (see above) in case we opt for a do-it-yourself queuing to avoid some drawbacks of the solution provided out of the box?

as it turned out that 1/n exec/fetch thing seems to be the main difference in terms of performance -- and therefore it 's the most important "topic" for us.

we 'd really appreciate it very, very, ..., very much if you could clarify and help on this.

and to be honest: we can't even imagine any other way than an autonomous transaction to avoid the fetch-across-commit-issue in the above case ...

Tom Kyte
May 10, 2005 - 4:23 pm UTC

No, you benchmark and evaluate. AQ is a bit of a black box.

Inside the server, they have many things available to them, that you do not. They write it after all.

You would, in your application, have two sessions (only need one connection) to mimick what they appear to be doing (i say appear because I'd need to read the actual trace file to be sure they are doing what I hypothesize they are).




max, May 11, 2005 - 1:29 am UTC

do i get that right: the consuming session interacts with another session responsible for dequeuing the messages within a transaction which in turn can be considered "autonomous" from the receiving session's point of view and therefore doesn't hit the fetch-across-commit?

how could one prove that two-sessions-solution (thought of it too) from the trace file's content? what would you suggest after all?

Tom Kyte
May 11, 2005 - 7:26 am UTC

I'm GUESSING that is what they are doing. I'm not really excessively interested in reverse engineer AQ to this degree.

A single client application can have many sessions going at the same time on a single connection, yes. OCI applications can, Java can, plsql cannot (no "logging in" in plsql)

Look -- my perspective is either

a) you use AQ
b) you use the documentation and build it yourself, not using "skipped locked" and other things internals can use.

thank you for your patience ...

max, May 11, 2005 - 3:38 pm UTC

but since the trace file contains *both* dequeuing statements (the one for "get messages" as well as the other for "get message's payload") there 's really no evidence for such a second session involved, right? from our point of view it looks (more or less) as if "they" *can* fetch across commit, doesn't it?

meanwhile we 've chosen the do-it-yourself-approach (simple version, no cross-session-communication). we 've to use SELECT FOR UPDATE (like ORACLE does) and therefore have to reopen the "get messages" cursor over and over again, after each transaction.

now we 're experiencing some performance issues during message processing: the more messages we DELETE from our self-made queue table (object type payload) the less SELECT performance we get. finally rebuilding the (b*tree) index (transaction id feed by sequence plus user provided message id) seem to increase the performance ...

but does that really solve the problem? would you take reverse key indexes into consideration here?

Tom Kyte
May 11, 2005 - 7:28 pm UTC

if they are fetching a for update cursor over a commit (and the trace file shows us the sequence) then there must be two transaction threads going on. whether there were two sessions of not -- maybe, maybe not (they OWN the server, they can do whatever they feel like).

YOU would need two sessions in order to accomplish the same.

max, May 12, 2005 - 3:54 am UTC

thanks a lot for confirming that two sessions, autonomous transaction (or whatever -- server secret ;o)

and do you have any suggestions on how to attack that "read performance slowes down after delete over time" issue during dequeuing phase? is an index rebuild an appropriate solution for that, or can one avoid the performance degredation by *fixing* a design problem?

Tom Kyte
May 12, 2005 - 7:45 am UTC

tell me why the read performance slows down after delete over time first.

give simple use case. If this is a sweeping index, it could be a candidate for a rebuild.

but you seem to be writing alot of code for something that is builtin....

alt, May 12, 2005 - 6:26 am UTC

What is denormailizing the table and database?
and what is STARTUP FORCE command in oracle 9i?

SAL, May 12, 2005 - 6:33 am UTC

IF SUPPOSE I AM IMPORTING ONE USER INTO MY DATABASE IN ORACLE 9I THAT USER ALREADY EXISTING IN MY DATABASE,THEN IMP WILL FAIL OR THAT DATA WILL UPDATE INTO DATABASE AND
THE TABLE I AM IMPORTING IS ALREADY EXISTING IN MY SCHEMA IN THIS CASE DATA OF THE TABLE WILL OVERWRITE THE TABLE OR IMP WILL FAIL?


Tom Kyte
May 12, 2005 - 8:04 am UTC

(mixed case is tons easier to read.... and it doesn't make you sound like you are shouting :)


if you are doing a user level import AND the user doesn't already exist, that will fail.

if you are doing a FULL import and the user exists, it depends on the setting of ignore.


if the schema already exists, you have to use ignore=y and import will INSERT (not update) into the tables.

as for slowing down issue ...

max, May 12, 2005 - 9:59 am UTC

well, if only i could tell you that i wouldn't have asked that question at all.

we 've just observed that described behaviour during a test running for many hours within our production-like system. it started with a mass insert ("enqueue") followed by multiple subsequent selects and deletes ("dequeue"). proper b*tree indexes and analyzed statistics were in place before "dequeuing" began. performance for selecting remaining entries went down as more and more entries got deleted ... unless the index got rebuilt ...

now apart from that the problems are: we don't have a clue how this has happened. we don't have the space to "double" that system. we don't have the permission to rerun the test (remember: the issue is considered to be "solved" by index-rebuild). and since local hardware ressources are not comparable to the test system we 're not sure if we would be able to reproduce that effect at all.

that 's why i 've tried to ASK TOM first whether he could even imagine any correlation between those facts provided :o(

that would be an invaluable help for constructing a test case too.

Tom Kyte
May 12, 2005 - 1:06 pm UTC

measure the io's you have when you started (run a couple of samples with sql_trace=true)

measure later

if you see an increase in LIOs on your range scan, similar to this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2913600659112#24081704877485 <code>

then it was a sweeper. commonly would happen to an index on a sequence where you are going after the oldest sequence - and then you delete it, the index sweeps from left to right.

you can measure the io
rebuild
measure the io again
wait
take some more samples over time

to determine how long it might take to break down and affect you. (or find it it does nothing)

fanning out messages in AQ

Ray, May 20, 2005 - 7:47 am UTC

Hi Tom,

Can you give a brief few liner example on how to set up in which recipients are in turn other queues in seperate databases?

Thanks in advance for your valuable time.

Regards
Ray

But how to serialize?

Basil, May 20, 2005 - 4:33 pm UTC

I used your sample as a model for my own application. In my case, the queue is defined as multi-consumer. As the customers work with my application, messages are enqueued. I would like messages from multiple customers to be able to be processed at the same time. However, each customer should only have ONE of their messages handled at a given instant, as the messages for a given customer should be serialized.

The sample you give does not have such a serialization requirement.

When the callback procedure is invoked, I'm stumped as to how to recognize whether another callback is processing a message from the same customer. I've tried submitting a job, which I then look for in the callback. However, that creates a race condition if the second message arrives too soon after the first -- both end up getting processed. (And deadlocks occur as they attempt to operate on the same data.)

Thoughts? I'm using 9.2.0.4 (and .6 in places).

Tom Kyte
May 21, 2005 - 8:27 am UTC

you could use dbms-lock to create a lock "named" after the customer. search this site for dbms_lock

How is the payload in the callback usable?

Basil, May 25, 2005 - 10:04 am UTC

The dbms_lock tip is helpful. One other thing in this that I don't understand is how the payload parameter would be used,
as shown in your proc definition:
create or replace procedure notifyCB( context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor, payload raw,
payloadl number)

In your example you do a dequeue to get the payload. However, if the procedure simply wants to 'peek' at the payload in order to dispatch it to something else (which would do the actual dequeue), is it possible to cast the payload parameter to a variable of the proper payload object type? If so, how?

Tom Kyte
May 25, 2005 - 12:57 pm UTC

the payload is delivered ONLY with non-persistent queues, you have to dequeue to explicitly dequeue the message to peek it.

unable to grant queue privileges across databases

Nilanjan Ray, June 13, 2005 - 4:06 am UTC

I am trying to set up AQ across two databases. But I encountered the following error while trying to grant enqueue privilege on the target queue to the source database schema. Here's the script I am using

create or replace type myobj as object
( msh varchar2(100),
cid number(3),
p_id number(5)
);
/

-- Both database has the same usename st'

-- target
create database link "src" connect to st identified by <pwd> using <..>;

--source
create database link "tgt" connect to st identified by <pwd> using <..>;

-- At both database 
grant aq_administrator_role to st;
grant execute on dbms_aq to st;
grant execute on dbms_aqadm to st;

--At both database
begin
  dbms_aqadm.grant_system_privilege('enqueue_any','st',false);
  dbms_aqadm.grant_system_privilege('dequeue_any','st',false);
end;
/

-- At source database
begin
    dbms_aqadm.create_queue_table (
        queue_table => 'source_qt',
        queue_payload_type=> 'myobj',
        multiple_consumers=>true,
        compatible => '9.2');

    dbms_aqadm.create_queue (
        queue_name =>'source_q',
        queue_table=>'source_qt',
        max_retries=>'5',
        retry_delay=>'0');

    /* Start the Queue */
    dbms_aqadm.start_queue('source_q',true,true);        

end;
/

--At Target Database
begin

    dbms_aqadm.create_queue_table (
        queue_table => 'target_qtx',
        queue_payload_type=> 'myobj',
        multiple_consumers=>true,
        compatible => '9.2');

    dbms_aqadm.create_queue (
        queue_name =>'target_qx',
        queue_table=>'target_qtx',
        max_retries=>'5',
        retry_delay=>'0');

    /* Start the Queue */
    dbms_aqadm.start_queue('target_qx',true,true);

end;
/

-- At source database
create or replace procedure enqueue_msg(p_msg in myobj,
                                        p_add in varchar2 default null)
as

  enqueue_options dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  msg_id RAW(16);
  message myobj;
  recipients DBMS_AQ.aq$_recipient_list_t;
  
BEGIN

  recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
  message_properties.recipient_list := recipients;
  --message_properties.exception_queue := 'target_qex';
  
  dbms_aq.enqueue(queue_name => 'source_q',
                  enqueue_options => enqueue_options,
                  message_properties => message_properties,
                  payload => p_msg,
                  msgid => msg_id);
end;
/

--at target database to grant enqueue privilege to st user at the source database
exec dbms_aqadm.grant_queue_privilege('enqueue','target_qx','st@src');

The last statement gives the following error.

ERROR at line 1:
ORA-01917: user or role 'ST@src' does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 0
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3801
ORA-06512: at "SYS.DBMS_AQADM", line 441
ORA-06512: at line 1

I verified that the dabase link was working perfectly.

SQL>select * from dual@src;

D
-
X

In fact I was using Seans's previous example in this post across differnet databases (the example was using the same database for both the source and target queues). My understanding is that in order to use different databases the source schema must be granted the 'enqueue' privilege on the target queue residing on another database.

Thanks in advance for your valuable time. If possible please treat this as urgent

Regards
Ray 

Tom Kyte
June 13, 2005 - 11:01 am UTC

you grant abilities to USERS, not to "users at a database".

st@src doesn't make sense to use here. when st@src connects to this database they are a USER in this database. that is the user you grant to

as for "How is the payload in the callback usable?"

A reader, June 13, 2005 - 4:03 pm UTC

hi basil, hi tom,

during some "research" to find out what an *anonymous* subscription would be (very few documentation about that -- even here) i 've found out that this is another case where the payload gets delivered to the notified routine:

/*
drop user AQ cascade ;
create user AQ identified by AQ ;
grant connect, resource to AQ ;
grant execute on DBMS_AQ to AQ with grant option ;
grant execute on UTL_Raw to AQ with grant option ;
*/

connect AQ/AQ

create table AQ.MyTab( MyMsg varchar2( 100 ) ) ;

-- PL/SQL callback procedure
create or replace procedure AQ.LogMsg( Context raw,
RegInfo SYS.AQ$_Reg_Info,
Descr SYS.AQ$_Descriptor,
Payload raw,
Payloadl number )
is
begin
insert into AQ.MyTab( MyMsg )
values( substr( UTL_Raw.Cast_To_Varchar2( PayLoad ), 1, 100 ) ) ;
commit ;
end LogMsg ;
/

-- registering PL/SQL callback routine and posting to anonymous subscription
begin
DBMS_AQ.Register(
SYS.AQ$_Reg_Info_List(
SYS.AQ$_Reg_Info(
Name => 'MySubscription',
Namespace => DBMS_AQ.NAMESPACE_ANONYMOUS,
Callback => 'plsql://AQ.LogMsg',
Context => UTL_Raw.Cast_To_Raw( 'MyCtx' ) ) ),
1 ) ;

DBMS_AQ.Post(
SYS.AQ$_Post_Info_List(
SYS.AQ$_Post_Info(
Name => 'MySubscription',
Namespace => DBMS_AQ.NAMESPACE_ANONYMOUS,
Payload => UTL_Raw.Cast_To_Raw( 'MyPayload' ) ) ),
1 ) ;
end ;
/

select * from AQ.MyTab ;

as one can see there 's no queue involved at all here ;o)

Advanced Queing & PL/SQL Notification

A Reader, July 13, 2005 - 10:29 am UTC

Hi Tom

I managed to implement PL/SQL notification using the example highlighted in this thread. There is one AQ behaviour which I would like to clarify.

(1) I have two queues Q1 and Q2 ( single consumer ).
(2) I have registered procedure P1 with Queue Q1 and procedure P2 with Queue Q2 using DBMS_AQ.REGISTER. These procedures simply dequeue messages from the queues.
(3) Then I enqueued messages onto these queues concurrently using two different sessions.
(4) I expected procedures P1 & P2 to dequeue messages simultaneously from Queues Q1 and Q2. But Queue Q1 was dequeued first followed by Queue Q2.

Eventhough the notification works for both the queues, only one works at a time. Is this an expected AQ behaviour? Your feed back will be greatly appreciated.

Thanks


Tom Kyte
July 13, 2005 - 12:55 pm UTC

what are your related init.ora parameters set to? aq*

Advanced Queuing & PL/SQL notification

A Reader, July 14, 2005 - 4:57 am UTC

Hi Tom

AQ related parameters that I know, have been set to the values below:

aq_tm_processes = 10
job_queue_processes = 100

Is there any other parameter I need to look at?

Thanks


Tom Kyte
July 14, 2005 - 10:33 am UTC

so if you make your dequeue procedure take "a long time" using dbms_lock sleep or something, do they still go sequentially.

Persistent Queue

Ash, July 27, 2005 - 1:09 pm UTC

Hi Tom

I have tried the first example in this thread . Its all working fine but I am loosing message when I restart the database during the deque eprocess. Is this example using non-persistent queue. If so how to make it to Persistenet queue.

Rgds
Anil

Tom Kyte
July 27, 2005 - 2:44 pm UTC

you should not "lose" the message if you shutdown during the dequeue (the commit would be needed to make the message go away)

tell me, how do you know you are shutting down during the dequeue and not after the commit of the dequeue?

Async Transaction

Ash, July 28, 2005 - 2:23 am UTC

Hi Tom

The steps that I followed


I ran a plsql script to insert 1000 messages in to the queue
then Committed .
I did select count(*) from the message table
I could see number of records incrimenting

I did shutdown abort

Restarted the database

Did again select count(*) from message table

I see only 962 records.

I expect 1000 records. Unless until I insert the record into the message table after deque I should not release the message from I queue..

I presume this programme dequeue one message from the queue and insert the record into the table and then commit.

I am using 10g R1 10.1.0.3
Waiting for your response.

Rgds
Anil



Tom Kyte
July 28, 2005 - 8:51 am UTC

did 38 of them execute? In the time between you committed and (counted) shutdown abort - did 38 of them process.



Asych Trx

Ash, July 29, 2005 - 2:36 am UTC

Hi

I am slightly lost. Here is the test I did

I fired the following PL/SQL block

begin
for i in 1..1000
loop
enqueue_msg( 'Hi '||i);
end loop;
commit;
end;

My expectation is once the above code executed succesfully , I must not loose the data, wheather database shutdown after that, I think that is persistant queue.


In this case 1000 rows must get inserted into the table ,irrespective of a DB restart, by my dequque process. If db restarted the remaining messages in the queue will be dequeud and inserted in to the table once DB is back


Would you please tell me , does the sample code above from You has a chance to loose message?.

Rgds
Anil

Tom Kyte
July 29, 2005 - 8:34 am UTC

each message was an independent message. It is sounding to me like

a) you put 1,000 in
b) you committed
c) we immediately started processing them, did some
d) you shutdown abort.


So, can you tell me that 38 of them were not dequeued and processed??????

You do understand that the 1000 will not be dequeued as a SINGLE THING, but as 1,000 separate distinct "things" right?

Asynch Trx

Ash, July 30, 2005 - 1:20 pm UTC

Sorry Tom,

I didn't look at the queue table. Remaining messages where resides in the queue. I thought ones the database is up the dequeue process starts automatically and dequeue the remaining messages.

Now, How can I dequeue the remaining messages.

Rgds
Anil

Tom Kyte
July 30, 2005 - 2:02 pm UTC

it should "just resume" unless you had altered the system to turn on the aq processes and such before you aborted the instance.

Async trx

Ash, July 30, 2005 - 2:40 pm UTC

Hi Tom 

I did the test again see the result


after the shutdown abort and restart 

SYS@NGPL1-SQL> show parameters aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     3
SYS@NGPL1-SQL> show parameters job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
SYS@NGPL1-SQL> conn aq/aq
Connected.
AQ@NGPL1-SQL> select count(*) from objmsgs80_qtab;

  COUNT(*)
----------
       478

AQ@NGPL1-SQL>  select count(*) from message_table;

  COUNT(*)
----------
       522


I didn't do any alter system . 

Now how do I restart the dequeue process 

Tom Kyte
July 30, 2005 - 3:40 pm UTC

anything useful in your alert log.

Asynch Tx

Ash, July 30, 2005 - 11:57 pm UTC

Hi Tom

I have already checked the alert file. Nothing specific in that. It shows nornal instance recovery afater an abort.

Is this a bug (Contact Oracle..)

or is there a way to manually kick the deque eprocess.

Rgds
Anil

Tom Kyte
July 31, 2005 - 9:07 am UTC

I'll refer you to support, if it was running before you shutdown and you didn't "configure it to run just for the life of that instance via alter system commands", it should resume normally after a restart.

how about shutting off AQ?

neil, August 23, 2005 - 11:04 am UTC

I enabled AQ for a proof-of-concept. aq_tm_processes was at 1.

A recent statspack report shows lots of calls to AQ tables. Since I'm not using AQ any more, I'd like to disable it - Is it just a matter of setting aq_tm_processes=0?

Tom Kyte
August 24, 2005 - 4:26 am UTC

that'll stop the background process that is doing the query, yes.

Unique queue

Andriy terletskyy, August 25, 2005 - 2:35 am UTC

Hi Tom,
can You please explain how to make a unique message queue.

I need some queue, where I enqueue all messages and dequeue only unique.

Regards
Andriy

Tom Kyte
August 25, 2005 - 3:35 am UTC

define "unique"

to me, every message is by definition UNIQUE.

Unique Message Queue

Andriy Terletskyy, August 25, 2005 - 5:17 am UTC

Hi,

I mean the messages base of the same object type instance.

I have the queue with more messages of same object type value, and i'll dequeue only one message for all same object type values.

Regards
Andriy


Tom Kyte
August 25, 2005 - 8:29 am UTC

No, there is really no such facility or concept - they are all "unique messages" as far as we are concerned.

Bulk enqueue notifications

Michaela, December 08, 2005 - 7:44 am UTC

Hi Tom,

I'm coming to the conclusion that array enqueue functionality has an uncomfortable relationship with PL/SQL notification.

A bulk enqueue results in one notification for each record in the array, so it's not possible to write a bulk dequeue. Atleast, if you do then you need to be prepared to handle notifications on MSGID's that you've already consumed.

Do you know if there is a way of triggering a bulk enqueue notification, or otherwise have any words of design wisdom around this area?

Java notification

R, February 24, 2006 - 12:57 pm UTC

Hi Tom,

standard approach to speed up performance in the Java world when dealing with databases is to cache data in the Java middle tier. Of course, the complication comes when the data you've been caching changes in the database and you need get the fresh data from the db.

So here's my example. I have various tables of reference data, lets say one of them is a list of countries that appears on countless pages in my web application.

Here's one strategy:

1. Get the results from the database and cache them.
2. The user needs the data, is it in the cache? Yes it is, return it.
3. Have a trigger on the country table that fires whenever the data changes and it updates a flag in another table (lets call it DATA_HAS_CHANGED_T) to say the countries table has changed.
4. Have a job in java running every (say) 5 minutes that polls the DATA_HAS_CHANGED_T table and see's that the countries table has changed. The job expires the cache and updated the flag in the table and finishes.
5. The user requests a list of countries, its not in the cache this time, so a trip is made to the db, the results are cached.........

I was wondering if this was done using AQ instead, it would be more scalable and performant?

The trigger would now put a message into a queue to say the countries table has changed. The java dequeue process would see the message and expire the cache. No more regular job needed.

What do you think?

Thanks

R

Tom Kyte
February 24, 2006 - 1:20 pm UTC

the standard approach could be wrong, couldn't it.

When trying to figure out if something you know, just "ain't so", look first at your standard operating procedures. They are full of stuff like that (that you KNOW, but just AIN'T SO).

see the new features for 10g - change notification.
</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm#sthref691 <code>

I'm not endorsing it in general, for I believe the "standard approach" is overdone, there are lots lower hanging fruit in most java applications. Bind variables being of course the first and foremost :) (or more precisely the LACK of bind variables)

Difference between queue message and queue payload

LML, May 11, 2006 - 4:44 pm UTC

Tom

What is the difference between a queue payload and a queue message.

When I delete a payload from a queue table, is the message also deleted.

Please explain.

Tom Kyte
May 11, 2006 - 8:15 pm UTC

payload is the message contents.

message is the entire thing, analogy: payload = letter, message = letter plus envelope plus address plus stamp....

Lml, May 11, 2006 - 8:35 pm UTC

Tom

Please answer the second part of my question above.

1.After Dequeuing the payload, I want to delete the payload, where should I set this up?

2.When I delete the payload will the entire message get deleted.

Tom Kyte
May 12, 2006 - 9:03 am UTC

have you read through the documentation for AQ itself.

dequeuing, message retention (or not) and all are described.

You choose, you can dequeue and leave the message, you can dequeue and remove the message, you can peek and not affect the message - whatever you want to do.

Thanks

san_mat, June 08, 2006 - 3:01 am UTC

Hi Tom,
Thank you very much for the useful information & the examples provided. I just have a small query, when we register a pl/sql procedure as a notification with the queue, in which data dictionary object i can find out the information for the same ?
I mean which data dictionary object tells us that proc. 'xyz' is registered with queue 'abc' as a pl/sql notification.
Appreciate your response.

Thanks in advance.

Tom Kyte
June 08, 2006 - 9:07 am UTC

aq$<queue>_s view has information about subscribers.

a question

A reader, July 10, 2006 - 6:17 pm UTC

Tom,

We implemented AQ with a callback to a PL/SQL procedure.
This procedure deQueues the message, processes it and moves it to another Queue. It is then deQueued and processed by another PL/SQl callback.

We EnQueue 1000 messages at a time. We want to EnQueue the next 1000 messages only when all the Queues are drained.

I was thinking of querying queue_table which was created through dbms_aqadm.create_queue_table.

ex: select count(*) from queue_table,
zero=> all the Queues are drained.

What is the best way to get this information.

Tom Kyte
July 11, 2006 - 7:43 pm UTC

Reader

Randy, July 15, 2006 - 1:04 pm UTC

Tom,

Processed Queue messages are not clearing.
Oracle 9
values set retention_time = 0 and aq_tm_processes = 2.
I don't understand why the messages are not clearing, appreciate you help.

Tom Kyte
July 15, 2006 - 3:25 pm UTC

smallest possible test case on the planet to reproduce with would look like what?

Advanced Queuing & PL/SQL Notification

Hemendra, July 27, 2006 - 5:11 am UTC

Hi Tom,

Please review this code

connect sys/o9isb9 as sysdba
drop user aqtest cascade;

CREATE USER aqtest IDENTIFIED BY aqtest;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aqtest;
GRANT EXECUTE ON dbms_aq TO aqtest;
GRANT EXECUTE ON dbms_aqadm TO aqtest;

begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
end;
/

connect aqtest/aqtest
CREATE type aqtest.Message_typ as object(subject VARCHAR2(30), text
VARCHAR2(80));
/

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aqtest.objmsgs80_qtab',
queue_payload_type => 'aqtest.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUE',
queue_table => 'aqtest.objmsgs80_qtab');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUE');
end;
/

-- ADDED: Setup additional queue to propagate messages to
begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aqtest.objmsgs80_qtabX',
queue_payload_type => 'aqtest.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUEX',
queue_table => 'aqtest.objmsgs80_qtabX');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUEX');
end;
/

-- SMD: you'll see there is now a second parameter, as discussed above:
create or replace procedure enqueue_msg(p_msg in varchar2,
p_add in varchar2 default null)
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aqtest.message_typ;
recipients DBMS_AQ.aq$_recipient_list_t;
BEGIN
-- ADDED
-- SMD: here's where the parameter is used
recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
message_properties.recipient_list := recipients;

message := message_typ('NORMAL MESSAGE', p_msg );
dbms_aq.enqueue(queue_name => 'msg_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/

-- SMD: this msgs is meant for THIS queue and will not be propagated.
begin enqueue_msg('This is a test....'); commit; end;
/

-- ADDED: Create loopback database link
create database link aqtest.LoopBack connect to aqtest identified by aqtest
using 'funk92.us.oracle.com';

-- ADDED: Setup scheduling for messages
begin DBMS_AQADM.Schedule_Propagation(Queue_Name => 'MSG_QUEUE',
Destination => 'aqtest.LoopBack',
Start_Time => sysdate,
Latency => 0);
end;
/

-- Check scheduling: Everything checked out OK.
select * from user_queue_schedules;

-- SMD: this msgs is meant for the MSG_QUEUEX queue and WILL BE propagated.
begin enqueue_msg('This should be propagated.',
'aqtest.MSG_QUEUEX@aqtest.LoopBack'); commit; end;
/

-- Check scheduling: Neither error nor action reported
select * from user_queue_schedules;
----------------------------------

I think this is what you're looking for:

aqtest@FUNK92> select t1.cnt, t2.cnt
2 from (select count(*) cnt from objmsgs80_qtab) t1,
3 (select count(*) cnt from objmsgs80_qtabx) t2
4 /

CNT CNT
---------- ----------
1 1



---
Here you used database link for loopback connection. now my query is :
1. I want to propagate Queue to some other database on other machine.
2. for this i already modified the code respectivly.

when i am using Database link and want to use a Table from other database on which object tables or user-defined type columns are defined then it gives ORA-22804 Error.

Please provide the solution for the same

AQ Documentation

Shekhar Pandit, September 13, 2006 - 12:04 pm UTC

Hello Tom/Sean

Where would I find the documentation on day-2-day support/monitoring that DBA group suppose to provide on AQ.

I need to setup some processes which will monitor this & send alerts to production support,developers depending on problems. All I am trying to search is potential issues with AQ & Mitigation.

Tom Kyte
September 13, 2006 - 3:06 pm UTC

Not sure what monitoring you would need - beyond what something like Enterprise Manager already provides.

AQ Documentation

Shekhar Pandit, September 13, 2006 - 12:06 pm UTC

Tom/Sean

Sorry forgot to Thank you in advance.

Regards
-Shekhar

Calling shell script via Advance Queue

Enayet, January 25, 2007 - 5:12 pm UTC

Tom,
This may sound obscure; but I should ask:

1. If there anyway we can invoke a Unix shell script instead of procedure using Advance Queue?

Scenerio:
We have an Application which need on-demand database backup (data is very precious!).
So we are thinking that by clicking an "BACKUP" button from App we will update a row in DBA_CONTROL table as "READY TO BACKUP". Then we will have Adavance Queue to invoke the shell script to start our backup (RMAN is the choice).

How can we accomplish our idea?

Thanks,

request-reply

A reader, April 20, 2007 - 3:17 am UTC

Hi,

I'm new to AQ, so please forgive me if my question seems unclear ,
we created the aq procedures as you adviced Jens on top of the page.
Could you give us an example of an request-reply message, how do we bind them together? Or point me to the right documentation?

thank you
Tom Kyte
April 20, 2007 - 7:06 am UTC

I don't know what you mean by a "request-reply message"

request-reply

A reader, April 23, 2007 - 5:06 am UTC

Hi Tom,

By request-reply message i mean for example:
If we were to send a ASYNCHRONOUS message1 from DB1 to DB2, when the DB2 has prosessed the message1 succesfully then DB1 should get a message2 about succesfull prosessing. We should bind this message1 to message2, so we could be sure that the message was not only received but prosessed too.

Can this bind be done automatically for example with correlation id? Or do we need to handle this with our own code? How does this happen in synchronous messaging?

Thank you for your help
Tom Kyte
April 23, 2007 - 4:33 pm UTC

what does it mean to send an asynchronous message to a database....

You can use a dblink to queue a job on the remote site, when you commit, the job will be able to be seen by remote site and can run. Job can, as it ends, use dblink to do something on the original site if you like.

Or, if you use AQ, you can use the queue propagation feature to

a) queue a message locally
b) have AQ move it
c) so remote site can process it

synchronous messaging is just called "sql" - you use dblinks, run procedures, execute sql.

PL/SQL notification does not work on (my) 9.2 instance

Alex Voinea, May 02, 2007 - 3:57 pm UTC

Hi Tom,

I am trying to run the code you provided after:
"and we said...
Here is an example of an asyncronous dequeue of a message from a queue using PLSQL."

with mixed results:
It works fine on 10gXE and 10g. However, I cannot get the notification procedure to execute on an Oracle 9.2.0.6 database:

-------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
-------------------------------------------------------

I can dequeue the messages manually, using:

SQL> CREATE OR REPLACE PROCEDURE dequeue_msg AS
  2     text                 VARCHAR2 (32767);
  3     MESSAGE              message_typ;
  4     dequeue_options      DBMS_AQ.dequeue_options_t;
  5     message_properties   DBMS_AQ.message_properties_t;
  6     msgid                RAW (16);
  7     descr                SYS.aq$_descriptor;
  8  BEGIN
  9     dbms_output.put_line ('dequeue_msg started');
 10     dequeue_options.msgid := descr.msg_id;
 11     dequeue_options.consumer_name := 'recipient' ; -- descr.consumer_name;
 12     DBMS_AQ.dequeue (queue_name              => 'MSG_QUEUE',
 13                      dequeue_options         => dequeue_options,
 14                      message_properties      => message_properties,
 15                      payload                 => MESSAGE,
 16                      msgid                   => msgid);
 17     COMMIT;
 18     dbms_output.put_line('dequeued [' || msgid || '~' || message.subject || '~' || message.text || ']');
 19  END dequeue_msg;
 20  /

Procedure created.

SQL>


However, the notification procedure (notifyCB) you provided does not get invoked (it's not that it doesn't dequeue, it does not get invoked at all).
How do I know this ? I added a debug message at the very beginning (a procedure that is doing an autonomous_transaction insert into a debug table).

I was just wondering, where can I look to diagnose what is happening ?

Here's the registration of the notifyCB callback proc:

SQL> SELECT * FROM sys.reg$;

SUBSCRIPTION_NAME              LOCATION_NAME                            USER# USER_CONTEXT CONTEXT_SIZE  NAMESPACE PRESENTATION    VERSION     STATUS
------------------------------ ----------------------------------- ---------- ------------ ------------ ---------- ------------ ---------- ----------
CUSTOMER.MSG_QUEUE:RECIPIENT   plsql://CUSTOMER.notifyCB                   33 FF                      1          1            0          0          0

The relevant parameters are set to the following values:

d:customer@TECHPRO3> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
aq_tm_processes                      integer     5
job_queue_processes                  integer     5
processes                            integer     200
d:customer@TECHPRO3>


Thank you for your time,
Alex


Tom Kyte
May 02, 2007 - 6:17 pm UTC

show parameter aq_tm

shows what?

Notification Registrations

Anton Nielsen, May 02, 2007 - 4:08 pm UTC

Tom,

You noted that aq$<queue>_s has information about subscribers.

Where can you find notification registrations? That is to say, if I do the following

begin
  dbms_aq.register
    ( sys.aq$_reg_info_list(
      sys.aq$_reg_info('MY_Q:MY_QS',
                        DBMS_AQ.NAMESPACE_AQ,
                        'plsql://test_proc',
                        HEXTORAW('FF')) ) ,
     1 );
end;
/

Where can I find plsql://test_proc associated with MY_Q and MY_QS?

Thanks,

Anton

PL/SQL notification does not work on (my) 9.2 instance

Alex Voinea, May 03, 2007 - 2:16 pm UTC

Re: parameter aq_tm shows what ?

aq_tm_processes and job_queue_processes are set to 5:

SQL> show parameter aq_tm

NAME TYPE VALUE
------------------------------------ ----------- -------
aq_tm_processes integer 5

SQL> sho parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------
aq_tm_processes integer 5
db_writer_processes integer 1
job_queue_processes integer 5
log_archive_max_processes integer 2
processes integer 200
SQL>

messaging gateway

Andrew, May 04, 2007 - 9:53 am UTC

Dear Tom,

We are trying - without any success - to make MGW work.

ENV.:
Oracle Enterprise 10g R2
O/S : Solaris (tried 2 = x86 as well as SPARC)

catmgw installed
AQ + Streams tested and is working

However, we are hitting several brick walls with MGW config, i.e.
1) We followed various on-line docs to set up listener + tnsnames + mgw.ora = but it really is unclear as maybe we are doing it wrong
2) We installed the IBM Web-Sphere MQ6 client
3) Ideally, we would like to have MGW on a separate from Oracle server machine = within DMZ, however it is something to test later - for now we really need a working example and not dozens of Oracle docs that all say "configure this and that according to your environment" + and later: "We assume that you have configured listener+tnsnames+mgw.ora correctly and here is what you should do to find why you are getting ORA-28575 error..."

Tom,
Have you ever worked with this environment = i.e. configured AQ/Streams + MGW to work together and push/pull data to/from IBM non-Oracle system and Web-Sphere MQ 6.

If you have successfully implemented such or have seen one done and know how config setup issues have been resolved we would really very much appreciate it.

BTW.
We have reported this to Oracle via metalink - but have not received any reply.

Kind regards
Andrew

Subscribe to Exception Queue

R.S.Karthik, August 02, 2007 - 8:22 am UTC

Hi Tom

We are implementing SOA architecture using Oracle SOA Suite. The Oracle Apps Adapter internally talks to an AQ in the Oracle ERP for all communications.
One of the failure point that we detected in our design if a message fails to get dequeued for whatever reasons, then the message is automatically routed to an exception queue and the adapter keeps trying from there until it succeeds.

At this point, we are still evaluating how to handle it from the adapter but we are also checking if it 's possible to subscribe to the exception queue so that we get a notification when an error happens.

I get the following error when I try to add a subscriber:
17:20:21 SQL-APPS> begin
17:21:25 2 dbms_aqadm.add_subscriber
17:21:25 3 ( queue_name => 'APPS.AQ$_WF_BPEL_QTAB_E',
17:21:25 4 subscriber => sys.aq$_agent( 'recipient', null, null ) );
17:21:25 5 end;
17:21:25 6 /
begin
*
ERROR at line 1:
ORA-24050: subscribers are not supported for exception queue APPS.AQ$_WF_BPEL_QTAB_E
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4426
ORA-06512: at "SYS.DBMS_AQADM", line 301
ORA-06512: at line 2

Looks like we cannot subscribe to an exception queue.. Are there any other workarounds for "triggering" a notification when something comes into exception queue.. ?

Thanks for any help on this
Karthik

Exception queue - Found a link

Karthik, August 03, 2007 - 8:07 am UTC

I found answer to my Exception Queue issue in the documentation..
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96587/qsample.htm#81498

planning to just browse the exception queue at regular intervals..

~~Karthik

queue and table become unusable if the msg type dropped first

Nikhilesh, July 08, 2008 - 2:34 am UTC

Dear Tom,
I had a working AQ setup but i dropped the message type with "force" option since i needed some chages in it.
I recreated the type with the same name.

But now the que and queue table has become unusable.
When i select, alter from queue table i get
ORA-04063: table "COMMON_DATA.AQ_QUEUE_TABLE" has errors

When i try to drop queue i get
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2873
ORA-06512: at "SYS.DBMS_AQADM", line 167
ORA-06512: at line 9

When i try to drop queue table i get
ORA-24002: QUEUE_TABLE COMMON_DATA.AQ_QUEUE_TABLE does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3575
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 11

Could you please help me out?

Thanks in advance.
Tom Kyte
July 08, 2008 - 9:37 am UTC

please utilize support for that.

What is the different between dequeue and delete?

Dapeng Geng, October 15, 2008 - 9:16 pm UTC

Hi Tom,

Do you know is there any difference between dequeue a message and delete it from the qTab?
Tom Kyte
October 17, 2008 - 8:30 pm UTC

you would never delete from the queue, you would use the API's provided to access this stuff.

dequeue provides you the API to retrieve the next message you should receive.

manage jobs

Chaman, October 20, 2008 - 12:12 pm UTC

Tom,
To be honest with you, I don't know the concept of queues (DBMS_AQ). I would definetly try understanding today but before that here is my requirement.

Of late one of our JOB (scheduled through DBMS_SCHEDULER) is giving problem (because of an logic error of course) and till we fix it, we want to timeout if it goes for ever.

Can I use DBMS_AQ.LISTEN's WAIT a paremeter for this ?

If so, can you please tell me how do I use this? If have already answered this question please send me the link.

Thanks
Tom Kyte
October 21, 2008 - 12:12 pm UTC

no, you would not use AQ for that, it wouldn't make sense. Your job is not using AQ, I don't see how AQ would even apply here.

If you can change the logic of the job (guess you were thinking along that line, putting a listen call into it?), you would just have the job ask itself "how long have I been running, longer than I should - if so, then RAISE PROGRAM_ERROR".

If you cannot change the logic of your job, create a new job that looks for dba_jobs_running every now and again and if you job is running long - kill it I suppose.


Seem though that the hack for the temporary fix is at least as hard to do as fixing the code properly in the first place? Why not go for fixing the code?

Thanks

Aarti, October 22, 2008 - 8:04 am UTC

Tom,
Thanks for the reply. Are the Scheduler Window used for this purpose, where I can define a window duration saying 15 minutes and if it's goes 15 minutes the job would terminate?

I tried but it seems it's not working.

I have done the following:
1. Created a plan, Consumer group (max_idle_item = 15 secs) and plan directive
2. Created a schedule
3. Created a job class (MY_JOB_CLASS)
4. Created a window and assigned it to an window group
5. Created a job with job class and set the attribute STOP_ON_WINDOW_CLOSE = TRUE

Can you help me out?


Here's the code snippet

Create or replace procedure proc1
as
ln number;
begin
insert into proctable values(0, sysdate);
commit;

--the next select takes 2 minutes
select count(1) into ln from big_tab;
insert into proctable values(ln, sysdate);
commit;

end;
/


Create Resource Plan
--------------------
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'job_plan',
COMMENT => 'Job plan for job users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Job_group',
COMMENT => 'Resource consumer group/method for job users sessions');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'job_plan', GROUP_OR_SUBPLAN => 'Job_group',
COMMENT => 'job users sessions at level 1', CPU_P1 => 40,
PARALLEL_DEGREE_LIMIT_P1 => 4, max_idle_time => 15, queueing_p1 => 15, max_idle_blocker_time => 15 );

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'job_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'job users sessions at level 1', CPU_P2 => 40, PARALLEL_DEGREE_LIMIT_P1 => 4);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;
/

Create Schedule
---------------
BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name => 'PROC_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=10;',
end_date => sysdate + 1,
comments => 'proc schedule repeats 10 minutes');
END;
/

Create Job Class
----------------

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS
(
job_class_name => 'MY_JOB_CLASS'
,resource_consumer_group => 'JOB_GROUP'
,service => NULL
,logging_level => SYS.DBMS_SCHEDULER.LOGGING_RUNS
,log_history => NULL
,comments => 'This is the default job class.'
);
END;
/



Create Window
-------------
BEGIN
DBMS_SCHEDULER.create_window (
window_name => 'W1',
resource_plan => 'JOB_PLAN',
schedule_name => 'PROC_SCHEDULE',
duration => INTERVAL '1' MINUTE,
window_priority => 'LOW',
comments => 'Window with a predefined schedule.');
END;
/


Create Window Group
-------------------
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP
(
group_name => 'MYWINDOW_GROUP'
,window_list => NULL
,comments => NULL
);
END;
/

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER
(group_name => 'MYWINDOW_GROUP',
window_list => 'W1');
END;
/

Create Job
-----------

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'FULL_JOB_DEFINITION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN proc1 ; END;',
schedule_name => 'PROC_SCHEDULE',
--schedule_type => 'MYWINDOW_GROUP',
job_class => 'MY_JOB_CLASS',
enabled => TRUE,
comments =>' Self contained job');
END;
/


Set Attribute
-------------
begin
DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'FULL_JOB_DEFINITION'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => true);
end;
/


Run Job
-------
BEGIN
DBMS_SCHEDULER.run_job (
job_name => 'FULL_JOB_DEFINITION');
end;
/

Concurrency of notifications

Michael Donnelly, December 11, 2008 - 8:17 am UTC

Tom,

When we create any number of queues in our system (11.1.0.7) which have pl/sql notifications, one job is spawned to handle all the notifications. The job is called aq$_plsql_ntfn_NNNN, and it runs the job_action sys.dbms_aqadm_sys.register_driver. This job calls the pl/sql procedure registered in the registration. While that procedure is running, this job is essentially blocked. I can create more jobs manually in dbms_scheduler which also have the aq$_plsql_ntfn prefix and the same job_action, and if I create n such jobs, I can have n + 1 callbacks running concurrently (because AQ spawns a job whether or not I create one.)

In addition, once these jobs finish processing notification AND a certain idle time has passed, these jobs exit. When each time the job exits, a new job must be spawned on the next enqueue to a registered queue. When the job is spawning, processing of the dequeue is much slower than when the job is already running and waiting to time out (second scale vs. millisecond scale).

My questions are:

1) Is there any way to tune the number of concurrent notification jobs? If not, is there a real danger to starting additional jobs myself?
2) Is it possible to tune the idle timeout of these jobs?
3) Am I missing some "approved" way to make notifications run in parallel, or is this lack of concurrency by design?

btw, it occurs to me that I could, in my callback function, use the dbms_scheduler functionality to make my actual processing occur in parallel, but this seems like a "hack" to me.
Tom Kyte
December 11, 2008 - 9:16 am UTC

I'll refer you over here for this one:
http://forums.oracle.com/forums/forum.jspa?forumID=66

Candidate for AQ?

Tom, December 20, 2008 - 11:16 pm UTC

Tom, Happy holidays to you and your family.

I have wanted to make use of AQ in a production environment for a couple of years now but have not had a customer with such a requirement. I believe I have come across a situation where the AQ will save the day. Can you please let me know if you believe that this would be a proper use of the AQ functionality?

My customer is running a call center with approximately 1000 call center representatives. The customer has an Oracle 10g database running on a Linux server. Their front-end call center representative application is web based. The front-end web based application is calling a stored procedure that will return the next customer phone number to be called by a call center representative. I have not been given access to look at the pl sql procedure code yet. I am told that it works by selecting a phone number from an unassigned customer table and places it into an in-use table to represent that the customer has been assigned to a representative. The code always checks against the in-use table before grabbing a new phone number from the unassigned table.

Sounded like code that would be logically problematic when it comes to making sure that 2 call center representative don¿t get the same phone number returned. My first response was to say that the code should be changed to have an assigned column added to the first table that would represent if the customer was free or not. I was thinking that we could have the stored procedure use a sql statement with `for update¿ to lock the row when setting the assigned column. This would prevent 2 representatives from getting the same customer phone number assigned. I don¿t see a need to keep their in-use table.

Later that night in my sleep a light bulb went off in my head and I remembered that I have AQ as a tool in my toolbox. I decided that this might be a perfect candidate for AQ. I believe that we could create a queue and add customer phone numbers to the queue periodically when the queue gets below a threshold of customer phone numbers (dbms_scheduler job) and mark them as queued up for call. Every night we could seed the queue with a whole day worth of customer phone numbers. We can have the web based front-end call our package to retrieve the next customer phone number that has been marked as queued up for call. Our package can call the AQ dequeue to retrieve and delete the next phone number from the queue. There is no requirement to know which call center representative is assigned to which customer¿s phone number. Another table that logs the call interaction information will log this information. Management wants to make sure that the call center representatives have phone numbers to call and that the center does not call the same customer more than once.

The main reason I am being brought into this problem is that the customer¿s current code keeps running into a deadlock and none of the 1000 users can work until the DBA kills all of their sessions. Of course management wants this fixed ASAP and wants some type of guarantee that the new solution will not have similar results once in production. The development team has not been able to reproduce the result in the test database. Of courses it has been hard for them to simulate 1000 users coming into the database over the web with out the use of a test software package.

Do you believe I should go forward with recommending and employing the AQ or just implement better PL SQL logic in the existing design?






Tom Kyte
December 29, 2008 - 2:22 pm UTC

it does sound like, based on this cursory reading, that instead of them inserting a record into "unassigned customer table", it should queue a message and then call center people dequeue - yes.


Advance Queue - Dequeue session limit by queue

A Reader, January 07, 2009 - 3:09 pm UTC

Hi Tom,

Is it possible to set dequeue session limit at queue level?

Like I have QueueA where I want max 5 messages to be dequeued at a time and for QueueB I want max 100 messages getting dequeued.

Thanks.

Advance Queue - Dequeue session limit by queue

A Reader, January 07, 2009 - 3:45 pm UTC

My question is on concurrent dequeue limit.

Say, if 1000 messages are enqueued only 100 messages will be dequeued concurrently. Kind of throttling number of dequeue sessions get spawned for a queue.

Is your initial example working when payload type is SYS.XMLType?

Philip, June 29, 2009 - 12:23 pm UTC

Hi Tom,

I tried your PL/SQL notification example and admittedly it works perfectly OK for the ADT you defined (aq.message_typ).
However, when I changed the payload type to SYS.XMLType, instead of the notification procedure executing (and inserting it straight to a table of XMLType) it doesn't, although I can "see" the message in the queue.
What I also saw is that every time I enqueue an XMLType there is an extra entry added in SYS.AQ_SRVNTFN_TABLE queue table and that the messages are queued in the exception queue (SYS.AQ$_AQ_SRVNTFN_TABLE_E).

Any ideas why this happens ?

Thanks in advance
Philip

PS: I am still kicking myself for missing your presentations here last April. I was - unfortunately - unavoidably detained ;). I hope you had a good time though ....

PLSQL Notification not working

Chinni, December 16, 2009 - 7:08 am UTC

Hi Tom,
thank you very much for your time in clarifying doubts on this site!

I am testing AQ PLSQL notification in my db but it is not working, messages are stying in "Ready" state forever instead of being dequeued in the notified procedure. I have tested the same code in another database and there it is working. Both databases are on 11.1.0.7.0 , init parameters of both databases are same.

could you kindly help?

This is the code I have used
=========================
CREATE TYPE demo_queue_payload_type AS OBJECT (
MESSAGE VARCHAR2 (4000)
);

CREATE TABLE demo_queue_message_table ( MESSAGE VARCHAR2(4000) );

BEGIN
DBMS_AQADM.create_queue_table
(queue_table => 'demo_queue_table',
queue_payload_type => 'demo_queue_payload_type',
multiple_consumers => TRUE
);
END;

BEGIN
DBMS_AQADM.create_queue (queue_name => 'demo_queue',
queue_table => 'demo_queue_table'
);
DBMS_AQADM.start_queue (queue_name => 'demo_queue');
END;

CREATE TABLE demo_queue_message_table( MESSAGE VARCHAR2(4000) );

CREATE PROCEDURE demo_queue_callback_procedure (
CONTEXT RAW,
reginfo SYS.aq$_reg_info,
descr SYS.aq$_descriptor,
payload RAW,
payloadl NUMBER
)
AS
r_dequeue_options DBMS_AQ.dequeue_options_t;
r_message_properties DBMS_AQ.message_properties_t;
v_message_handle RAW (16);
o_payload demo_queue_payload_type;
BEGIN
r_dequeue_options.msgid := descr.msg_id;
r_dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.dequeue (queue_name => descr.queue_name,
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);

INSERT INTO demo_queue_message_table
(MESSAGE
)
VALUES ( 'Message ['
|| o_payload.MESSAGE
|| '] '
|| 'dequeued at ['
|| TO_CHAR (SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS.FF3')
|| ']'
);

COMMIT;
END;

BEGIN
DBMS_AQADM.add_subscriber
(queue_name => 'demo_queue',
subscriber => SYS.aq$_agent
('DEMO_QUEUE_SUBSCRIBER',
NULL,
NULL
)
);
DBMS_AQ.REGISTER
(SYS.aq$_reg_info_list
(SYS.aq$_reg_info ('DEMO_QUEUE:DEMO_QUEUE_SUBSCRIBER',
DBMS_AQ.namespace_aq,
'plsql://DEMO_QUEUE_CALLBACK_PROCEDURE',
HEXTORAW ('FF')
)
),
1
);
END;

DECLARE
r_enqueue_options DBMS_AQ.enqueue_options_t;
r_message_properties DBMS_AQ.message_properties_t;
v_message_handle RAW (16);
o_payload demo_queue_payload_type;
BEGIN
o_payload :=
demo_queue_payload_type (TO_CHAR (SYSTIMESTAMP,
'DD-MON-YYYY HH24:MI:SS.FF3'
)
);
DBMS_AQ.enqueue (queue_name => 'demo_queue',
enqueue_options => r_enqueue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
COMMIT;
END;



Tom Kyte
December 16, 2009 - 10:20 am UTC

is your cjq0 process running, any messages in the alert log.

(no, I did not read your code, triage first. I assume you have run MY example verbatim right?)

finally it is resolved .. but need some help on how to debug such things .. thanks

Chinni, December 17, 2009 - 9:35 am UTC

Hi Tom,

That must have been your example, certainly i did not write it myself :)

We finally resolved this, as you said some background job issue it seems. However there were no trace files or alert log entries for this at all. One of my team mates noticed that (on Grid control) a job, aq$_plsql_ntfn4584 , was stuck and not processing the messages, I do not know internal of PLSQL notification. that is the reason I am just writing how I heard from my team mate, then he stopped that plsql notification job and cleared a queue called , AQ_SRVNTFN_TABLE_Q, and ran dbms_aqadm_sys.register_driver and after these three steps plsql notification right on track.

I think what you told to check is also related to this. But due to no trace files or alert entries I could not figure this out at first. I could also not find any failed jobs. I don't know why. I don't how it is was displayed on Grid but

Could you please let me know, if I have this kind of issue, how do I debug this. This might be too generic question, but
if you could just guide me with some points it will be helpful. May be I am missing basics of plsql notification.


thank you very much for your time

"..we don't commit in the enqueue procedure.."

JD, February 02, 2010 - 2:00 pm UTC

> aq@ORA920> exec enqueue_msg( 'This is a test....' );
> PL/SQL procedure successfully completed.

> aq@ORA920> commit;
> Commit complete.

> commit needed, we don't commit in the enqueue procedure..

Tom,
Why did you COMMIT at all? Is it needed? Is it necessary after ENQUEUE?
If so, then I'm confused (& disappointed?) because I thought async messaging was all about getting away from the COMMIT mechanism.
-JD
Tom Kyte
February 02, 2010 - 2:32 pm UTC

No, you have confused yourself. Entirely and completely.

Async messaging is about loosely coupled processing. Instead of a transaction being:


begin work
   insert a row into an order table
   generate an email to someone about the row in the table
   run a long query to generate a report about that query
commit work


(that is a series of tightly coupled processes - all or nothing and you wait till the end)

Where the end user is waiting (patiently, as end users are wont to do) for the message "your order has been placed", you would:

begin work
   insert a row into an order table
   queue message "row added!"
commit work


Now, your end user gets the message IMMEDIATELY that "your order has been placed" and you get a pat on the back for being such a fine tuner of processes - you made things go so so so fast...

In the background, another process starts:

begin work
   dequeue message
   generate email
   run the report
commit work (makes the message disappear)



You have the same transaction end to end, you just broke it up into two pieces.


That is what messaging is about - you of course need commit - and rollback - you of course need transaction boundaries.


I have no idea what you could have been thinking about - you need transactions, you need a beginning and an ending - lines drawn as to where work (done as a unit) will be performed.




"..we don't commit in the enqueue procedure.."

JD, February 02, 2010 - 3:54 pm UTC

>you need transactions, you need a beginning and an ending - lines drawn as to where work (done as a unit) will be performed.

I thought transactions/COMMIT/lines drawn were only needed in DML.

and there is no DML in your following:

begin work
dequeue message
generate email
run the report
commit work (makes the message disappear)

so why the 2nd COMMIT in your pseudo-code?

Are you saying that the only way one can successfully complete a DEQUEUE is to COMMIT? If so I remain confused.

My thinking/perception (& i don't claim to know ANYTHING) is that AQ /async messaging is loosely coupled and therefore NOT in need of a strong coupling mechanism aka transaction COMMITs.

Or maybe that's the theory but in practice we must needs use COMMIT after DEQUEUE since we're still using queue_tables?

And IF so, then what about this that I just learned:

enqueue_options.visibility := dbms_aq.IMMEDIATE; --autonomous

versus

enqueue_options.visibility := dbms_aq.ON_COMMIT;


>..you have confused yourself. Entirely and completely.
Yes. and yours I remain so...?!?

Tom Kyte
February 03, 2010 - 9:27 am UTC

....I thought transactions/COMMIT/lines drawn were only needed in DML....

what do you think putting a message into a QUEUE is? It has to be transactionally consistent.


There is "DML" all over the place.


begin procedure; end;

Look - no inserts, no updates, no deletes, no merge, no select (SELECT by the way is DML).

You need a commit or rollback to end a transaction. Many things can start a transaction.

CREATE - implicitly ends any existing transaction, begins a new one, does the DDL, and implicitly commits or rollsback. - the COMMIT is there, you just don't do it.


... Are you saying that the only way one can successfully complete a DEQUEUE is to
COMMIT? If so I remain confused.
...

why? What if your thing that dequeus FAILS, completely FAILS. It crashes, it runs halfway and stops.

You need to undo the work it did AND to un-dequeue the message. Hence ROLLBACK.

What if it succeeds??? Then the work IT DID plus the dequeue must be made persistent - permanent. Hence COMMIT.


... My thinking/perception (& i don't claim to know ANYTHING) is that AQ /async
messaging is loosely coupled and therefore NOT in need of a strong coupling
mechanism aka transaction COMMITs. ...


the loose coupling is the loose coupling of TRANSACTIONS - always.



Hopefully that clears it up a bit. Think about a dequeue followed by a bunch of work. All of that work must be done (or not) and the dequeue can only be made permanent AFTER the work is done - as part of that work itself. It has to be either all done, or not done.

AQ vs Regular tables

rollersun, February 02, 2010 - 7:21 pm UTC

I have heard this argument many times. Everything that can be done in AQ can be done using regular tables. Why use AQ?
Tom Kyte
February 03, 2010 - 9:53 am UTC

Anything that can be done in an off the shelf HR package can be done in regular tables too - start with the SCOTT schema, it has an EMP table, should be a good foundation.


Anything that can be done in a database with ACID properties can be done in assembler on a machine without an operating system too. In fact, you could do it on paper - we did that for many thousands of years (rocks before that, more persistent storage even, some of those records still exist).


Anything can be done with Anything.


If you want a queue you can either

a) build it yourself
b) use what already exists, what already has many interfaces (eg: JMS, enqueue/dequeue procedures, gateways to other messaging systems etc), what already have prioritization, rules, propagation to other systems capability, etc.



Go ahead, try to get the first unprocessed record from a table with 10 dequeue processes. I know you *can*, I've written that code a thousand times. I can do it with dequeue, you can do it with procedural code - but go ahead write it.

And then add the other necessary features that ultimately crop up - like multiple recipients, peeking without dequeuing, propagation to another system,etc.


You can write anything - the fact Oracle exists shows that - but you SHOULDN'T write everything - if you do, you are doing it wrong.

AQ is a tool

djb, February 03, 2010 - 8:48 am UTC

Everything that can be done in AQ can be done using regular tables. Why use AQ?

AQ is a tool. You don't have to use it to do the things you want. However, it has a framework that makes it great for handling large amounts of asynchronous activity. We use it here - we have over 100 different queues all using the same queue table, and we process well over 1000 queue messages per second (along with all the other activity in the database)

Are you saying that the only way one can successfully complete a DEQUEUE is to COMMIT? If so I remain confused.

A dequeue is DML, so yes, it needs a commit. The commit removes it from the queue. If you rollback after a dequeue, it goes into the exception queue.

AQ vs. asynchronous messaging?!?!

JD Donaldson, February 03, 2010 - 10:40 am UTC

Reading all of the above responses is very helpful and it makes me think perhaps I'm not as confused as I thought I was.

Isn't it a mistake to associate AQ with async messaging? It sounds to me like there's nothing async with AQ at all since queue_tables are the underlying foundation and THEREFORE COMMITS ARE REQUIRED.

Some of the above comments fail to address that there's nothing "async messaging" about COMMIT transactions. or am I wrong?
(sorry I'm definitely not the expert here; appreciate all feedback; & I defer to Tom and the others here).

I thought I could use AQ between 2 separate DB's in order to get away from the COMMIT mechanism. At best AQ seems to provide a wrapper for AUTONOMOUS_TRANSACTION so that it actually works properly via dblink.

or am I wrong?
Tom Kyte
February 03, 2010 - 12:03 pm UTC

... Isn't it a mistake to associate AQ with async messaging? ...

no??? not at all????

*all* - as in ever - *all* queues need a stable, transactional, persistent base - else they are not very useful.

Would you like your paycheck processing to be done via loosely coupled processes that used a queue that didn't have the concept of commit or rollback???? I sure as heck WOULD NOT. (well, on the months I got paid more than once maybe I wouldn't have a problem).


*all queue table*, in all *queueing implementations* have transactional qualities. You want to use IBM's MQ series? Guess what, you just bought DB2.


... At best AQ seems to provide a wrapper for
AUTONOMOUS_TRANSACTION so that it actually works properly via dblink.

or am I wrong?...

you are wrong. You might want to pick up a book similar to:

http://www.amazon.com/exec/obidos/redirect?tag=asktom03-20&path=tg%2Fdetail%2F-%2F1558601902

to learn about transaction processing in general, the concepts and theory behind the practice.



... I thought I could use AQ between 2 separate DB's in order to get away from the
COMMIT mechanism. At best AQ seems to provide a wrapper for
AUTONOMOUS_TRANSACTION so that it actually works properly via dblink.
....

I would like you as an exercise, to describe HOW that would work. From start t finish. With all of the error conditions you would hit.


Here is how it works in *real life*, in all queuing scenarios (well, the scenarios that purport to ensure data actually gets reliably MOVED from point a to point b that is)


a) you do a transaction in database 1. say "update accounts set bal = bal+100 where account = ?".

b) with that transaction, as part of that transaction, you queue a message into a queue. This message will be processed AFTER you commit.

c) you commit, that makes the message visible for the very first time. If you had rolled back, of course the update AND the message you just queued would disappear. Regardless - either you did (a) AND (b) or you did NEITHER of (a) or (b). You are, as we say, transactionally consistent.

d) after you commit, another process can dequeue your message for the first time (it wasn't visible to anyone until you commit - it wasn't ready until then).

e) that process reads your message which basically says "I updated account X to Y" in some form. That process decides what to do with that fact - in this case, the correct action is to connect to database 2 and apply that update there.

f) that process would now request a TWO PHASE COMMIT - 2pc - (this is probably why you did queues in the first place, to remove the 2pc from the client transaction - to make it so that (a) can take place even if database 2 is NOT available - that is the goal of loosely coupled processing - increased availability (and reduced response times, from the end user perspective). The 2pc is necessary to commit the dequeue (the removal of the message on database 1) AND the update to database 2 as a single atomic transaction. EITHER BOTH or NEITHER of these operations must happen - as a single unit. You cannot dequeue, commit, update, commit - you have to dequeue + update AND THEN commit both.


I don't see the relationship to autonomous transactions (atrans) AT ALL. atrans are "in real time" (a subtransaction, they happen in the same timeline as their parent, not after). They do not facilitate loosely coupled processing at all. They are almost always used for evil - not for good (by those that don't get transaction processing). Queues, when used correctly, have none of those properties.

AQ vs. asynchronous messaging?!?!

JD Donaldson, February 03, 2010 - 10:47 am UTC

>what do you think putting a message into a QUEUE is? It has to be transactionally consistent.

why does the concept of "putting a message into a QUEUE" HAVE to be "transactionally consistent"?

It seems to me that the only reason this is so in ORACLE LAND is because AQ happens to use queue_Tables as it's underlying foundation. So isn't that a design flaw or a misrepresentation of async messaging?

Please don't find me to be argumentative or difficult. I really am seeking understanding and am just trying to express my confusion.

Tom Kyte
February 03, 2010 - 12:09 pm UTC

read above.


It happens in ALL queuing mechanisms. Please sit down and think through the "make a change that happened in system 1 happen also in system 2" using queues.

Now, think about it if the queue modifications (enqueue, dequeue) ARE NOT transactionally consistent with the other modifications. What happens when an error occurs in any multi-step process?????? YOU LOSE - that is what.

Hence, all queues support this capability, that of commit and rollback.


It is not a design flaw, it is a PRE-REQUISITE to being called a queue. Period.


I don't see how this "misrepresents" async at all. Definition please:


a?syn?chro?nous
  /e??s??kr?n?s/ Show Spelled Pronunciation [ey-sing-kruh-nuhs] Show IPA
Use asynchronous in a Sentence
See images of asynchronous
Search asynchronous on the Web
adjective
1.  not occurring at the same time.
2.  (of a computer or other electrical machine) having each operation started only after the preceding operation is completed.
3.  Computers, Telecommunications. of or pertaining to operation without the use of fixed time intervals (opposed to synchronous ).




1) not occurring at the same time - check. got it, see above list in previous followup. Definitely not occurring at same time, rather occurring one after the other.


2) having each operation started only after the preceding operation is completed. Check, got it.


AQ is permitting async operations, it facilitates it. The use of commit/rollback - has nothing to do with the discussion of async processing - other than the fact that EACH AND EVERY STEP that precedes some other step must end in commit or rollback to ensure that the entire previous step either entirely succeeds or entirely fails.



Using queues does not suspend the law of transactions, not at all. If anything, it demands MORE from them.

AQ vs. asynchronous messaging?!?!

JD Donaldson, February 03, 2010 - 10:50 am UTC

>>
why? What if your thing that dequeus FAILS, completely FAILS. It crashes, it runs halfway and stops.

You need to undo the work it did AND to un-dequeue the message. Hence ROLLBACK.

What if it succeeds??? Then the work IT DID plus the dequeue must be made persistent - permanent. Hence COMMIT.
<<

None of the above seems asynchronous to me. It seems purely synchronous, the synchronicity enforced by the Ora COMMIT mechanism.


Tom Kyte
February 03, 2010 - 12:13 pm UTC

you are missing the definition of async, please read above.


If you have steps:

begin work
   insert a row into an order table
   generate an email to someone about the row in the table
   run a long query to generate a report about that query
commit work


and you make that be:

begin work
   insert a row into an order table
   queue message "row added!"
commit work

AND THEN, LATER, AFTER THE ABOVE, IN ANOTHER TIMEFRAME (eg: asynchronously)

begin work
   dequeue message
   generate email
   run the report
commit work (makes the message disappear)



You have taken a synchronous process (insert, generate, report) and broken it into 2 asynchronous processes 1) (insert,queue) and then 2) (dequeue,generate, report). The end user waits for (1) to complete and nothing more, then later, in another timeframe, after (1) completes (all from the very definition of async!), (2) runs to completion (or stays in the queue until it does!!!!)



commit forcing syncronicity? I don't even get that.

JD Donaldson, February 03, 2010 - 10:54 am UTC

>A dequeue is DML, so yes, it needs a commit. The commit removes it from the queue. If you rollback after a dequeue, it goes into the exception queue.

Am I wrong that a dequeue is conceptually and expressly NOT DML? Again, the only reason why it HAPPENS to be DML in ORACLE is because of the design of AQ using tables.
Tom Kyte
February 03, 2010 - 12:15 pm UTC

it happens to be a modification in all queues, all of them. Not just in "oracle", if you have a queue that isn't transactionally consistent, you have a queue that isn't very usable in real life.

You could never know if a message had already been processed or not.
You'd never know if the thing the message was supposed to have triggered happend (or not).

You'd be blind.



Who's registered

Dayne Olivier, February 17, 2010 - 8:06 am UTC

Hi Tom,

On the subject of plsql callbacks, how do you determine what callbacks are registered?

I have created a queue and registered a plsql callback. I have confirmed that the plsql callback is being called. But when I try selecting from [ALL|DBA|USER]_QUEUE_SUBSCRIPTIONS, I don't get any records. The documentation says these views are only applicable for queue tables that are 8i compatible. But then what should be used for 10g compatible tables? Or is that exactly the point... we shouldn't be using 10g compatible?

My queue table was created using:
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'RAW_QUEUE'
   ,QUEUE_PAYLOAD_TYPE    =>        'RAW'
   ,COMPATIBLE            =>        '10.0.0'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );
End;
/

and I registered my callback using:
 declare

  l_list     sys.aq$_reg_info_list;
  l_reg      sys.aq$_reg_info;

 begin

  l_reg := sys.aq$_reg_info(
   'PQAB_V1.ABACUS_IMP_Q', 
   dbms_aq.NAMESPACE_AQ, 
   'plsql://PQAB_V1.ABACUS_ASYNC_API.PROCESS_IMPORT?PR=0',
   null
  );
  l_list := sys.aq$_reg_info_list(l_reg);
  dbms_aq.register(l_list, 1);

 end;
/


Thanks for the assistance Tom.
PS. AQ notification feels flaky. I am never really sure if things are setup correctly and think it would really help if there were a few more admin views giving status of notification and registration metadata.

Tom Kyte
February 17, 2010 - 10:57 am UTC

contact support, reference

Bug 7541906 and Note 836327.1


it is actually a bug with the *_queue_subscribers views - using the /*+ ordered */ hint on the view (select /*+ ordered */ * from dba_queue_subscribers) is a possible temporary workaround.

call to dbms_aq.dequeue Hangs

Kaleem, February 24, 2010 - 6:43 am UTC

hi Tom, I am sorry for disturbing you at your busy schedule as lost me deadly upon dbms_aq.dequeue.
i se t following param with sys
ALTER SYSTEM SET SGA_TARGET = 1024M SCOPE=BOTH;
ALTER SYSTEM SET undo_retention=8600 SCOPE=BOTH;
ALTER SYSTEM SET timed_statistics=true SCOPE=BOTH;
ALTER SYSTEM SET job_queue_processes=6 SCOPE=BOTH;
ALTER SYSTEM SET streams_pool_size = 0 SCOPE = SPFILE;
ALTER SYSTEM SET job_queue_processes = 6 ;
ALTER SYSTEM SET aq_tm_processes = 6 ;
used DBMS_STREAMS_ADM.SET_UP_QUEUE with agent 'a'
and set message notification to call the proc.
initially it was working but later on it hangs on even i called it explicitly.

deqopt.consumer_name := 'a';
DBMS_AQ.DEQUEUE(queue_name => 'ULTRADBA.ULTRA_TRADE_QUE',dequeue_options => eqopt,message_properties => mprop,payload => payload,msgid=> msgid);

What I think is setting some parameter wrong above in init.ora.
Tom Kyte
March 01, 2010 - 8:51 am UTC

are there any messages to actually dequeue?

mq series ibm

nick, February 25, 2010 - 4:59 am UTC

What is Oracle Message gateway in simple terms defined?

Is it a separate product that we have to buy license or bundled with oracle enterprise database license?

Requirments is to install omg in between oracle database and ibm mq series. Can this not be done by tuxedo or web logic?

cheers
Tom Kyte
March 01, 2010 - 9:28 am UTC

The message gateway is a separately licensed bit of software, yet.

You would need a two phase commit between Oracle and IBM to do it yourself, so you could code that using XA yourself if you wanted to dequeue, translate, enque the message again yourself.

Time between dequeue and payload removal from Q table

Rajakumar Bopalli, February 26, 2010 - 7:50 am UTC

Hi Tom,

I've the following call back procedure.
When I en-queue a message in my Q,
the callback is being called and message is inserted into TEST table.
However, It is taking atleast 10 seconds to remove the message from Q table.

Could you please let me know the reason for this?

CREATE OR REPLACE PROCEDURE foo_callback (context IN RAW,
reginfo IN SYS.AQ$_REG_INFO,
descr IN SYS.AQ$_DESCRIPTOR,
payload IN VARCHAR2,
payloadl IN NUMBER)
as
msg SYS.AQ$_JMS_TEXT_MESSAGE;
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
msg_id RAW(16);
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => 'TEST_QUEUE:AGENT_10', dequeue_options => dequeue_options,
message_properties => message_properties, payload => msg, msgid => msg_id);

INSERT INTO test (TEST_COLUMN) VALUES (msg.text_vc);

COMMIT;
END;

Dequeue delay

A reader, May 03, 2010 - 1:46 pm UTC

I had a similar problem when I enqueued to a queue. It was remaining in the queue for what I estimated was about 30 seconds. In my case, I had setup the Oracle AQ with multiple_consumers=TRUE. When I changed multiple_consumers=FALSE, the message was dequeued immediately. The delay was Oracle's way to give multiple consumers a reasonable amount of time to read the same message. This may not be your problem but I thought I would point a potential resolution. Good luck.

not working, need help

G.S.Reddy, February 03, 2011 - 8:12 am UTC


SYS@TSTDB> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SYS@TSTDB> drop user aq cascade;

User dropped.

SYS@TSTDB> CREATE USER aq IDENTIFIED BY aq;

User created.

SYS@TSTDB> GRANT CONNECT, RESOURCE, aq_administrator_role TO aq;

Grant succeeded.

SYS@TSTDB> GRANT EXECUTE ON dbms_aq TO aq;

Grant succeeded.

SYS@TSTDB> GRANT EXECUTE ON dbms_aqadm TO aq;

Grant succeeded.

SYS@TSTDB> begin
  2    dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQ',FALSE);
  3    dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQ',FALSE);
  4  end;
  5   /

PL/SQL procedure successfully completed.

SYS@TSTDB> connect AQ/AQ@TSTDB
Connected.

USER_NAME                      DB_NAME
------------------------------ ------------------------
AQ                             TSTDB

AQ@TSTDB> CREATE type aq.Message_typ as object( subject VARCHAR2(30), text VARCHAR2(80));
  2  /

Type created.

AQ@TSTDB>  begin
  2          DBMS_AQADM.CREATE_QUEUE_TABLE
  3          ( queue_table => 'aq.objmsgs80_qtab',
  4            queue_payload_type =>  'aq.Message_typ',
  5            multiple_consumers => TRUE );
  6  
  7          DBMS_AQADM.CREATE_QUEUE
  8          ( queue_name => 'MSG_QUEUE',
  9            queue_table => 'aq.objmsgs80_qtab');
 10  
 11         DBMS_AQADM.START_QUEUE
 12         (  queue_name => 'MSG_QUEUE');
 13     end;
 14  /

PL/SQL procedure successfully completed.

AQ@TSTDB>  create procedure enqueue_msg( p_msg in varchar2 )
  2      as
  3       enqueue_options dbms_aq.enqueue_options_t;
  4       message_properties dbms_aq.message_properties_t;
  5       message_handle RAW(16);
  6       message aq.message_typ;
  7      BEGIN
  8         message := message_typ('NORMAL MESSAGE',  p_msg );
  9         dbms_aq.enqueue(queue_name => 'msg_queue',
 10                        enqueue_options => enqueue_options,
 11                        message_properties => message_properties,
 12                        payload => message,
 13                        msgid => message_handle);
 14     end;
 15  /

Procedure created.

AQ@TSTDB> create table message_table( msg varchar2(4000) );

Table created.

AQ@TSTDB> create or replace procedure notifyCB( context raw,
  2                                            reginfo sys.aq$_reg_info,
  3                                            descr sys.aq$_descriptor,
  4                                            payload raw,
  5                                            payloadl number)
  6      as
  7       dequeue_options dbms_aq.dequeue_options_t;
  8       message_properties dbms_aq.message_properties_t;
  9       message_handle RAW(16);
 10      message aq.message_typ;
 11     BEGIN
 12        dequeue_options.msgid := descr.msg_id;
 13        dequeue_options.consumer_name := descr.consumer_name;
 14        DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
 15                        dequeue_options => dequeue_options,
 16                        message_properties => message_properties,
 17                        payload => message,
 18                        msgid => message_handle);
 19        insert into message_table values
 20        ( 'Dequeued and processed "' || message.text || '"' );
 21        COMMIT;
 22     END;
 23  /

Procedure created.

AQ@TSTDB>  begin
  2          dbms_aqadm.add_subscriber
  3          ( queue_name => 'aq.msg_queue',
  4            subscriber => sys.aq$_agent( 'recipient', null, null ) );
  5      end;
  6   /

PL/SQL procedure successfully completed.

AQ@TSTDB>  BEGIN
  2          dbms_aq.register
  3          ( sys.aq$_reg_info_list(
  4              sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
  5                                DBMS_AQ.NAMESPACE_AQ,
  6                               'plsql://AQ.notifyCB',
  7                                HEXTORAW('FF')) ) ,
  8            1 );
  9      end;
 10   /

PL/SQL procedure successfully completed.

AQ@TSTDB> select * from message_table;

no rows selected

AQ@TSTDB> exec enqueue_msg( 'This is a test....' );

PL/SQL procedure successfully completed.

AQ@TSTDB> commit;

Commit complete.

AQ@TSTDB> pause

AQ@TSTDB> select * from message_table;

no rows selected

AQ@TSTDB> /

no rows selected

AQ@TSTDB> CONN SYS@TSTDB AS SYSDBA
Connected.

USER_NAME                      DB_NAME
------------------------------ ------------------------
SYS                            TSTDB

SYS@TSTDB> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     250
SYS@TSTDB> show parameter aq_tm

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1


SYS@TSTDB> select subscription_name, location_name
2 from sys.reg$;

SUBSCRIPTION_NAME LOCATION_NAME
----------------------------------------------------------------------------------------------------
"AQ"."MSG_QUEUE":"RECIPIENT" plsql://AQ.notifyCB

Tom, it is not running dequeue process automatically when we enqueue a message, am I doing anything wrong or do we need to make any changes to db parameters?

please help me, this is deadly needed for our application

Thanks,
G.S.Reddy

Ideas?

A reader, February 08, 2011 - 5:52 am UTC

Any Idea guys what's going wrong in my code?

PL/SQL Notification for messages queued before enabling queue for dequeing

SIE, February 07, 2012 - 4:19 pm UTC

Hi Tom,

The example you provided is very usefull and well documented, thank you. It works very well. However, I have a few concerns about how pl/sql notification works after disabling/re-enabling dequeue for a given queue.

Let's say we have the given example working nice. Something goes wrong and dequeueing has to be stopped because every messages in the queue fails to complete ang gets expired (the pl/sql procedure getting notified calls an [now] offline webservice). So, we issue a dbms_aqadm.stop_queue('queue', false, true) to disable dequeueing on thath queue.

Everything continue to work well and applications are still enqueueing their messages correctly. Pl/SQL notification is stopped, no more error occur.

After the called webservice goes back online, we want to restart dequeueing so we issue a dbms_aqadm.start_queue('queue', false, true) to enable dequeueing. PL/SQL notification then restarts and calls the pl/sql procedure to dequeue new messages. The problem remains in older messages (enqueued prior restart dequeueing), they don't get notified and stay in the queue.

Is there any way these messages get notified automatically like new enqueued messages? I found a couple of workarounds here and there and the options seems to be :
1-Re-enqueue these messages in the same queue with another pl/sql procedure so they get treated the same way;
2-Change the notified pl/sql procedure to loop around messages so they get processed.

I don't like these workaround especially because they are workaround. I would like to rely on the database to get the pl/sql notified, not writing code on my own to do so. I don't want to select data from aq$_queue to get unprocessed messages (based on scn at enabling dequeuing) to re-enqueue or manage them by hand?

I tried on 10.2.0.3 and 11.2.0.2 with same results so I guess it's not a bug, it's the way it works. What do you suggest?

Thank a lot.

Thread on AQ forums that explains this

SIE, February 13, 2012 - 2:38 pm UTC

Thread: PL/SQL Notification after re-enabling dequeue
https://forums.oracle.com/forums/thread.jspa?messageID=10145173#10145173

A reader, June 27, 2012 - 1:00 pm UTC

Hello Sir,

I am reading your book Expert Oracle Database Architecture. I just read first 10 pages and found learn something new.. Very excited to read this book.

In Chapter 1 Page 10 you mention

"Both of the preceding “solutions” would help to solve the second serialization problem my client
was having when processing messages. But how much easier would the solution have been if my client
had just used Advanced Queuing and invoked DBMS_AQ.DEQUEUE?"

I could not find how to solve above problem via Advanced Queuing, can you pl. show me how to resolve this via Advanced Queuing?

Regards
sonu
Tom Kyte
June 27, 2012 - 1:05 pm UTC

umm, you would just have to create a queue, write a program to enqueue and another to dequeue.

you could search around for samples
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8760267539329

for example


What way to go?

Galen Boyer, August 23, 2012 - 10:04 am UTC

Hi Tom,

We are just starting with one queue and I'm trying to set up for when
there are multiple queues in the future. Is it best to have one suite
of queue tables created per queue, or should we have one suite of
queue tables servicing multiple queues of which those queues are all
of the same queue_payload_type/multiple_consumers combination? So, we
might have multiple suites of queue table as well, but each suite
could service more than one queue. Or, maybe something just
completely different?

Setting the right context in callback function

Livio, October 09, 2012 - 1:04 pm UTC

Hello Tom,

I have setup the mechanism for asynchronous dequeue using PLSQL described at the beginning of the thread.
In the callback function, I have replaced the insert into message_table with a call to the procedure I want to be executed for any message:

create or replace procedure bs2020.notifyCB(
context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number)
as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aq.message_typ;
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

-- My procedure
my_pkg.p1(message.text);
-- End


COMMIT;
END;

Now, in turn, within my_pkg.p1, there is a call to procedure defined within another package:

other_pkg.replicate

and in turn, here is my problem, within replicate procedure
there is a call to dbms_session.set_context(lower(user)||'_ctx', 'descr', 'N');

The call raises an exception ora-01031 Insufficient privileges.

My dba claimed there is no way to grant the relevant privileges to my user, bs2020, to be allowed to execute the set_context. He claims I would have to modify the registration process to provide the right context:

BEGIN
dbms_aq.register
( sys.aq$_reg_info_list(
sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
DBMS_AQ.NAMESPACE_AQ,
'plsql://bs2020.notifyCB',
HEXTORAW('FF')) ) ,
1 );
end;

Is he right? And if so, how can I do that? Is there a way to issue the set context? I've googled all day but with no luck.

I hope I have provided a clear explanation.

Thank you in advance for any help you will provide

Kind regards

Livio Curzola

Tom Kyte
October 09, 2012 - 2:28 pm UTC

when you create the context "user_ctx" (you have to create it you know...) you bind it to a procedure then. You don't have to grant anything, you just have to create the context with the binding.

ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_session.set_context( 'xxx', 'x', 'x' );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "OPS$TKYTE.P", line 4
ORA-06512: at line 1


ops$tkyte%ORA11GR2> create context xxx using p;

Context created.

ops$tkyte%ORA11GR2> exec p;

PL/SQL procedure successfully completed.


Non-persistent Queue Example

AL Swamy, June 13, 2013 - 12:51 am UTC

Tom,

Can you please post some complete example on non-persistent queue as I couldn't find anywhere? We need this for one our projects.

Thanks a lot in advance.

AL Swamy

Pass Parameter to Callback Procedure

Tony, June 14, 2013 - 7:13 pm UTC

Tom,

I would like to use nonpersistent queue. How to pass message/parameter to callback procedure? As the messages are not stored in a table, I could not read them in my callback procedure.

Thanks a lot.

Tony

More to Explore

DBMS_AQ

More on PL/SQL routine DBMS_AQ here