| 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
Probably to update dynamic dictionary tables/views to reflect the real
time status/statistics for the running sql/cursor.
Like columns: fetches, executions, etc in v$sql
Just guessing!
Regards,
Waleed
-----Original Message-----
From: Larry Elkins [mailto:elkinsl_at_flash.net]=20
Sent: Wednesday, June 30, 2004 6:36 PM
To: oracle-l_at_freelists.org
Subject: 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=3Dshared
pool).
#8 is the select statement, #1 is the begin ... end of the anonymous
block
associated with "execute package.proc".
FETCH =
#8:c=3D0,e=3D99,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68=
4296371
WAIT #1: nam=3D'latch free' ela=3D 138809 p1=3D60175956 p2=3D156 p3=3D0
FETCH =
#8:c=3D0,e=3D91,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68=
4503268
WAIT #1: nam=3D'latch free' ela=3D 207973 p1=3D60175956 p2=3D156 p3=3D0
FETCH =
#8:c=3D0,e=3D94,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68=
4779497
WAIT #1: nam=3D'latch free' ela=3D 138264 p1=3D60175956 p2=3D156 p3=3D0
WAIT #1: nam=3D'latch free' ela=3D 70925 p1=3D60175956 p2=3D156 p3=3D1
FETCH =
#8:c=3D0,e=3D107,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D6=
85059389
WAIT #1: nam=3D'latch free' ela=3D 207427 p1=3D60175956 p2=3D156 p3=3D0
WAIT #1: nam=3D'latch free' ela=3D 68432 p1=3D60175956 p2=3D156 p3=3D1
FETCH =
#8:c=3D0,e=3D97,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68=
5402671
WAIT #1: nam=3D'latch free' ela=3D 278221 p1=3D60175956 p2=3D156 p3=3D0
FETCH =
#8:c=3D0,e=3D93,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68=
5747413
WAIT #1: nam=3D'latch free' ela=3D 209616 p1=3D60175956 p2=3D156 p3=3D0
WAIT #1: nam=3D'latch free' ela=3D 69029 p1=3D60175956 p2=3D156 p3=3D1
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org 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 -----------------------------------------------------------------Received on Wed Jun 30 2004 - 17:46:10 CDT
 |
 |