Home -> Mailing Lists -> Oracle-L -> Re: global method_opt preference and histograms on internal oracle schemas

Re: global method_opt preference and histograms on internal oracle schemas

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Wed, 4 Nov 2020 11:26:37 +0000
Message-ID: <DB7PR10MB2090AB1730AFF6646D12417685EF0_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM> 








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


Received on Wed Nov 04 2020 - 12:26:37 CET












Original text of this message