Re: Experience using dbms_alert or dbms_pipe?

tobbe_at_rt.ge.se (Torbjorn Backstrom) wrote:
>
>Hello netland,
>
>I have some tables that are updated infrequently (about every five
>minutes or so). When an update is done some external C-programs
>have to be triggered. I've solved this using the dbms_alert package,
>but I am considering using the dbms_pipe package instead.
>
>Does anyone have any opinions/recommendations/experiences concerning the
>dbms_alert package and the dbms_pipe package? I'm using Oracle 7.0.16.
>Are there any performance considerations to make (apart from the comments
>about parallell mode etc in the dbmsalrt.sql file)?
>
>What I am doing at the moment is something like this (using OCI):
>
>A C-program generates data irregularly and writes this data to one or
>more tables in the database. These tables have triggers on them, which
>in turn "raise signals", ie does the following in PL/SQL:
>
> ...after insert or update or delete on the_table
> begin
> dbms_alert.signal('the_table_alert', 'a_table_alert');
> end;
>
>Other C-programs subscribe on these signals using dbms_alert.waitany(...).
>
>Everything works, but sometimes it seems all tables get locked until
>there is a commit from the first C-program.
>

This is true, alerts are serial. If one application raises a named alert, no other session will be able to do so until that session either commits (and sends the signal) or rolls back (and does not send the signal).

Database pipes will not have this effect. They will however send the message as soon as the client application writing the message, writes it (pipes are NOT transactional). Therefore, the client application waiting for data to appear in the pipe, to signal it about new data in the table, will get the message before the commit occurrs. Additionally, it will get the message even if the data never appears (eg: the transaction causing the trigger to write on the pipe rolls back).

Alerts are good for very short transactions (since they will cause serialization). Pipes are good when you want to develop a RPC or send some data to another application (cause they are not transactional, they are sent immediately, and they do not serialize).

If timing is not important (your C program doesn't need to know immediately after the update was committed, just some reasonable time later), you may want to try something like the following:

1.) use triggers to insert the rowid of the newly updated rows into some little temp table.

2.) Let your C program wake up every couple of minutes and issue a "delete from THIS_LITTLE_TEMP_TABLE". Assume the THIS_LITTLE_TEMP_TABLE has a trigger that, after delete for each row, puts the rowids into a PL/SQL table. It then commits.

3.) Your C program looks at the sqlca.sqlerrd[2] variable to see if any rows were in fact deleted (if not, go back to sleep). If so, you have the rowids of all rows inserted into the real table. Do something with this.

This has the advantage that-
1.) clients won't serialize on inserts into the real table 2.) it is transactional (in comparision to pipes)

Its a 'disadvantage' in that

1.) your C program must wake up and 'poll' the database.

Lets get rid of the disadvantage.

Write a stored procedure that does something like the following:

create or replace procedure wait_for_data as
begin

   loop

      delete from THIS_LITTLE_TEMP_TABLE;
      if ( sql%rowcount <> 0 ) then
         commit;
         return;
      end if;
      commit;
      dbms_lock.sleep( 10 ); -- sleep for 10 seconds 

Now your C program just calls wait_for_data. When it returns, it accesses the rowids in THIS_LITTLE_TEMP_TABLE via the pl/sql table the trigger on that table populates.

Now, for something completely different. When you move to Oracle7.1 you will find a package called dbms_job. This package runs stored procedures in the background (it can also be called from triggers). Using dbms_job (which will NOT serialize) you will be able to queue your alerts to be executed in the background, right after you commit. So instead of the alert being fired right after you commit, a job will be executed right after you commit that fires the alert. Who cares if the job in the background executes in serial? The client applications won't serialize, the alert will be transactional, and all is well....

Hope this helps

Thomas Kyte
tkyte_at_us.oracle.com

>greetings, tobbe
>---
>Torbj|rn B{ckstr|m email: tobbe_at_rt.ge.se
>Gbg Energi AB / Teleteknik, Box 53 tel: +46 31 627361
>S-401 20 Gothenburg, Sweden fax: +46 31 190823
Received on Sun Jun 18 1995 - 00:00:00 CEST