Re: Oracle ignoring function-based index - why?

On 25 Jul 2005 09:34:48 -0700, "Anurag Varma" <avoracle@xxxxxxxxx>
wrote:

>Sybrand,
>
>Can you elaborate where oracle recommends a 20 percent value for
>estimate_statistics?
>I've seen oracle recommend using auto sample size. However, nowhere
>have I seen a number like 20% recommended.
>Also, the default for histogram is 1.
>http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003993
>
>"FOR ALL COLUMNS SIZE 1" does not gather any histogram. Its not
>ridiculous as you claim it to be. If OP does not want to gather any
>histograms than this value if fine.
>
>Also, your suggestion of a default 200 value for all indexed columns is
>ridiculous (I'm using your lingo now). Oracle suggests using "AUTO".
>Gathering histograms for
>columns which are not skewed will only harm performance. Also,
>non-indexed columns
>can benefit from histograms.
>
>Also, degree of parallelization is not necessarily dependent on number
>of CPU's. This has been debated a number of times in this forum. Try
>googling this.
>
>Anurag

As you may well know by now (as I have posted it many times) most of
our customers still run 8i, they have no intention to upgrade, and we
are not pressing them. In fact, it seems we are going to maintain a
'new' 7.3.4 database, which can't be upgraded because the client app
has phased out.
So you shouldn't criticize me for using 8i terminology, as you may be
aware 'auto' sized histograms simply don't exist in 8i.
The value of 200 is not ridiculous, as Oracle won't create 200 buckets
if it doesn't need to.

--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • Re: Table stats in 10.2
    ... The New Features Manual (or the New Features course in which this is ... the choice of columns needing histograms is AUTO ... The Co-operative Oracle Users' FAQ ...
    (comp.databases.oracle.server)
  • Re: Why index Scan in this case ?
    ... > analyze table t compute statistics for table for all tables for all ... > Whey you tried with histograms it pick the full table scan. ... > Oracle knows the distinct no of columns - Index Stats ...
    (comp.databases.oracle.server)
  • Re: Oracle CBO / Performance Issue !!!
    ... The app is quite complex and we have experienced some ... oracle back end performance issues. ... go check the histograms of columns that are ... no histograms is created on columns and CBO will automatically ...
    (comp.databases.oracle.server)
  • Re: DBMS_STATS
    ... Don't confuse Table Level statistics (implicitly including certain ... Column Level statistics) and Histograms. ... These would update Oracle with information on: ... Thus it is the last set of information that Oracle uses to identify ...
    (comp.databases.oracle.server)
  • Re: Advice on a weird query plan
    ... for the case of character-based histograms. ... just the first six BYTES of the character string, ... Oracle works. ...
    (comp.databases.oracle.server)