| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: METALINK and OWS
You are right, Rachel, and that was precisely my point. Even the most
intelligent optimizer can't have all the facts, or consider them. Physical
location of your data/index matters (on top of that, are they sitting in
large contiguous space? is the data sorted? ), and if you use outdated
statistics, of course you are going to throw the optimizer off, and it
doesn't even know that.
Michael
> Other than the fact that I don't think the optimizer takes into account
the
> physical location of your data and index tablespaces, nor does it care how
> stale your statistics are (it works with what it has, accurate or not)...
> yes, these are the things to consider
>
> Rachel
>
>
> >From: "Michael Sun" <mikeny31_at_speakeasy.org>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: METALINK and OWS
> >Date: Sun, 11 Jun 2000 20:25:45 -0800
> >
> >If you have time, a BIG if that is, set up different queries, passing
hints
> >to make sure differen execution plan will be picked up (and verified by
> >EXPLAIN PLAN and/or trace/tkprof), and time the performance using
> >DBMS_UTILITY.GET_TIME.
> >
> >What Rachel said, and in fact, recommended by Oracle is absolutely true.
> >However, the number Rachel refered to, as low as 3-5% or as high as 20%,
is
> >for a 'typical' set up. It all boils down to a 'simple' choice for the
> >optimizer to make : is it more efficient to do a full table scan or index
> >(unique, range) scan to find and return the rows you ask for?
> >
> >And that choice depends just 'a few' factors:
> >
> >1) How many rows the table has?
> >2) What is the avg_row_len(gth)?
> >3) What is your block_size?
> >4) What is your db_file_multi_block_read_count setting?
> >5) Do you have updated and accurate statistics on your table?
> >6) Do you have updated and accurate statistics on your index?
> >7) How uniformly is your indexed column data distributed? If not, do you
> >have histograms?
> >8) Where are you data and index tablespace datafiles physically located?
> >Will it cause excessive I/O contention when both are accessed at the same
> >time?
> >
> >I am sure there are a few factors here that I missed. But the message is
> >the
> >same, do your own homework. There is just some piece of information
nobody
> >knows better than you do. What do you think, Rachel?
> >
> >Michael
> >
> >----- Original Message -----
> >To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> >Sent: Sunday, June 11, 2000 8:14 PM
> >
> >
> > > WHOA! Slow down before you drop the indexes. Indexes are not totally
> > > useless! It very much depends on the queries you run against your
> >database.
> > > If you are doing a mixture of queries, where some return single rows
or
> >a
> > > very small subset of data, and some return a large number of rows, you
> >still
> > > should keep the indexes for the first type of query.
> > >
> > > Rachel
> > >
> > >
> > > >From: Nicholas Tufar <ntufar_at_chemist.gen.tr>
> > > >Reply-To: ORACLE-L_at_fatcity.com
> > > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > >Subject: Re: METALINK and OWS
> > > >Date: Sun, 11 Jun 2000 14:17:51 -0800
> > > >
> > > > >
> > > > > Nicholas,
> > > > >
> > > > > Sorry... I thought most people knew this. Oracle says that if a
> >query
> > > >will
> > > > > return more than 20% of the rows of a table, it will use a full
> >table
> > > >scan.
> > > > > So you DON'T tune it to use an index. In fact, as of 8i, I believe
> > > >Oracle
> > > > > now recommends that if a query returns as little as 3-5% of a
table,
> >a
> > > >full
> > > > > table scan is the way to go.
> > > >
> > > >A very valuable piece of information. How did I miss it!
> > > >Now I know what I am going to do on Monday. I had a feeling that
> > > >all those indeces are useless, now I know why they are.
> > > >Thank you very much.
> > > >
> > > >Nicholas
> > > >--
> > > >Author: Nicholas Tufar
> > > > INET: ntufar_at_chemist.gen.tr
> > > >
> > > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > >San Diego, California -- Public Internet access / Mailing
Lists
> > > >--------------------------------------------------------------------
> > > >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).
> > >
> > >
 |
 |