Re: Does EXEC do a FETCH?

I don't think it's PRIOR to the fetch -- I'd interpret this as being "THE"
fetch.

From what I understand by reading 10046 trace files - all the events listed
belong to the next call that follows (for the same cursor) in the tracefile.

Therefore if you have

PARSE
WAIT 1
WAIT 2
EXEC
WAIT 3
WAIT 4
WAIT 5
FETCH
WAIT 6
FETCH

It basically means that waits 1&2 "belong" to the EXEC call, waits 3-5 to
the 1st FETCH call and wait 6 to the second FETCH call.

Stefan



On 7/13/07, Henry Poras <henry@xxxxxxxxxxxxxxx> wrote:

I've been spending some time on my system trying to do time accounting by
comparing output from sqlnet trace files and 10046 traces. While doing
this,
I've realized I don't understand what is  going on with the EXEC and FETCH
calls with simple SELECT statements. (this is on a 10.2.0.3 db RedHat
2.6.9)

I ran a simple test case which returned a single record. From looking at
the
10046 trace it appears as though the data is sent to the client before the
FETCH call. Huh? Is the initial FETCH  actually coupled with the EXEC and
just broken out in the trace file at a later time?

=====================
PARSING IN CURSOR #2 len=53 dep=0 uid=40 oct=3 lid=40 tim=1156580450666631
hv=1459047207 ad='9ae399a8'
select /* fetch test */ ename
from emp
where rownum=1
END OF STMT
PARSE
#2:c=6999,e=6990,p=0,cr=18,cu=0,mis=1,r=0,dep=0,og=1,tim=1156580450666627
BINDS #2:
EXEC #2:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1156580450666729
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=1156580450666769               <===
FETCH #2:c=0,e=59,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,tim=1156580450666858
<===
WAIT #2: nam='SQL*Net message from client' ela= 1253 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=1156580450668168
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1156580450668205
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=1156580450668226
*** 2007-07-13 10:53:12.998
WAIT #2: nam='SQL*Net message from client' ela= 11244427 driver
id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1156580461912676
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=5 pr=0 pw=0
time=55 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=48029 op='TABLE ACCESS FULL EMP (cr=5
pr=0 pw=0 time=43 us)'
=====================

So after the EXEC, a packet is sent to the client followed by a FETCH of
one
record (r=1). After getting confirmation back from the client (message
from
client wait), sqlplus does its final  FETCH confirming no more data, and
sends this to the client (message to client). The client then responds
with
the next sql to parse.

Looking at the sqlnet trace (level=SUPPORT), I see the following packets:

1. nspsend of the SQL  (after getting this, the PARSE should happen)
2. nsprecv of the result set (header information [ENAME], and the data
[SMITH])
    This is connected to the 'SQL*Net message to client' wait immediately
following the EXEC
3. nspend (plen=21). I assume this is confirmation of receipt of the data
packet
4. nsprecv of packet containing ORA-01403 no data found
5. nspsend of next SQL

So the data is sent from the server to the client prior to the initial
FETCH. The second FETCH confirms no more data and that is sent to the
client
after the FETCH.

I am wondering if the initial EXEC actually does EXEC and FETCH, and the
explicit FETCH call is just bookkeeping, not the real work. Maybe the
Oracle
kernal changed the way it does its  work, but the 10046 display hasn't
been
adjusted accordingly.

Has anyone else seen this? Are there any other explanations?

Thanks.

Henry

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





--
=========================

Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com

=========================

Other related posts: