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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
