oddity when deleting histograms (11gR2)
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2010 11:23:44 -0500
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:
