HOEKSTRA.CO.UK

Famous Posters

hoekstra.co.uk

Oracle DBMS_PIPE - why you should abandon it

A classical method of getting Oracle-based processes communicating with the outside world is by using the DBMS_PIPE package: Typically, we write to a pipe from an Oracle PL/SQL-based process, and then we continuously run an asynchronous Oracle PL/SQL process which reads the pipe and processes what it reads. A common use of DBMS_PIPE is for debugging, logging and launching external processes.
Don't get your hopes up though - this is not a UNIX or a WINDOWS O/S pipe - this pipe strictly remains within the Oracle environment. And in my experience it is not very robust, which is a bad thing in the line of business that I am in.

Here is some example code that writes and then reads data over an Oracle DBMS_PIPE.
First, we write to a pipe:
 
#!/usr/bin/ksh
# Write message to pipe 'P'
sqlplus -s / <<!
var i number
dbms_pipe.pack_message('$1');
-- this creates the pipe if it does not already exist
exec :i:=dbms_pipe.send_message('P',10); -- message a lifetime of 10 seconds
!

Now, we read from the pipe: 
#!/usr/bin/ksh
# Reads pipe and outputs it STDOUT
sqlplus -s / <<!
set echo off
var i number
var s varchar2(250);
exec i:=dbms_pipe.receive_message('P',0);
exec dbms_pipe.unpack_message(s);
print s
!

Over time you will discover that no matter how large you make the DBMS_PIPE buffer and how short you set the message life time, after a while (days, weeks, ...) the DBMS_PIPE buffer fills up and blocks any process that wants to write to it. The only way to unbock the pipe was to 'let the pipe run out', by repeatedly running the reading (second) process and not pumping any more data down the pipe by not running the writing (first) process. After that all is well until a few days later again...

I am experiencing this on Oracle 9i and looking at the identical documentation of Oracle 10g, expect the same iniquitous behaviour. Useless attempts at solving this problem have been to:

  • Strategically place some dbms_pipe.reset_buffer statements in the writing program,
  • Liberally sprinkle dbms_pipe.purge statements in the reading programs
  • Deviously splice dbms_pipe.remove_pipe followed by dbms_pipe.create_pipe  commands in the writing and reading programs

None of these things made the pipe behave any better. I suspect that the problem lies in the implementation of the message aging mechanism -  unwinding a blocked pipe gives me messages that were created days ago, when in reality they should only persist, as in the example above, for 10 seconds.

Note that there is no locking - advisory or otherwise - between writing and reading processes sharing a DBMS_PIPE pipe, so you are not prevented from ripping out a pipe with a dbms_pipe.remove_pipe command while another processes is still reading or writing to it.

In  O'Reilly's book Oracle Built-in Packages, Steven Feuerstein (for we are not worthy) bravely attempts to explore and then to explain the hidden behaviours of the DBMS_PIPE package and this is so far the best explanation to found anywhere. After reading the chapter, I was finally convinced that this DBMS_PIPE-thing was designed on the back of a matchbox.

I have now changed my signalling strategy for inter-process signalling in Oracle by using:
  • External procedures (read about them here
  • Writing to a real file from Oracle using UTL_FILE which I will tail in a similar vein to the common UNIX $ tail -f real-OS-file


The latter approach works so well that I have created a number of shell scripts that execute processes on the Oracle database and show any of  Oracle's output that it writes to the real log file while the process is executing:


#!/usr/bin/ksh
# kill tailing process
trap kill -9 %1 > /dev/null 2>&1 && exit INT TERM HUP EXIT
# Start tailing the log file from NOW
tail -0cf $APP_HOME/log/events & 
# The process on Oracle to execute:
sqlplus -s / <<!
set feedback off serveroutput on size 100000 format wrapped
exec  oracle_stored_procedure(${1},${2});
!
sleep 1 # Give time for tail to poll log file and output
kill -9 %1 > /dev/null 2>&1 # kill tailing process

Note that other processes' output will also appear in your output. If this causes confusion, grep for the process name in the line with the tail command.

I could have used dbms_output in the Oracle stored procedures instead of writing to an events file, but as we all quickly learn, dbms_output is synchronous and therefore does not give you running updates of what is happening until the process that it is in has completed.

© Gerrit Hoekstra