RE: 3 round trips over network for simply "select sysdate from dual"

This is sqlplus which is sending multiple commands to server. It first closes lastly executed cursor, then sends bundled parse + exec and then does a fetch.  

Event 10051 allows us to trace OPI calls sent from client, I run a select statement twice:  

SQL> alter session set events '10051 trace name context forever, level 1';  

Session altered.  

SQL> select * from dual;  

D
-
X  

SQL> select * from dual;  

D
-
X

And trace file shows:  

• 2008-06-20 19:35:24.953
• SESSION ID:(122.1090) 2008-06-20 19:35:24.953
• CLIENT ID:() 2008-06-20 19:35:24.953
• SERVICE NAME:(SYS$USERS) 2008-06-20 19:35:24.953
• MODULE NAME:(sqlplus.exe) 2008-06-20 19:35:24.953
• ACTION NAME:() 2008-06-20 19:35:24.953

OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
OPI CALL: type= 5 argc= 2 cursor=  1 name=FETCH
 

• 2008-06-20 19:35:45.953 OPI CALL: type=105 argc= 2 cursor= 0 name=Cursor close all OPI CALL: type=94 argc=28 cursor= 0 name=V8 Bundled Exec OPI CALL: type= 5 argc= 2 cursor= 2 name=FETCH

--
Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/> 

 



  _____  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of qihua wu
Sent: Friday, June 20, 2008 17:14
To: oracle-l
Subject: 3 round trips over network for simply "select sysdate from dual"


Hi, all

When using tcpdump to check what happen over the network when do a simple
query as
SQL> select sysdate from dual;

SYSDATE
---------
20-JUN-08

The tcpdump is as following, 3 round trips between the server and client.
Why so many trips? Don't we only need to 1 round trip as client send the
query and the server reply?
     
16:56:13.354672 IP host1 > host2.1580: . 7898:8 054(156) ack 6755 win 32768
16:56:13.354683 IP host2.1580 > host1: . 6755:6 836(81) ack 8054 win 32768
16:56:13.521731 IP host1 > host2.1580: . 8054:8 126(72) ack 6836 win 32768
16:56:13.521744 IP host2.1580 > host1: . 6836:6 853(17) ack 8126 win 32768
16:56:13.688967 IP host1 > host2.1580: . 8126:8 141(15) ack 6853 win 32768
16:56:13.688980 IP host2.1580 > host1: . 6853:6 874(21) ack 8141 win 32768


And the expert says below: one additional roundtrip to initiate the query.
Don't quite understand why we need additional roundtrip to initiate.
http://www.adp-gmbh.ch/ora/sqlplus/arraysize.html

Thanks,
Qihua



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