Re: oddity when deleting histograms (11gR2)
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: landstander668@xxxxxxxxx
- Date: Wed, 23 Jun 2010 18:53:21 +0200
I'm guessing you're misinterpreting the output of your query against
dba_*_histograms
Check it against dba_tab_col_statistics, namely the num_buckets value for
the columns in question. Only if that's > 1 you actually have a histogram.
Stefan
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Wed, Jun 23, 2010 at 6:23 PM, Adric Norris <landstander668@xxxxxxxxx>wrote:
> We're investigating an erratic performance problem, in which performance of
> certain queries goes from good to truly horrific due to undesirable
> execution plan changes. The cause, as you've probably already guessed,
> appears to be the infamous histogram plus bind peeking scenario.
>
> We've got several things in mind to address the issue, but the immediate
> plans are to:
>
> 1. Remove existing histograms, all of which were created automatically
> by Oracle, from the tables in question.
> 2. Use *method_opt=>'FOR ALL COLUMNS SIZE REPEAT'* whenever gathering
> statistics for these tables, in order to preserve histograms (if any) which
> are explicitly re-added without automagically creating new ones.
>
> I'm starting out by using the *dba_tab_histograms* and *
> dba_part_histograms* views, in order to identify the relevant histograms.
>
> SQL> select owner, table_name, column_name
> 2 from ( select owner, table_name, column_name from
> dba_tab_histograms
> 3 union
> 4 select owner, table_name, column_name from
> dba_part_histograms
> 5 )
> 6 where owner = 'AGGR'
> 7 and table_name = 'ENS_BILL_GL_SUMMARY_T'
> 8 order by 1, 2, 3;
>
> OWNER TABLE_NAME COLUMN_NAME
> ----- --------------------- ------------------------------
> AGGR ENS_BILL_GL_SUMMARY_T ACTV_AMT
> AGGR ENS_BILL_GL_SUMMARY_T ACTV_CODE
> ...
> AGGR ENS_BILL_GL_SUMMARY_T USAGE_JURISDICTION_CD
>
> 41 rows selected.
>
> Next, I use *dbms_stats.delete_column_stats* to remove the histograms in
> question.
>
> SQL> exec dbms_stats.delete_column_stats('AGGR', 'ENS_BILL_GL_SUMMARY_T',
> 'ACTV_AMT', -
> > cascade_parts=>TRUE, force => TRUE, col_stat_type => 'HISTOGRAM')
>
> PL/SQL procedure successfully completed.
>
> Finally, I need to verify that the histogram has indeed been removed.
>
> SQL> select owner, table_name, column_name
> 2 from ( select owner, table_name, column_name from
> dba_tab_histograms
> 3 union
> 4 select owner, table_name, column_name from
> dba_part_histograms
> 5 )
> 6 where owner = 'AGGR'
> 7 and table_name = 'ENS_BILL_GL_SUMMARY_T'
> 8 order by 1, 2, 3;
>
> OWNER TABLE_NAME COLUMN_NAME
> ----- --------------------- ------------------------------
> AGGR ENS_BILL_GL_SUMMARY_T ACTV_AMT
> AGGR ENS_BILL_GL_SUMMARY_T ACTV_CODE
> ...
> AGGR ENS_BILL_GL_SUMMARY_T USAGE_JURISDICTION_CD
>
> 41 rows selected.
>
> What the heck... shouldn't the first entry have dropped off the list? This
> occurs for all tables/columns I've tried so far, so I'm clearly overlooking
> something but can't seem to pinpoint the culprit. Can anyone explain what's
> going on here?
>
> I've also tried removing the global and partition-level histograms
> individually (rather than relying on *cascade_parts=>TRUE*), with no
> apparent change to the behaviour.
>
> Thanx!
>
>
> [Let the onslaught of useless out-of-office notifications begin!]
> --
> Awk! Pieces of eight. Pieces of eight. Pieces of seven... ERROR: kernel
> panic [parroty error]
>
>
Other related posts:
