Date: Wed, 30 Sep 2009 01:58:49 -0700 (PDT)
On Sep 29, 12:47 am, jimmyb <jimmybr..._at_gmail.com> wrote:
> I don't rely on the Oracle job that gathers stats each night. If your
> data does not fall within the "baseline" for that job it will not the
> gather stats you need.
> It does not take into account skewed data, nor will it gather stats
> for function-based indexes.
> Oracle creates a hidden/virtual column for function-based indexes, so
> to gather stats you will need to use the method_opt 'FOR ALL HIDDEN
> COLUMNS SIZE 1'. Assuming your indexing only one value.
If you state such things you should at least mention the version you've encountered this behaviour.
A simple test case on 10.2.0.4 reveals that at least for that version this in incorrect, as long as you are running with defaults, which is you haven't changed the AUTOSTATS_TARGET and METHOD_OPT parameter using DBMS_STATS.SET_PARAM and the scheduler auto stats job is active.
The default values of above parameters are "ALL" and "FOR ALL COLUMNS SIZE AUTO" respectively, and using this defaults both will be covered by the auto stats job:
- Skewed column value, if the column has been used as predicates in queries (column workload monitoring)
- Statistics on hidden columns, including histograms in case of skew of this hidden column data
A simple test case demonstrates this:
drop table auto_stats_job_skew_hidden purge;
- Sample table with data skew create table auto_stats_job_skew_hidden as select id , case when id <= 10 then 'RED' when id <= 100 then 'BLUE' when id <= 1000 then 'GREEN' end as skew_column from (select level as id from dual connect by level <= 1000);
- Function based index with data skew create index idx_auto_stats_job_skew_hidden on auto_stats_job_skew_hidden ( decode(sign(id - 10), -1, 'GREEN', 'RED') );
- Generate workload on the columns select count(*) from auto_stats_job_skew_hidden where skew_column = 'RED';
select count(*) from auto_stats_job_skew_hidden t where decode(sign(id - 10), -1, 'GREEN', 'RED') = 'GREEN';
select count(*) from auto_stats_job_skew_hidden t where decode(sign(id - 10), -1, 'GREEN', 'RED') = 'BLUE';
- Check statistics before job select column_name , num_buckets , sample_size , last_analyzed from user_tab_cols where table_name = 'AUTO_STATS_JOB_SKEW_HIDDEN';
- run as SYS in a separate session
- This is what is run by the auto stats job in 10g via the scheduler set echo on
- Check statistics afterwards select column_name , num_buckets , sample_size , last_analyzed from user_tab_cols where table_name = 'AUTO_STATS_JOB_SKEW_HIDDEN';
select * from user_tab_histograms
table_name = 'AUTO_STATS_JOB_SKEW_HIDDEN';
Oracle related stuff blog: