| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Latch Free / Shared Pool on Fetch
Just a couple of guesses
Do you have cursor_sharing set to force or similar ?
Is there a pl/sql function call or globally packaged variable in the SQL statement ?
Does the statement reference sys_context()
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
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=1FETCH #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_at_flash.net
214.954.1781
 |
 |