Re: global method_opt preference and histograms on internal oracle schemas

Just to add to Neil's comment:

There are only two sensible strategies for histograms, the one I prefer and the one Maria Colgan prefer's

1. Set global to SIZE AUTO then do something about the small number that cause problems (Maria's)
2. Set global to SIZE 1 then do something about the small number that cause problems (Mine)

The problem is that switching between strategies is likely to be painful - though you can make some good guesses about switching into SIZE 1, columns with very small numbers of distinct values are worth checking for uneven distributions, "special" values, and queries involving literals as these might benefit hugely from a frequency histogram. Columns with a few very popular values but many other values spread over a small fraction of the data (and literal queries for the populare values) could benefit from "top-frequency" histograms.

In the bad old days the "something" could be quite messy - like locking stats to stop the autogather for given tables, then coding for a specific gather - including constructing a "fake" histogram Nowadays it's usually the option that Neil suggested - set a table preference to name the columns that need histograms. And if you've only got a few, and it really matters then there's rarely a threat in making the size the current maximum (2048 for 12c) unless this pushes Oracle from a top-frequency to a hybrid. And DON'T ever use "size repeat".

Regards
Jonathan Lewis

On Wed, 4 Nov 2020 at 11:29, Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> Dirk,
>
> All histograms in 11.2 may cause instability due to their sampled nature.
> It's much better in 12+ for frequency-type histograms but hybrid and
> heigh-balanced will always be sampled (unless Oracle find a way to sort
> large data sets very cheaply).
>
> Removing all histograms by setting a global parameter is a significant
> change. You will probably get plan changes across the entire system (oracle
> and user schemas). Remove histograms and you get different cardinality
> calcs for the same predicate. The changes may improve the performance,
> degrade the performance or leave it fairly unchanged. It is impossible to
> predict the scale and impact of such a broad change without testing.
>
> A safer approach would be to adjust METHOD_OPT using SET_TABLE_PREFS on a
> table-by-table basis, removing histograms one table at a time. If there's a
> problem, you will be dealing with a limited subset of change rather than an
> entire system. This would also allow you to leave some histograms in play
> when they are a known benefit.
>
> You might want to test this with Pending stats, so you can run the same
> SQL with and without histograms and observe the change.
>
> regards
>
> Neil Chandler
>
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Tahon, Dirk [GTSBE] <dmarc-noreply_at_freelists.org>
> *Sent:* 02 November 2020 10:48
> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Subject:* global method_opt preference and histograms on internal oracle
> schemas
>
>
> Hi all,
>
>
>
> We're considering to change the method_opt on an existing database from
> the default FOR ALL COLUMNS SIZE AUTO to FOR ALL COLUMNS SIZE 1. Reason is
> SQL Plan instability issues, caused by too many histograms getting rather
> arbitrary different endpoints (because of the small histogram sample size
> of 5500 [and we'd like to stick with the auto sample size as that gives the
> better result for the basic statistics]).
>
> Several internal Oracle schemas (including SYS) currently do have column
> histograms.
>
> Question: has anyone seen performance issues by setting method_opt FOR ALL
> COLUMNS SIZE 1 as the global preference because of column histograms on
> internal schemas also being removed?
>
>
>
> Note: in our specific case the Oracle version still is 11.2.0.4.
>
>
> Thanks,
>
> Dirk
>

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