RE: full-scan vs index for "small" tables

Mladen,  

I'm going to stand firm on the following statement:  

Work first to reduce the biggest response time component of the business's most important task.  

Everything else follows from that. But in order to know what your most important task is, you have to communicate with your business. And in order to know what that task's biggest response time component is, you have to measure your specific circumstance.  

The objection I have with the world at large is when people guess instead of measure. Most people guess "It's got to be I/O." But "it" is I/O in fewer than 5% of cases I've witnessed since about 1995.  

So I find the "It's got to be I/O" guess to be extremely dangerous, first because it's the most common guess, second because it's erroneous a surprisingly large proportion of the time.  

Of course, if "it" is really I/O-and you know it to be so because you've accurately measured-then by all means dive into the I/O problem with all four feet.  

But only if (that is, after) you've determined that I/O is the biggest response time component of the business's most important task.  

My point ends with, "Why guess... When you can know."    

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba  

Hotsos Symposium 2007 / March 4-8 / Dallas

Visit www.hotsos.com for curriculum and schedule details...  

-----Original Message-----
From: Mladen Gogala [mailto:gogala_at_sbcglobal.net] Sent: Tuesday, June 27, 2006 8:34 AM
To: Cary Millsap
Cc: oracle-l_at_freelists.org
Subject: Re: full-scan vs index for "small" tables  

On 06/27/2006 09:03:33 AM, oracle-l-bounce_at_freelists.org wrote:

> On a slightly modified topic, for a long time I've had a problem with
this section 13.5.1.2.3 of the Oracle documentation. It's one of those sections that was written 25 years ago and apparently never subjected to scientific scrutiny.

>

> The part about "...which can be read in a single I/O call..." is one
of those myths that makes sense when you hear it, but it's just not true. An index scan of a 1-row, 1-block table is more efficient than a full table scan of that table. Try it.

>

> Performance of an Oracle database is NOT uniquely determined by how
many OS read calls your application causes it to make.

>
 

Cary, here I have to disagree with you. Performance is not uniquely determined,

but, apart from sleeping and waiting for an event, physical I/O is the slowest

thing that an application can do. If the pathological cases of endlessly waiting

for locks are eliminated, the performance of an application will have, at least

according to my own experience, a direct correlation with the number of performed

physical I/O requests. Spinning in memory is relatively rare and can be constructed

Connor McDonald's sinister "hit ratio adjusting tool" but I didn't see to many of

it in real life. Tuning response time is an extremely sound methodology which essentially

dictates going after the part of application where the application spends most of the

time, but in "real life" in the computer room (contradiction in terms, I know), cutting

down on the number of physical I/O requests will usually have an extremely beneficial

impact. It's a common wisdom which served me well, throughout my career.  

-- 

Mladen Gogala

http://www.mgogala.com

 


--
http://www.freelists.org/webpage/oracle-l