Re: Currval and buffer gets

I think there is a completely different level at which to view this issue.

If your application is using dual "like there is no tomorrow" then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU.

Sure, it's cute to play with replacing DUAL with a view called DUAL on x$dual - or playing slightly more safely by recreating DUAL as an IOT, but if you are hammering DUAL, it probably won't be the CBC latch on dual's bucket that is the problem.

BTW - counter-example for anyone thinking of using a view.

If your developers decide that they will get Oracle to do all the arithmetic to avoid problems of IEEE rounding or some such issue. and have millions of lines like:

    select 2.4 * 5.1 from dual;
    select 18.7 / 2.1 from dual;
    select 1 + 1 from dual;

You will really kill the system, because every time you hard-parse a statement containing a view, Oracle re-executes a recursive query like:

    select text from view$ where rowid = ...

(Believe it - it has been done).

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

|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.
|

-- 
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).