Re: Trigger to DBMS_PIPE Gotcha's

On Thu, 23 Sep 1999 21:42:28 GMT, r16668_at_email.sps.mot.com wrote:

>All,
>
> I have written a ProC application with a function to dynamically
>generate a trigger on a table and write the results to dynamically named
>DBMS_PIPE which is being polled by the same application. The ultimate
>customer of the data is a realtime factory control system. My goal is
>to create a way to subscribe to data events in our "legacy" systems that
>use Oracle. My application works great in a test environment, but
>before I take it to my customers I need to be able to describe how I
>have addressed all the gotcha's out there, and how I won't be killing
>the performance of their Oracle based applications.
>
>The triggers that are generated simply read the data from either the
>:new or :old records formatting a string as it goes and writing the data
>to the pipe. The triggers are "after" triggers, so I am not trying to
>get in the middle of transactions.
>
>My Pro C reader is a single threaded tcl program. It polls each pipe it
>is listening to one at a time and forwards the results to the realtime
>system. The polling period is 1 second. There is a 2 second delay
>between polls if no data was found on the previous poll. I poll in a
>loop rather than block because the reader must also be somewhat
>responsive to asynchronous requests for new triggers to be set up.
>
>If I have lots of triggers, say 5 - 10, would it be more or less
>efficient to have 5 -10 pipes or just one pipe, from the Oracle side.
>
>What ever I do has to work with Oracle 7.3.x and be OS independent. I
>built it on an HP and haven't thought about NT yet.
>
>I'm not a dba or expert with oracle at all so I expect to be missing
>something.
>
>C-
>

It would probably be more efficient to use one pipe but if it is necessary to keep the information from each pipe seperate, it should be OK to leave it as multiple.

Your polling loop may be a bigger problem. We have a (remotely) similar setup but use an Oracle alert to signal the listening app that something is on the pipe. According to the Oracle documentation alerts do not use a polling mechanism and are therefore more efficient.

It may require some re-work to implement, but you might want to investigate using alerts.

--
Mark Wagoner
To reply, remove no.spam from my e-mail address