Hello Yechiel,
Not sure what you mean...I was referring to the "cache
buffers chains" latch in my note NOT the library cache
load lock.
Gaja
- Yechiel Adar <adaryechiel_at_hotmail.com> wrote:
> Hello Gaja
>
> I checked the report from YAPP and library cache
> load lock accounts
> only to 0.75% of the wait time. 28 seconds during
> 6100 seconds between
> snaps.
>
> Yechiel Adar
> Mehish
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 24, 2002 5:03 PM
>
>
> > 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
>
=== message truncated ===
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).
Received on Wed Apr 24 2002 - 15:43:23 CDT
