Delete Histogram | Oracle Scratchpad
Here’s a note which I drafted in Novemeber 2010, and 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 success 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 success srec dbms_stats.statrec; success m_distcnt number; m_density number; m_nullcnt number; m_avgclen success number; n_array dbms_stats.numarray; begin dbms_stats.get_column_stats( ownname => user, tabname => 't1', colname => 'n1', distcnt => m_distcnt, success 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 success all columns size 1′ (i.e. no histograms) and then run scripts to create the histograms success 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 success 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 success => ‘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 .
Thanks for that. I guess that may be why I never got around to publishing the note originally – I had forgotten all about the easy option. Still – there are a few people around using 10g and (much) older versions ;)
Jonathan, no worries, i remembered that one because some time ago, i had just incorporated to SQLT that functionality (delete histograms) when I learned from Maria that 11g provided the same!… Your script is very useful for 10g or older. Thanks for sharing!
Hi Jonathan, a few month ago i hit Oracle bug #11786774 (MOS ID #11786774.8 – Bug 11786774 invalid histogram created by set_column_stats) at client site, which creates an invalid histogram even if only two endpoint values (and no buckets) are specified – it was on 10.2.0.5. I noticed this bug by cloning and adjusting (local) partition statistics which results in subsequent copy issues, when the number of buckets were not explicitly specified / reset every time. Unfortunately there is no (official, not hacking) work around for getting success the rid of (invalid) histograms then.
Thanks for the note. According to the bug description the problem success appears when set_column_stats is called with a single value; obviously this shouldn’t be allowed to cause a problem, but any code that calls it is inherently wrong anyway. There’s a patch available for 10.2.0.5 for anyone on extended support.
The bug is also against 10.2.0.5 and has been dormant for a couple of years – but looking at the information offered (and the sample code) the problem is that the programmer didn’t realise you had to set srec.epc (endpoint counter) to match the number of elements in the array.
Hi Jonathan, i used set_column_stats with the whole parameter set and not just one single value. I used the following PL/SQL code and it created that invalid (frequency) histogram (in this specific case i needed the manual adjustment of the low/high value due to Oracle bug #14607573
Here’s a note which I drafted in Novemeber 2010, and 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 success 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 success srec dbms_stats.statrec; success m_distcnt number; m_density number; m_nullcnt number; m_avgclen success number; n_array dbms_stats.numarray; begin dbms_stats.get_column_stats( ownname => user, tabname => 't1', colname => 'n1', distcnt => m_distcnt, success 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 success all columns size 1′ (i.e. no histograms) and then run scripts to create the histograms success 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 success 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 success => ‘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 .
Thanks for that. I guess that may be why I never got around to publishing the note originally – I had forgotten all about the easy option. Still – there are a few people around using 10g and (much) older versions ;)
Jonathan, no worries, i remembered that one because some time ago, i had just incorporated to SQLT that functionality (delete histograms) when I learned from Maria that 11g provided the same!… Your script is very useful for 10g or older. Thanks for sharing!
Hi Jonathan, a few month ago i hit Oracle bug #11786774 (MOS ID #11786774.8 – Bug 11786774 invalid histogram created by set_column_stats) at client site, which creates an invalid histogram even if only two endpoint values (and no buckets) are specified – it was on 10.2.0.5. I noticed this bug by cloning and adjusting (local) partition statistics which results in subsequent copy issues, when the number of buckets were not explicitly specified / reset every time. Unfortunately there is no (official, not hacking) work around for getting success the rid of (invalid) histograms then.
Thanks for the note. According to the bug description the problem success appears when set_column_stats is called with a single value; obviously this shouldn’t be allowed to cause a problem, but any code that calls it is inherently wrong anyway. There’s a patch available for 10.2.0.5 for anyone on extended support.
The bug is also against 10.2.0.5 and has been dormant for a couple of years – but looking at the information offered (and the sample code) the problem is that the programmer didn’t realise you had to set srec.epc (endpoint counter) to match the number of elements in the array.
Hi Jonathan, i used set_column_stats with the whole parameter set and not just one single value. I used the following PL/SQL code and it created that invalid (frequency) histogram (in this specific case i needed the manual adjustment of the low/high value due to Oracle bug #14607573
No comments:
Post a Comment