Mark,
I saw and read the thread and I've read the paper.
Dan was talking about how in '99 Oracle was preaching 15% and I told
what I had been told, by Oracle in 2000. Two years makes an awfully
large difference in what the party line is and becomes.
And I also said that I don't follow that as a rule, but work off
performance and the explain plan. If the performance dies with an index
usage and improves without it, I make sure the index isn't used FOR
THAT QUERY.
Rachel
- "Mark J. Bobak" <mark_at_bobak.net> wrote:
> 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
>
=== 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: wisernet100_at_yahoo.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 Tue Nov 12 2002 - 21:08:29 CST
