10046 trace question + ORASRP possible problem.

From: Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk>
Date: Fri, 30 Jun 2006 09:49:03 +0100
Message-Id: <s4a4f3a9.079@environment-agency.gov.uk>

Morning all,

quoting Cary & Jeff in "Optimising Oracle Performance", page 81 :

Note that WAIT lines appear in the trace data *before* the database call that motivated them.

I have the following trace extract (somewhat trimmed for clarity) from a 9206 database session :

PARSING IN CURSOR #1 len=157 dep=0 ... hv=1 SELECT stuff FROM table WHERE column = :bind_var END OF STMT
PARSE
#1:c=0,e=228,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3355551484648
BINDS #1:
...
EXEC #1:c=0,e=224,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3355551485040 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 301 p1=1413697536 p2=1 p3=0

WAIT #1: nam='db file sequential read' ela= 398 p1=25 p2=87117 p3=1
WAIT #1: nam='db file sequential read' ela= 6216 p1=25 p2=87118 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1

p3=0
FETCH
#1:c=0,e=6998,p=2,cr=3,cu=0,mis=0,r=4,dep=0,og=4,tim=3355551492476
WAIT #1: nam='SQL*Net message from client' ela= 509 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=4 pid=0 pos=1 obj=1660748 op='TABLE ACCESS FULL

A_TABLE_NAME '
WAIT #1: nam='latch free' ela= 100 p1=-4611686017037494072 p2=555 p3=0
WAIT #1: nam='latch free' ela= 47 p1=-4611686017037492344 p2=555 p3=0
WAIT #1: nam='latch free' ela= 179 p1=-4611686017037492344 p2=555 p3=0
WAIT #1: nam='latch free' ela= 107 p1=-4611686017037492344 p2=555 p3=1
WAIT #1: nam='latch free' ela= 3 p1=-4611686017037493208 p2=555 p3=0
WAIT #1: nam='latch free' ela= 11 p1=-4611686017037492632 p2=555 p3=0
WAIT #1: nam='latch free' ela= 205 p1=-4611686017037492632 p2=555 p3=1
WAIT #1: nam='latch free' ela= 94 p1=-4611686017037492632 p2=555 p3=0
WAIT #1: nam='latch free' ela= 108 p1=-4611686017037493496 p2=555 p3=0
WAIT #1: nam='latch free' ela= 135 p1=-4611686017037492344 p2=555 p3=0
=====================

PARSING IN CURSOR #1 len=199 dep=0 ...hv=2 SELECT more_stuff FROM another_table WHERE column = :bind_var END OF STMT
PARSE
#1:c=220000,e=222788,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=3355551716148
...

Would I be correct in thinking that the 10 WAITs on 'latch free' (ignore the P2 value, it has been fiddled - more on that later) actually apply to the PARSE stage of the statement with hv=2 and are nothing to do with the first statement ?

If so, the ORASRP version 2.1 is lumping the latch free waits above into the details for the first statemenmt rather than the second. I fiddled the latch number to see where ORASRP was puting the latch free details - it's actually the library cache latch (157) that is being waited on - 555 is just a dummy number I chose for the experiment.

If ORASRP is broken, this is interesting as the order that it outputs statements is not necessarily the same order as the input trace file, so the latch free waits for statement 'n' in the output may not apply to statement 'n' and cannot be assumed to apply to statement 'n-1' either - as the ordering is not as per the tarce file. Bummer.

If the above is totally wrong, then apologies to Egor in advance.

Cheers,
Norman.

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

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

Received on Fri Jun 30 2006 - 03:49:03 CDT