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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
