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

Rachel,

In case you missed the mention earlier in the thread, go to http://www.hotsos.com/ and click the "Knowledge On-line" link and look for a paper called "When to use an index". Cary does an excellent job explaining why row selectivity is a totally invalid criteria. He then proceeds to demonstrate how index access may be valid for 100% selectivity, and how FTS access may be valid for 1% or less selectivity.

If you haven't seen it, it's a great read.

-Mark
On Tue, 2002-11-12 at 19:48, Rachel Carmichael wrote:
> last time I checked with an Oracle University instructor who I trust as
> knowledgeable, it was FTS if more than 5-8% of rows expected to be
> returned. This was 2000. These days, who knows?
>
> I don't go by the rules much anymore but by perception of performance
> and by explain plan analysis.
>
>
> --- "Fink, Dan" <Dan.Fink_at_mdx.com> wrote:
> > Of course, sacred cows make the best steaks (sorry, Gaja).
> >
> > An excellent example is the age old ideas that the earth was the
> > center of
> > the universe, that the world was flat, that the Cubs will never win
> > another
> > World Series (okay...bad example). Knowledge is limited by what we
> > can
> > currently test. We are always restricted by our physical world (I
> > don't have
> > a clue as to how I can personally test if the world is indeed round)
> > but
> > also by what we choose to accept as fact. What happens to indexing
> > strategies when disk reads are faster than memory access operations?
> > Before
> > you say, "It will never happen" think about it...Can you predict the
> > future
> > with absolute certainty?
> >
> > Even the 'experts' choose to accept certain facts. Look at the
> > scientific
> > world. Many of the most 'brilliant' ideas now can be proven false.
> > According
> > to the experts, we only need 5 computers worldwide with 64k of
> > memory.
> >
> > I checked by Data Server Internals texts from 1999 and they preach
> > 15% of
> > rows returned for indexing, and this series is certainly looked upon
> > as the
> > 'expert'. Anyone out there with a more recent version? I wonder what
> > it
> > says...
> >
> > IMHO, the bottom line is that many of us are so concerned with just
> > keeping
> > systems running that we have no time for our own personal research
> > and
> > development. Until I decided to write an article about rollback
> > segments, I
> > never applied the scientific method to my understanding of Oracle.
> > Will
> > application of the method explain everything? Nope, but it will come
> > close.
> > It requires a lot of time and hard work, something that is a precious
> > commodity these days, especially in the corporate world. I am very
> > grateful
> > for people like Cary, Tim, Anjo, Craig, Gaja, Kirti, et.al. who take
> > the
> > time to say "prove it!" and then perform the experiments and, most
> > importantly, are willing to share the results with us on this list
> > and at
> > meetings like IOUG-A.
> >
> > Dan Fink
> >
> > -----Original Message-----
> > Sent: Tuesday, November 12, 2002 2:24 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I think the question "Is nothing sacred?" is an interesting one. Lots
> > of
> > these things we're talking about have been false for a very long
> > time.
> > It's only that people are finally starting to notice them. Product
> > changes are often *not* what's driving "new knowledge." In many
> > cases,
> > the "change" that's taking place is the improvement in the quality of
> > our conclusions.
> >
> > "Is nothing sacred?" I think it's perfectly legitimate to confront
> > people's (and companies') conjectures with scientific data.
> >
> >
> > 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-----
> > dgoulet_at_vicr.com
> > Sent: Tuesday, November 12, 2002 10:19 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Jesse,
> >
> > No, nothing in sacred any more. Change is the theme of the day.
> > BTW: did
> > you experiment with caching these tables in the keep_pool?? I've had
> > some real
> > good luck with unindexed tables that are small (in the 1 to 10 block
> > size) that
> > get assigned to the keep pool and retained in memory forever.
> >
> > Also, BTW: I'll disagree with Cary and Hotsos on the costs of a
> > PIO
> > vs a
> > LIO. In my experience it's not such a clear cut distinction.
> > Whenever
> > Oracle
> > needs a block of data that data must be in memory which means that a
> > PIO
> > requires 2 LIO's to fulfill the request and on top of that there may
> > be
> > other
> > memory management routines that get called if an empty data block in
> > memory must
> > be created. All in all it's a very mixed bag that needs to be
> > considered case
> > by case. I believe that was one of the reasons Oracle allows us to
> > configure
> > the cache three ways. Static, seldomly changed tables in the keep
> > pool.
> > Large
> > constantly changing tables in the discard pool. Also to index or not
> > to
> > index
> > are no longer such clear cut item, especially with CBO which loves to
> > ignore
> > indexes.
> >
> > Dick Goulet
> >
> > ____________________Reply Separator____________________
> > Author: "Jesse; Rich" <Rich.Jesse_at_qtiworld.com>
> > Date: 11/11/2002 8:58 AM
> >
> > 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?
> >
> > 2) What other pearls of performance wisdom from Oracle Corp should I
> > completely disregard as false?
> >
> > 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.
> >
> > Maybe I'm grumpy because it's snowing on my leaves right now...
> > <sigh>
> >
> >
> > Rich
> >
> >
> > Rich Jesse System/Database Administrator
> > Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex,
> > WI
> > USA
> >
> > Disclaimer: I only said the Packers would be 12-4 this year -- I
> > never
> > said
> > that they couldn't do better! WOO-HOO! :)
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >
> === message truncated ===
>
>
> __________________________________________________
> Do you Yahoo!?
> U2 on LAUNCH - Exclusive greatest hits videos
> http://launch.yahoo.com/u2

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it
well."
 						-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: mark_at_bobak.net

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).