Home -> Usenet -> c.d.o.server -> Re: DBMS_STATS

Re: DBMS_STATS

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 30 Sep 2009 01:58:49 -0700 (PDT)
Message-ID: <576446a9-264d-429d-9e00-c2835bc32425_at_s31g2000yqs.googlegroups.com> 





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 where skew_column =
'BLUE';


select count(*) from auto_stats_job_skew_hidden where skew_column =
'GREEN';


select count(*) from auto_stats_job_skew_hidden t where
decode(sign(id - 10), -1, 'GREEN', 'RED') = '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






exec dbms_stats.gather_database_stats_job_proc



    
  
  •   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


where


       table_name = 'AUTO_STATS_JOB_SKEW_HIDDEN';






Regards,


Randolf



Oracle related stuff blog:


http://oracle-randolf.blogspot.com/



SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/


http://sourceforge.net/projects/sqlt-pp/
Received on Wed Sep 30 2009 - 03:58:49 CDT












Original text of this message