Latch Free / Shared Pool on Fetch

Maybe a simple question, but I cannot find the answer, and I've been looking
for 24 hours now ;-)

Why is a shared pool latch taken when (between?) fetching rows from a query?
I have a trace file where once we start hitting the fetch lines, we are
seeing latch free waits on the shared pool (9.2.0.5, p2 of 156=shared pool).

#8 is the select statement, #1 is the begin ... end of the anonymous block
associated with "execute package.proc".

FETCH #8:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=684296371
WAIT #1: nam='latch free' ela= 138809 p1=60175956 p2=156 p3=0
FETCH #8:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=684503268
WAIT #1: nam='latch free' ela= 207973 p1=60175956 p2=156 p3=0
FETCH #8:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=684779497
WAIT #1: nam='latch free' ela= 138264 p1=60175956 p2=156 p3=0
WAIT #1: nam='latch free' ela= 70925 p1=60175956 p2=156 p3=1
FETCH #8:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=685059389
WAIT #1: nam='latch free' ela= 207427 p1=60175956 p2=156 p3=0
WAIT #1: nam='latch free' ela= 68432 p1=60175956 p2=156 p3=1
FETCH #8:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=685402671
WAIT #1: nam='latch free' ela= 278221 p1=60175956 p2=156 p3=0
FETCH #8:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=685747413
WAIT #1: nam='latch free' ela= 209616 p1=60175956 p2=156 p3=0
WAIT #1: nam='latch free' ela= 69029 p1=60175956 p2=156 p3=1

Anyway, it's just something I don't think I've seen before, and I haven't
been able to get an answer on why this occurs. Note that if I take the SQL
and run it just as SQL, then the cursor referenced by the FETCH and WAIT
lines are the same value.


Regards,

Larry G. Elkins
elkinsl@xxxxxxxxx
214.954.1781


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: