Re: X$dual and Dual: performance and others?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 26 Dec 2002 09:53:38 -0800
Message-ID: <F001.0052255A.20021226095338@fatcity.com>

I see you've started two threads on SYSDATE at once. A couple of thoughts (rhetorical questions, not criticisms)

1. 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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).

Received on Thu Dec 26 2002 - 11:53:38 CST