RE: How to make query avoid using histograms

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Wed, 27 Apr 2005 18:51:42 +0200
Message-Id: <20050427165138.4F81A8010FF4@smtp-out1.tiscali.nl>

well then, why not simply drop the histograms? you can always recreate them -- or for a faster solution: save them into a stats table, clear them from the dictionary, and restore them from the stats table when done ... kind regards,

Lex.

Visit my website at http://www.naturaljoin.nl

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya
Sent: Wednesday, April 27, 2005 13:27
To: Niall Litchfield
Cc: lex.de.haan_at_naturaljoin.nl; Oracle Discussion List Subject: Re: How to make query avoid using histograms

we found that this query takes slightly different execution paths based no input parameters (most number), turning off bind variable peeking and changing cs=exact didn't help. That's why I wanted to find out what would happen, if somehow I could turn off usage of histograms for this query. Christian, RULE hint didn't help either.

Raj

On 4/27/05, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> On 4/26/05, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:
> > you can't -- if histograms are available, they will be used.
> > of course, you can use various hints and/or session settings to
> > influence the optimizer.
> > just out of curiosity: why would you, in the presence of histograms,
> > ever want to tell the optimizer *not* to use them?
>
> Because you issue a report with bind variables that for 99% of its
> executions *should* use histograms, but for 1% of them - the report to
> the board say, shouldn't use the execution path generated with
> histograms, and bind variable peeking isn't in operation. I don't
> think this applies to 9204 though.
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
>

--
------------------------------
select standard_disclaimer from company_requirements where category =
'MANDATORY';

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 27 2005 - 12:55:56 CDT