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

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