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" <[EMAIL PROTECTED]> 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----- > > [EMAIL PROTECTED] > > 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" <[EMAIL PROTECTED]> > > 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 > > [EMAIL PROTECTED] 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 [EMAIL PROTECTED] "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: [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).
