Re: Histograms Used to Select Best Index, or Only Between Index and FTS?

In general I advise quite strongly against having histograms on all columns.

Redundant histograms (which is most of them) waste space on disc - have to be loaded into the buffer (which is a wasteful activity with a side-effect on the rest of the buffer) - then have to be transferred to the row cache (which wastes space in the SGA) - then they have to be examined during every optimisation pass - which increases latch activity and wastes CPU.

Of course, in a well-run site which does very little optimising because of extremely well-controlled SQL, the overhead will not appear often and can then be ignored. Others may suffer.

By the way if you use 'cursor_sharing=similar' in Oracle 9, but every column has a histogram, then the 'cursor_sharing' feature becomes a cost with no benefits.

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

Next Seminars
 UK  July / Sept
 Australia August
 Malaysia September
 USA (MI) November

http://www.jlcomp.demon.co.uk/seminar.html

Herman de Boer wrote in message ...


>

>In general (according to my experience), histograms will not influence

>the CBO badly. There's no harm to have histograms for all columns,

>probably with some exceptions to the rule.

>