Here’s a note that I drafted in November 2010, then didn’t publish. I found it earlier on this morning while looking for another note I’d written about histograms so, even though it may not be something that people need so much these days, I thought: better late than never.
I’ve pointed out in the past that I’m not keen on seeing lots of histograms on a system and tend to delete them if I think they are not needed. Here’s an example of the type of code I use to delete a histogram.
declare srec dbms_stats.statrec; m_distcnt number; m_density number; m_nullcnt number; m_avgclen number; n_array dbms_stats.numarray; begin dbms_stats.get_column_stats( ownname => user, tabname => 't1', colname => 'n1', distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen ); srec.bkvals := null; srec.novals := dbms_stats.numarray( utl_raw.cast_to_number(srec.minval), utl_raw.cast_to_number(srec.maxval) ); srec.epc := 2; dbms_stats.prepare_column_values(srec, srec.novals); m_density := 1/m_distcnt; dbms_stats.set_column_stats( ownname => user, tabname => 't1', colname => 'n1', distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen ); exception when others then raise; -- should handle div/0 end; /
The code basically reads the column stats, resets the histogram figures to just the low and high values for the column, setting the endpoint-count to two, then adjusts the density to the standard for a column with no histogram. This specific example is for a numeric column.
Footnote: my preferred method of collecting statistics is to use method_opt => ‘for all columns size 1’ (i.e. no histograms) and then run scripts to create the histograms I want. This means that after any stats collection I need to run code that checks to see which tables have new stats, and then re-run any histogram code that I’ve written for that table.
To move from Oracle’s default histogram collection to this strategy, you could start by switching to method_opt => ‘for all columns size repeat’ (i.e. recreate existing histograms, don’t create new ones), then simply delete histograms as you find that you don’t need them, and introduce scripts to recreate the histograms that you do need. When you’ve finally got to the point where every histogram is scripted you can then switch to method_opt => ‘for all columns size 1’.
Footnote 2: Since 2010 when I drafted this note Oracle 12c has launched, and the changes it has introduced for frequency and Top-N histograms means that I’m far less stringent in my demand that if a histogram is worth having it’s better to write code to create it. There’s a series of three articles about 12c histograms in particular at this link.