Re: Currval and buffer gets

The acess path knows about DUAL and that it will return only 1 row. Create any dual table under another user and you will see that it returns all rows.

Anjo.

"Khedr, Waleed" wrote:

> Do you know of any reason that keeps Oracle implementing dual the way they
> have now and its effect on all the other PL/SQL functions?
>
> Also I saw something on the Metalink where some one inserted many records in
> dual and when selecting count(*) from dual it returns the right count but
> when selecting any expression from dual it returns it only once.
>
> Also completely agree with:
>
> >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.
>
> Thanks
>
> Waleed
>
> -----Original Message-----
> Sent: Wednesday, April 24, 2002 11:59 AM
> To: Multiple recipients of list ORACLE-L
>
> 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).
> --
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).