All, I think the issue of using SYS.DUAL vs. X$DUAL is much beyond just "response time". It is more related to "easing a potential bottleneck" in your database, in a production environment supporting multiple sessions. Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs (in Oracle9i for every access to SYS.DUAL, the issue then boils down to the contention for the "cache buffers chains" latch to access blocks in the database buffer cache. So just because it is only 5(3) LIOs, that does not make it OK.
If your application is using SYS.DUAL "like there is no tomorrow", the cache buffers chains latch becomes your single point of contention. This is true, even if you have _DB_BLOCK_HASH_BUCKETS set to a value higher than its default. As Cary has mentioned many times before, the problem here is "application serialization". For more on this subject, please read Cary's papers "Why a 99%+ buffer cache hit ratio is NOT Ok" on http://www.hotsos.com/catalog and a recent paper at IOUG-A Live 2002 which talks about some common "Misunderstandings about Oracle Internals". Best regards, Gaja --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote: > Kevin and Jonathan, > > Thanks for the explanation. It's weird for me that > Oracle is still > maintaining this kind of dependency between the SQL > and PL/SQL engines for > minor sql functions. Also regarding the dual and > x$dual, it does not sound > good to me that Oracle still is implementing dual as > a table segment even in > Oracle 9i. > > I would give Gaja all the excuses to recommend using > something else other > than sys.dual to overcome this limitation. > > But on the other hand the difference in performance > and the over all gain is > too minor to use x$dual (look at the test below). > > Modifying the code and changing the design (or even > tuning one sql) would be > more promising. > > Thanks everybody, > > > Waleed > > > declare > nn number; > ss1 date; > ss2 date; > begin > ss1 := sysdate; > for i in 1..100000 loop > select 2 into nn from sys.x_$dual; > end loop; > ss2 := sysdate; > dbms_output.put_line('run time using view x_$dual in > centiseconds='||(ss2 - > ss1 ) * 24 * 60 * 60 * 100); > > --- > ss1 := sysdate; > for i in 1..100000 loop > select 2 into nn from dual; > end loop; > ss2 := sysdate; > dbms_output.put_line('run time using table dual in > centiseconds='||(ss2 - > ss1 ) * 24 * 60 * 60 * 100); > end; > > -----Original Message----- > Sent: Tuesday, April 23, 2002 6:18 PM > To: Multiple recipients of list ORACLE-L > > > It's a change that also made it into 8.1.7.3 > (or possibly 8.1.7.2) - check in > > $ORACLE_HOME/rdbms/admin/standard.sql > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to 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: 23 April 2002 22:05 > > > |I did in 8i (8.1.7.3) and did not see what you > said: > | > |alter session set sql_trace = true > | > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Khedr, Waleed > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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). ===== Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __________________________________________________ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
