RE: global method_opt preference and histograms on internal oracle schemas

From: Tahon, Dirk [GTSBE] <"Tahon,>
Date: Wed, 4 Nov 2020 12:03:06 +0000
Message-ID: <CY4PR0701MB3700C6F031C28DF14F71F40F83EF0_at_CY4PR0701MB3700.namprd07.prod.outlook.com>



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_at_hotmail.com> Sent: Wednesday, 4 November 2020 12:27
To: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>; Tahon, Dirk [GTSBE] <dtahon_at_ITS.JNJ.COM> 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



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Tahon, Dirk [GTSBE] <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> Sent: 02 November 2020 10:48
To: ORACLE-L (oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>) <oracle-l_at_freelists.org<mailto: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
Received on Wed Nov 04 2020 - 13:03:06 CET

Original text of this message