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

My application [a highly customised Oracle Apps R11 database] is somewhere between "terrific" and "bust" !! So, I will be selective in deciding which columns I should compute Histograms on [e.g ORGANIZATION_ID columns used in Indexes].

This is in 8.1.7 and I am using the default CURSOR_SHARING=EXACT. Also, the supported optimizer mode is RULE but I am pushing the development team to modify all customised code to use CHOOSE and statistics.

Hemant
At 06:48 AM 14-04-03 -0800, you wrote:

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

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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