Re: dbms_stats default

>For wide tables, I saw a 3X increase in response time when gathering
stats
>for all columns in the table vs just those that are indexed.

Could you clarify this - did the stats gathering take three times as long or did the subsequent queries take three times as long ?

If you collect massively more column stats, it should not be a surprise to find that all the extra sorting and counting adds time to the stats collection.

If you have the column stats, your queries MAY run quicker, they MAY run slower.

The drawback to collecting REDUNDANT column stats is that they have to be loaded into the dictionary cache, and they have to be considered when the query is optimised. This waste shared pool space and CPU.

On the other hand, if the statistics add value about the data - which does mean EVEN on unindexed columns - queries can run faster.

Consider the unindexed predicates:

        date1 between 1st Jan 2003 and 31st Jan 2003 and date2 between 1st Jan 2002 and 31st Jan 2002

Assume that almost all the data has date1 in Jan 2003, and date2 in Dec 2002. In this case it would be more efficient to run the date2 test before the date1 test, as this would filter out all but a very few rows for the second (CPU costly) test. (9.2 execution plans can show this type of calculation taking place, by the way)

Bottom line - most systems should have only a few column histograms in place, but there is no obvious requirement that they should only be on indexed columns.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





John Darrah wrote in message ...


>I use indexed columns and non-indexed columns in WHERE clauses. My

question


>is, for non indexed-columns, is gathering stats on density low val

high val


>and distinct values going to have any affect on the access plan

generated?


>It seems unlikely that it would since Oracle has 1 and only 1 way to

access


>that data, through a table access. The reason I want to know this is

because


>using the default method produces sql similar to the following:

>

>SELECT count(*),count(col1),count(distinct

>col1),min(col1),max(col1),count(col2),count(distinct

col2),min(col2),...


>

>For wide tables, I saw a 3X increase in response time when gathering

stats


>for all columns in the table vs just those that are indexed.