Re: global method_opt preference and histograms on internal oracle schemas
Date: Wed, 4 Nov 2020 12:34:34 +0000
Message-ID: <DB7PR10MB2090FDEC824B0BBA9622135A85EF0_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
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_at_ITS.JNJ.COM> Sent: 04 November 2020 12:03
To: Neil Chandler <neil_chandler_at_hotmail.com>; ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> 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_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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 04 2020 - 13:34:34 CET