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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Tahon, Dirk [GTSBE] <dmarc-noreply@xxxxxxxxxxxxx>
Sent: 02 November 2020 10:48
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
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
