Gaja is correct. I had big problems recently with a highly concurrent
application selecting from DUAL too many times, resulting in an extremely
hot DUAL table. I wish I had knew this trick a few weeks ago. As it was, an
bit of application tuning sorted it out.
Jim
-----Original Message-----
Krishna Vaidyanatha
Sent: 24 April 2002 16:04
To: Multiple recipients of list ORACLE-L
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" <Waleed.Khedr_at_FMR.COM> 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
> <ORACLE-L_at_fatcity.com>
> 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: jonathan_at_jlcomp.demon.co.uk
>
> 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: 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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> 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: 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).
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: oraperfman_at_yahoo.com
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: 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: James McCann
INET: james_at_openet-telecom.com
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: 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 Wed Apr 24 2002 - 11:18:32 CDT
