Re: RE: query slow in 9i, but not slow in 8i

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 01 Mar 2004 15:55:46 -0700
Message-Id: <6.0.3.0.2.20040301154830.02b5e728@pop.centrexcc.com>

As I said, if the columns did not have histograms in 8i I would not create histograms in 9i.

I do no a-priori create a histogram on a column. ONLY if the analysis of a sql performance problem indicates that a histogram is indicated.

In 9i you also can query sys.col_usage$ to find candidates for histograms. That is what Oracle does when you request "method_opt => 'for ... columns size auto' ".

At 02:51 PM 3/1/2004, you wrote:
>yeah good idea. how do you determine which columns need histograms? I have
>100s of tables in my applications and I do not have time to determine it. I
>typically have them run over night.
>----- Original Message -----
>From: "Wolfgang Breitling" <breitliw_at_centrexcc.com>
>To: <oracle-l_at_freelists.org>
>Sent: Monday, March 01, 2004 2:17 PM
>Subject: Re: RE: query slow in 9i, but not slow in 8i
>
>
> > I would NOT do that. "method_opt='FOR ALL COLUMNS'" will collect
>histograms
> > of (default) size 75 for ALL columns of the table. At best that would be a
> > waste of time and resources, but it could easily be rather detrimental to
> > access paths. You do NOT want to collect histograms unless it is
>warranted.
> >
> > If the columns did not have histograms in 8i, I would not suddenly create
> > any in 9i.
> >

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 01 2004 - 16:55:57 CST