Re: Currval and buffer gets

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
> > 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: Yechiel Adar
  INET: adaryechiel_at_hotmail.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).