Agreed! No arguments there. I am all for designing and
writing scalable code from the starting block and
avoiding wastage of resources. But sometimes the
problem is much more complex. I am sure you have
inherited "bad application design" or "bad code"
enough times, and may I add "Not by choice". Add to
that, the inherent complexity of a production
environment, the options that you may have "on hand"
to fix a given problem could be limited.
We could all stand from a purist's perspective and
claim that every line of bad SQL needs to be
re-designed, re-written and re-optimized. That would
be perfect....in an ideal world. But that is easier
said, than done, in some cases.
In this forum, our goal is to try and provide
potential solutions to problems. In the final
analysis, it is upto the individual to seek out the
best alternative for his/her environment, based on
various factors such as time-to-implement, cost, risk
and benefit. And sometimes all it takes is a "cute
exercise" such as the one on X$DUAL, to fix a problem.
;-)
Regards,
Gaja
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:
>
> 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.
> |
>
>
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 - 16:33:25 CDT
