Re: disable histogram usage at session / statement level

Thanks a lot, yes both methods work fine .

As a background for my question: I noticed that for a lot of tables the stats job computed hybrid histograms on ID or PARENT_ID columns and the cardinality estimate for joins using these columns are orders of magnitude trillion of rows. Dropping/disabling the histogram resolves the estimate to 20-30 millions (the expected cardinality)

Because there are so many tables I need to take now query by query and do a quick evaluation with/without histograms and drop where I see a negative impact.

I`m starting to think that actually the best method is to have them disabled since the beginning and where is the case create one. (instead of taking Maria`s advise to use auto and evaluate where they create issues) - not to mention that because of histograms for some tables the incremental stats don't work as expected

În joi, 18 mar. 2021 la 12:41, Chinar Aliyev <chinaraliyev_at_gmail.com> a scris:

> Hi,
> Have you checked that one:
> /*+ opt_param('_optimizer_use_histograms' 'false') */
>
> Best Regards
>
> On Thu, Mar 18, 2021 at 2:16 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Adding the hint
>>
>> /*+ opt_param('_fix_control','9550277:1') */
>>
>> seems to work.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>> On Thu, 18 Mar 2021 at 09:49, Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> wrote:
>>
>>> Hello,
>>>
>>> Is there a method to influence the optimizer at session / statement
>>> level to avoid histogram (especially hybrid) usage?
>>>
>>> Thank you.
>>>
>>
>
> --
> *Chinar Aliyev*
>
>
> Visit My :Blog <http://chinaraliyev.wordpress.com/>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <https://www.linkedin.com/in/chinaraliyev/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - ChinarAliyev
> <https://twitter.com/ChinarAliyev>
>
>

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