Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Is nothing sacred? (Oracle vs The Experts)

Re: Is nothing sacred? (Oracle vs The Experts)

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Mon, 11 Nov 2002 11:04:04 -0800
Message-ID: <F001.005003B5.20021111110404@fatcity.com>


"Jesse, Rich" wrote:
>
> So, there I am, on 8.1.7.2 (and .4) on HP/UX 11.0, with a process that runs
> 20 minutes out of every hour of the day (despite my protests to it's
> design). After it starts having problems (go figure), it becomes a priority
> to speed it up.
>
> Thanks to a 10046 trace, we see that the query taking the most elapsed time
> does FTSs on each of two very small tables (1 block and 4 blocks -- 8K
> blocksize). These tables are not indexed, as per the official Oracle
> recommendation. After reading the excellent Hotsos paper "When to index a
> table" (THANKS, CARY!), I added an index to reduce elapsed time on this
> query by 50% (150 to 75 seconds in test), proving to me that the paper is
> valid. And I've only read to page four!
>
> OK, first I'm taught by Oracle to look at Buffer Cache Hit Ratios as a
> measure of performance, then told (and thoroughly convinced) by experts that
> this is bunk. Now, I found out that the 15% (or 10% or whatever, depending
> on version) ratio of rows returned to total rows in determining when to use
> an index in a query is garbage.
>
> 1) Why is this?

  This ratio is something I have taught (with 'about') in version 5 days. This was a time when a 500M database was considered to be reasonably big. The bigger the table, the smaller the ratio - you see the problem with a 50 million row table. Add to this that there was nothing such as parallel queries nor partitioning, hash join, etc, etc, which have strongly improved full scan performance and further tipped the balance. You have a lot of numbers around which once were valid but have never been updated.

> 2) What other pearls of performance wisdom from Oracle Corp should I
> completely disregard as false?

  Anything preached by somebody, including myself, who did a thorough benchmark 12 years ago and didnt't retry it recently _may_ be false today. Oracle evolves, that's the trouble.  

> I know there's an Oracle Fallacy website somewhere...
>
> It just looks bad on me, our department, and Oracle when, once again,
> something I've been preaching to our developers as gospel turns out to be
> completely false.

 You'd better get accustomed to it :-).

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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: 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 Mon Nov 11 2002 - 13:04:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US