I see you've started two threads on SYSDATE at once.
A couple of thoughts (rhetorical questions, not criticisms)
a) Does your benchmark actually mean anything to YOU ?
Are you really doing lots of
select sysdate from dual
in your application ?
If so why do you want sysdate that often ?
If you do need it, why do it this way ?
Unless the test mirrors the actual activity of your
system, then it is meaningless.
For example, as another way of testing DUAL/X$DUAL
try:
for i in 1..10000 loop
execute immediate
'select ' || i || ' from dual/xdual'
into m_var
end loop;
You should find for this type of work, you version of XDUAL
is a LOT more expensive on CPU and latching than DUAL.
b) Generally, playing games with x$dual and any X$ is
not a strategic move - if your problem relates to the
fact that
m_date := sysdate;
turns into
select sysdate into m_date from sys.dual
under the covers, then you'll find that your "clever
trick" in 8.1 turns into a penalty in 9.2 where the
SYSDATE call at the server turns into a C call to
the time - and maybe you've managed to rig things
so that your code is still doing a SELECT where
everyone else is doing a C call.
(There are lots of other things, though, which pl/sql
still turns into select's from dual). I have toyed with
the idea from time to time of turning DUAL into an IOT
to reduce the logical I/O a bit - but even so, the library
cache latching et. al. is likely to be more significant
than the logical I/Os.
BTW - it helps if you put a name to the statistic# - and
a version of Oracle. The numbers do change from time
to time.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 26 December 2002 08:50
>SQL> select * from v$mystat where statistic#=12;
>
> SID STATISTIC# VALUE
>---------- ---------- ----------
> 17 12 19907
>
>Elapsed: 00:00:00.00
>SQL> declare mydate date;
> 2 begin for x in 1..1000000 loop select sysdate into mydate from
dual; end loop; end;
> 3 /
>
>PL/SQL procedure successfully completed.
>
>Elapsed: 00:05:32.08
>SQL> select * from v$mystat where statistic#=12;
>
> SID STATISTIC# VALUE
>---------- ---------- ----------
> 17 12 39830
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
