RE: dbms_pipe: what would cause a timeout?
- From: "Roberts, David (GSD - UK)" <david.h.roberts@xxxxxxxxxx>
- To: <sacrophyte@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Oct 2008 11:01:42 +0100
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charles Schultz
Sent: 07 October 2008 20:40
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
