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

It doesn't matter too much in the context of round trips from the application client to the database. A dbcall executed on the client still has to be passed over to the server, and the answer passed back. Oracle Corporation has done an excellent job bundling multiple db-side functions into one OCI call, but no matter what amount of clever work reduction ultimately gets done on the db side of the network (e.g., parse call becomes "merely a 'soft' parse," table query becomes "merely a 'virtual' table query"), the round-trips of communication between the client and the server are bound to happen at a frequency dictated by the person who has written the code for the application side of the configuration.

Cary Millsap

http://carymillsap.blogspot.com
http://method-r.com
http://www.prweb.com/releases/2008/05/prweb839554.htm

On Fri, Jun 20, 2008 at 7:28 AM, Howard Latham <howard.latham_at_gmail.com> wrote:

> I seem to remember something about Dual bing a virtual table?
>
>
> On 20/06/2008, Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
>>
>> 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
>>
>>
>> ------------------------------
>> *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
>>
>>
>
>
> --
> Howard A. Latham
>

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