Greg,
I believe that the cultural root cause of the excessive LIO problem is
the conception that physical I/O is what makes databases slow. Disk I/O
certainly *can* make a system slow, but in about 598 of 600 cases we've
seen in the past three years, it hasn't. ["Why you should focus on LIOs
instead of PIOs" at www.hotsos.com/catalog]
The fixation on PIO of course focuses people's attention on the database
buffer cache hit ratio (BCHR) metric for evaluating efficiency. The
problem is that the BCHR is a metric of INSTANCE efficiency, not SQL
efficiency. However, many people mistakenly apply it as a metric of SQL
efficiency anyway.
Of course, if one's radar equates SQL efficiency with the BCHR's
proximity to 100%, then a lot of really bad SQL is going to show up on
your radar wrongly identified as really good SQL. ["Why a 99% buffer
cache hit ratio is not okay" at www.hotsos.com/catalog]
One "classic" result is that people go on search and destroy missions
for all full-table scans. They end up producing more execution plans
that look like this than they should have:
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
This kind of plan produces great hit ratios because it tends to revisit
the same small set of blocks over and over again. This kind of plan is
of course appropriate in many cases. But sometimes it is actually less
work in the database to use full-table scans. ["When to use an index" at
www.hotsos.com/catalog.]
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas
-----Original Message-----
Sent: Friday, November 15, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L
A while back someone mentioned that the two main causes of slow SQL are
excesive LIO's and excesscive database calls, which show up as excessive
CPU
use and excessive network traffic, respectively.
Regarding the database calls, is there a "classic" reason for this
problem?
My best guess is it's caused by an SQL statement in a PL/SQL loop, which
could be rewritten as a single SQL statement. But is this the single,
commonly seen cause for this problem, or are there other common ways
this
inefficiency is introduced?
Thanks in advance for help in understanding this.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Cary Millsap
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
