Re: JBoss keeps sending select 'hello' from dual

> From: "Niall Litchfield" <niall.litchfield_at_gmail.com>
>
> select <something> from dual
> is about as optimized as you can get these days - you'll be extremely
> unlikely to find a lower impact statement. The most optimized statement is
> still the one that you don't execute, but if you do have to execute one
> select <something> from dual is about as good as you get. I don't imagine
> that swapping to pl/sql will be lower impact!

I tested in 10.2.0.4 on my laptop (XP SP2). Local connection. No special setting in the database. SQL*Plus uses arraysize 100. I use my "Delta sesstat" script (http://yong321.freeshell.org/oranotes/SQLsFreqUsed.txt) and monitor all changed stats in v$sesstat. Stats not changed are not shown.

select 1 from dual;

NAME                                   OLDVAL     NEWVAL       DIFF
-------------------------------------- ------ ---------- ----------
SQL*Net roundtrips to/from client          70         73          3
bytes received via SQL*Net from client   9449       9644        195
bytes sent via SQL*Net to client        11080      11491        411
calls to get snapshot scn: kcmgss        2404       2405          1
execute count                             617        618          1
opened cursors cumulative                 497        498          1
parse count (total)                       351        352          1
user calls                                 99        103          4

begin null; end;
/

NAME                                   OLDVAL     NEWVAL       DIFF
-------------------------------------- ------ ---------- ----------
SQL*Net roundtrips to/from client          73         75          2
bytes received via SQL*Net from client   9644       9826        182
bytes sent via SQL*Net to client        11491      11629        138
execute count                             618        619          1
opened cursors cumulative                 498        499          1
parse count (total)                       352        353          1
session cursor cache hits                 326        327          1
user calls                                103        106          3

The result is very reproducible. It seems to favor begin null; end; slightly. I then tested

declare n number;
begin
 for i in 1..100000 loop
  select 1 into n from dual;
 end loop;
end;
/

versus the same loop except only replacing the query line with null. The difference is huge, with much more statistics shown in the query case. It overwhelmly favors the null statement PL/SQL block.

Yong Huang       

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