It's a brave call to switch off all histograms in 1 go.
If I'm in the rare situation of putting a greenfield database in, I globally
set to FOR ALL COLUMNS SIZE 1. I have some reasonably recent experience of this
at Oracle 11.2.0.3/4, 12.1 and 19.7 and I don't recall there being any unusual
performance issues in relation to oracle internal sql under these circumstances.
Neil
________________________________
From: Tahon, Dirk [GTSBE] <dtahon@xxxxxxxxxxx>
Sent: 04 November 2020 12:03
To: Neil Chandler <neil_chandler@xxxxxxxxxxx>; ORACLE-L
(oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
Subject: RE: global method_opt preference and histograms on internal oracle
schemas
Thank Neil.
For the application schemas, we've been using SQL Performance Analyzer to
capture production workload and test that on a lower environment without
histograms. As this is big database with lots of users and different SQL
statements going on, we could not do a 100% covering test but, together with
the application teams, we decided to go for the drastic approach and then
selectively add histograms as needed. Worst case we can always restore the
earlier statistics.
What we did not test were oracle internal kitchen statements and I was
wondering if anybody has seen issues with internal SQL on databases that have
used FOR ALL COLUMN SIZE 1 as the global preference from the beginning.
Regards,
Dirk
From: Neil Chandler <neil_chandler@xxxxxxxxxxx>
Sent: Wednesday, 4 November 2020 12:27
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>; Tahon, Dirk
[GTSBE] <dtahon@xxxxxxxxxxx>
Subject: [EXTERNAL] Re: global method_opt preference and histograms on internal
oracle schemas
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
