X$dual and Dual: performance and others?

From: chao_ping <chao_ping_at_vip.163.com>
Date: Wed, 25 Dec 2002 23:48:45 -0800
Message-ID: <F001.0052226F.20021225234845@fatcity.com>

Hi, dba friends:         

        I trussed some process execution in my system, and get result like:

oracle_at_main-db1$truss -c -p 3995
^Csyscall seconds calls errors
read .47 6963
write .48 6555
lseek .01 402
kill .00 2
times .61 25964
semsys .01 44
sigprocmask .00 92
context .00 23
yield .00 52
setitimer .01 46
pread .04 162

• --- --- sys totals: 1.63 40305 0 usr time: 11.89 elapsed: 129.88

	Also, from somewhere , i see someone's trick about xdual vs dual, and i did a benchmark:
	with dual:

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

        and with Xdual:

select * from v$mystat where statistic#=12;

       SID STATISTIC# VALUE
---------- ---------- ----------

        15 12 7
 declare mydate date;
 begin for x in 1..1000000 loop select sysdate into mydate from xdual; end loop; end; /

PL/SQL procedure successfully completed.

Elapsed: 00:03:34.00
select * from v$mystat where statistic#=12;

       SID STATISTIC# VALUE
---------- ---------- ----------

        15 12 12822         

	Time elapsed reduced about 40%, and cpu utilization reduced from  39830 to 12805.(less than half of the original value).
	I wonder if i can really replace the public synonym dual with my dual, I can reduce cpu usage significantly, right? But has anyone did this trick in production? any experience?
	Thanks.

Regards
zhu chao
msn:chao_ping_at_163.com
www.cnoug.org(ChinaOracle User Group)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.com

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 - 01:48:45 CST