Re: Histograms -- are there any negatives / gotchas ?

If they are not needed, then you have introduced an extra volume of data into the dictionary cache and library cache; and increased the amount of CPU and latching that takes place at parse time. If you have a terrific application that hardly ever parses at all, and plenty of memory to allocated as the shared_pool_size, then this probably doesn't matter too much.

If your application is bust, and less than excellently written, you may notice the difference.

Once special gotcha - if you are using
cursor_sharing = similar in v9 (rather than force as in v8) to reduce the impact of
literal strings, then the presence of histograms on columns that appear with literal conditions in the where clause causes Oracle to fall back to re-parsing after substituting for binds. So the cost of parsing goes up instead of down.

Regards

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

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 14 April 2003 09:53

>
>
> Other than the disk space required to store Column
> Histogram statistics, are there any negatives / gotchas /
> downsides to collecting Column Histograms with, say,
> size 10 or 20 or 30 for ALL COLUMNS or ALL INDEXED COLUMNS ?
>
> e.g. could there be a noticable impact on Parse time ?
>
> Hemant K Chitale
> http://hkchital.tripod.com
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).