RE: dbms_pipe: what would cause a timeout?

We unfortunately use pipes extensively.  

NB, 90% of the information is supposition, if there may be better interpretations of my observations, so take with a pinch of salt.    

We are on 8.1.7.4 with a couple of one off patches.  

I suspect that one of the major causes of our problems is developer misuse with the size of the messages in the DBMS_Pipe being generally quite large.    

We have experienced problems with pipes failing when there have been general networking issues. (Which I don't understand because the sending process and receiving process are both attached to the same database so in theory the information in the pipe should never hit the network.  

We have experienced problems with sending messages into a pipe from a call from a forms application running in debug mode, which is 100% reproducible.  

And finally the major problem we experience is (probably related to the size of the messages) as DBMS_PIPES appears to store the queued messages in the shared pool we get issues whenever the shared pool ages out old query plans oracle seems to have problems managing the pipes, and the frequently become corrupted.    

(We then delete the pipe that our application then recreates)  

We have scripts that monitor the queue size from v$db_pipes (The pipes seem to have a natural size, but if they grow much over 2.5 times this natural size then we often seem to get problems - our natural size appears to be about 4K, but I would expect this to be application/workload/hardware dependent)  

We also monitor pipe related wait events:  

column machine format a17

column event format a8

column sid format 9999  

SELECT process unixprocess, machine, vs.program, vse.*

  FROM v$session_event vse, v$session vs, v$process vp

 WHERE vs.paddr = vp.addr

   AND vse.sid = vs.sid

   AND event like 'pipe%'

   AND total_timeouts != 0

 ORDER BY total_timeouts DESC  

Get waits being normal, put waits (unless transitory) indicating a problem.    

And I suspect that soon we will have to start culling stray pipes that seem to have started to build up since we started running the database 24/7.    

Our 'solution' (and it will make you wince!) is to size the shared pool so that it can accommodate a days SQL execution plans and flush the shared pool overnight!!    

Regards,  

David Roberts    

David Roberts
 www.logica.com  

Logica UK Limited
Registered in England and Wales (registered number 947968) Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL, United Kingdom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charles Schultz Sent: 07 October 2008 20:40
To: oracle-l_at_freelists.org
Subject: dbms_pipe: what would cause a timeout?  

Good day, list,

What would cause a call to dbms_pipe.send_message to timeout?

We have an ERP-delivered procedure that "pings" a few public ERP pipes in Oracle 10.2.0.3 running on Solaris 8. I tried tracing (event 10046) but did not find much, and trying to drill down via OEM was difficult at best. Could be me. =) I am trying to understand what would cause a timeout, whether it be a database problem, and if so, where. Not much documentation about pipe-specific problems out there in the wild.

-- 
Charles Schultz



This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.


--
http://www.freelists.org/webpage/oracle-l