Great Tom,

Your solution worked perfectly.

Thanks a lot.

Could you or the poser of the question kindly explain
why there would be a serialization here in the first
place? How can inserts cause serialization and locking?

that explains it!!

Hi Tom,

I'd be interested in your thoughts on the performance of the following alternative way of "scaling" dbms_alert.

1. Create a sequence starting with 1, going to 100 and cycling.

2. Have the signalling process signal "ALERT_NAME_" || current sequence value;

3. Have the listening process register for all alerts between "ALERT_NAME_" || starting sequence value and "ALERT_NAME_" || ending sequence value.

Is there any overhead in managing a large number of alerts like this?

My thought was that this allows the alert to be sent "real-time" [rather than with a small delay due to dbms_job - how long is this delay?] but allows you to scale the number of concurrent inserts on the triggering table to whatever level you like [theoretically].

I'd benchmark this, but I'm not sure where I should be looking for contention in this scenario. Do you forsee any problems?

Tom,

For parcelling out the numbers here is what I thought

create sequence my_alert_seq start with 1 maxvalue 100 cycle
/
create or replace procedure send_scalable_alert
is
lsequenceval integer;
begin
--fetch the sequence number
select my_alert_seq.nextval into lsequenceval from dual;

--send an alert using the seuqence number as part of the name
dbms_alert.signal('MY_ALERT_' || lsequenceval,null);
end send_scalable_alert;
/

create table busy_table (
id integer
)
/

create or replace trigger busy_table_bi
before insert on busy_table
for each row
begin
send_scalable_alert;
end;
/

create or replace procedure scalable_alert_listener
is
begin
--register for all the alerts
for i in 1..100
loop
dbms_alert.register('MY_ALERT_' || i);
end loop;

--and wait for any of them
--dbms_alert.waitany would go here.
end scalable_alert_listener;
/

Now I'm quite happy to believe I'm not understanding this right :-) but I though this would mean I could have 100 people concurrently inserting into busy_table without blocking [as they would each get a unique alert identifier].

Although I agree with you that AQ is probably the way to go if you get to the point where your alert system is overwhelmed, I was wondering if there are any actual scalabilty problems with this approach [since the sequence could be expanded to any number you wanted].

Your thoughts would be appreciated as I don't know how dbms_alert works in terms of latches, etc.

using your idea of placing the signal inside of the job take to long. It takes around 3-15 seconds. I do not need any fancy message, just has to be fast and not lockup if someone else calls the same signal.

orignal method:
declare
l_job number;
begin
dbms_job.submit( l_job, 'dbms_alert.signal( ''alert_1'', null );' );
end;

new method:
declare
l_seq number;
begin
-- get a new id from a sequence
l_seq:= signal_list.nextval
dbms_alert.signal( 'alert_'||l_seq, null );
end;

Now the other user to wake up will be:
dbms_alert.waitany(vmsg);
if substr(vmsg,1,5) = 'alert' then
do_process;
end if;
go_back_to_sleep...;


using the sequence makes the alert name unique. What do you think?

I was playing with the AQDEMO00 - AQDEMO06 in the demo RDMBS directory.  After installing the script I tried to drop the objects.  I get the following error.
SQL> Set Echo on
SQL> BEGIN
  SYS.DBMS_AQADM.STOP_QUEUE 
   (
    QUEUE_NAME => 'AQUSER.INPUT_QUEUE'
   );
  SYS.DBMS_AQADM.DROP_QUEUE 
   (
    QUEUE_NAME => 'AQUSER.INPUT_QUEUE'
   );
END;
ORA-24026: operation failed, queue AQUSER.INPUT_QUEUE has errors
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2822
ORA-06512: at "SYS.DBMS_AQADM", line 235
ORA-06512: at line 2


Searching metalink I found that Oracle 8.1.7 has a bug and sometimes can not drop queues.  I need to add some patches to my db.