global method_opt preference and histograms on internal oracle schemas

From: Tahon, Dirk [GTSBE] <"Tahon,>
Date: Mon, 2 Nov 2020 10:48:45 +0000
Message-ID: <CY4PR0701MB37009443FF8FBF14D1E46C7283100_at_CY4PR0701MB3700.namprd07.prod.outlook.com>



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 Mon Nov 02 2020 - 11:48:45 CET

Original text of this message