Re: stale partition statistics
Date: Fri, 5 Mar 2021 18:45:56 +0000
Message-ID: <DB7PR10MB209043460D8154D913E0FA2585969_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
Using METHOD_OPT of "FOR ALL COLUMNS SIZE REPEAT" and nothing will change, until you get an additional distinct value in a Frequency or Top Frequency column. Then it will change and become a far less useful histogram (even if you only have a very few distinct values), which will be constructed using "samples" leading to the potential for plan instability.
If you want histogram stability from 12.1 onward (and most people should - definitely if you have partitioned tables), look and see what you have on the table currently and put that explicitly into the method opt. I cannot recommend strongly enough moving away from using "REPEAT". I'd rather use AUTO (and I don't like AUTO)
exec dbms_stats.set_table_prefs(schema,table,'METHOD_OPT',
'FOR ALL COLUMNS SIZE 1
FOR COLUMNS SIZE 254 col1, col2
FOR COLUMNS SIZE 2000 col7, col20, col35');
You might even identify columns which have a little over 254 distinct values and change the histogram to be the cheap and accurate Frequency-type.
If you look on my blog there's a low quality video of me talking about this sort of thing (Database Statistics – When It’s Harder<https://chandlerdba.com/oracle-database-statistics-when-its-harder/> ) : https://chandlerdba.com/presentations/ Fortunately the camera is mostly pointed at the screen and not me.
regards
Neil Chandler
From: Noveljic Nenad <nenad.noveljic_at_vontobel.com> Sent: 05 March 2021 18:03
To: Neil Chandler <neil_chandler_at_hotmail.com>; ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>; w1ti_at_comcast.net <w1ti_at_comcast.net> Subject: RE: stale partition statistics
I didn’t know that modifications below the stale threshold could trigger statistics calculation! Now I’m restoring the database to the point in time just before gather. Then I’m going to check the modifications view and relate it to the total number of records to figure out what exactly happened.
Yes, with “REPEAT” I’ve meant the METHOD_OPT value. This is to avoid new histograms on a daily basis. I’m aware that “REPEAT” can lead histograms to switch from frequency to hybrid but it doesn’t seem to be a problem in our case.
Thanks,
Nenad
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 05 2021 - 19:45:56 CET