RSS Feed

Method R Discussion Group

Hi All,

looking at my test I concluded, that pipe get is a within database call wait event not a between database call wait event as stated on page 88 of "Optimizing Oracle Performance".
I would be grateful, if anyone could confirm this. Below is some evidence:
VARIABLE n NUMBER
EXEC :n:=dbms_pipe.receive_message('some_pipe', 10)

PARSING IN CURSOR #1 len=60 dep=0 uid=61 oct=47 lid=61 tim=220526713361 hv=1164329770 ad='6c7ab014'
BEGIN :n:=dbms_pipe.receive_message('some_pipe', 10); END;
END OF STMT
PARSE #1:c=0,e=170,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=220526713354
WAIT #1: nam='pipe get' ela= 1999245 handle address=1819288864 buffer length=4096 timeout=10 obj#=-1 tim=220528712877
WAIT #1: nam='pipe get' ela= 2999931 handle address=1819288864 buffer length=4096 timeout=10 obj#=-1 tim=220531712923
WAIT #1: nam='pipe get' ela= 3999890 handle address=1819288864 buffer length=4096 timeout=10 obj#=-1 tim=220535712943
*** 2007-11-26 11:33:12.640
WAIT #1: nam='pipe get' ela= 999889 handle address=1819288864 buffer length=4096 timeout=10 obj#=-1 tim=220536712951
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=220536713102
EXEC #1:c=0,e=9999667,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=220536713147
WAIT #1: nam='SQL*Net message from client' ela= 667 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=220536713941
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=220536714089

The ela value of pipe get is rolled up into e from EXEC #1 for execution of dbms_pipe.
Since WAIT #1: nam='pipe get' comes before EXEC #1:c=0,e=9999667 I would say it's clearly a within database call wait event.

Kind regards,

Norbert

PS: book reviewers for "Secret ORACLE" are still welcome: http://groups.yahoo.com/group/oracle_book_review

Yes, in this circumstance 'pipe get' is definitely within the context of the EXEC.

In the spirit of the book, however, we were referring to SQL statement executions, not PL/SQL block executions.

If I were to write that part of the book, then I would make that more clear.

I checked the only site I know of that lists errata and didn't find anything for this reference. Please visit http://www.oreilly.com/cgi-bin/errata.form/optoraclep to submit errata. It would be best if it comes from you and not me (or anyone else). After all, you found it.

Thank you for your response, Jeff.

There's no way to cause a "pipe get" wait by executing SQL, is there?

Norbert

Probably, but it wouldn't make sense.

If one of "my" staff created a function that uses dbms_pipe and then wanted to reference that function in, say, a select statement, then I would immediately fire that person and walk them out the door.