oracle 统计信息的收集

在OLTP系统中，一定要收集统计信息
一般OLAP系统都不收集，直接用HINT固定
因为OLAP系统I/O压力大，表也非常大，收集统计信息很耗费资源
并且OLAP系统表的数据随时都在变化（因为实时在入库），那么就需要经常收集统计信息
所以OLAP系统收集统计信息不现实

在OLTP系统中要优化一个SQL语句，首先要保证SQL语句中所有的表统计是准确的，如果统计信息都不准确，那么SQL优化就无从谈起，在OLAP系统中，如果数据量特别大，一般不会去收集统计信息，会直接利用HINT把执行计划给固定住。

常用的统计信息收集脚本：
非分区表：
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
    tabname =>'DEPT',
    estimate_percent =>30,
    method_opt =>'for all columns size repeat',
    no_invalidate =>FALSE,
    degree =>8,
    cascade =>TRUE);
END;
/

对分区表收集统计信息：
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname =>'ROBINSON',
    tabname =>'P_TEST',
    estimate_percent =>30,
    no_invalidate =>FALSE,
    degree =>8,
    granularity =>'ALL',
    cascade =>TRUE);
END;
/

estimate_percent表示采样率，采样率设置太大，也没必要，如果表非常大，采样率过高会导致收集统计信息跑很长，增加了系统压力。采样率设置过小，统计的信息就不能很完整的体现表中数据的分布，这样CBO在进行执行计划的选择上，很可能选择错误的执行计划。根所工作经验：
表小于1GB采样率可以设置50%-100%
表大于1GB小于5GB可以设置30%
表大于5GB这类表都应该进行分区，采样率可以设置为30%

用segmet_size来计算一个表是大表还是小表

method_opt：表示收集的方法，一个稳定的系统收集统计推荐使用
method_opt:'for all columns size repeat'
repeat表示以前收集过直方图，现在收集统计信息的时候就收集直方图，如果以前没收集过直方图，现在收集统计信息的时候就不收集

method_opt:'for all columns size auto'
auto表示oracle根据谓词过滤信息，自动判断该列是否收集直方图，一个稳定的系统，不应该让Oracle去自动判断，自动判断很可能会出事，比如某列不该收集直方图，设置auto过后它自己去收集直方图了，从而导致系统不稳定。

degree：表示收集统计信息的时候并行度，并行度根据系统配置以及当前系统可用资源自行调协，一般degree设置4-8
一般设置为4
cascade：表示收集表的统计信息时候同时收集索引的统计信息
no_invalidate:表示收集统计信息之后在共享池中引用了相关的SQL游标是否失败。这个一定要设置为FALSE，不然你可能在做SQL优化的时候，你发现明明更新了统计信息，但是执行计划还是没改变

----实验1----
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
    tabname =>'DEPT',
    estimate_percent =>30,
    method_opt =>'for all columns size skewonly',--》这个会收集直方图
    no_invalidate =>FALSE,
    degree =>8,
    cascade =>TRUE);
END;
/

select a.column_name,
       b.num_raws,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows*100,2) selectivity,
       num_nulls,
       density,
       a.histogram,
       a.num_buckets
    from dba_tab_col_statistics a,dba_tables b
    where a.owner=b.owner
    and a.table_name=b.table_name
    and a.owner=upper('&table_name')
    and a.table_name=upper('&table_name')
    and a.column_name=upper('&column_name');

---实验2----
drop table test purge;
create table test as select * from dba_objects;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
    tabname =>'DEPT',
    estimate_percent =>30,
    method_opt =>'for all columns size auto',
    no_invalidate =>FALSE,
    degree =>8,
    cascade =>TRUE);
END;
/

select a.column_name,
       b.num_raws,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows*100,2) selectivity,
       num_nulls,
       density,
       a.histogram,
       a.num_buckets
    from dba_tab_col_statistics a,dba_tables b
    where a.owner=b.owner
    and a.table_name=b.table_name
    and a.owner=upper('&table_name')
    and a.table_name=upper('&table_name')
    and a.column_name=upper('&column_name');

用auto会根据where子句后的字段来自动创建直方图
例如在创建了表之后  执行 select * from test where owner=‘SCOTT';之后在统计信息的话
这时会在owner列收集直方图

exec dbms_stats.flush_database_monitoring_info;

select owner,table_name name,object_type,stale_stats,last_analyzed
from dba_tab_statistics
where table_name in ('TEST')
and owner='OWNER_NAME'
and (stale_stats='YES' or last_analyzed is null);
如果没有返回数据则表示没有过期

delete from test where rownum<=83499*0.11;

这时再查看则会返回数据，则表示已经过期

在做优化的时候，如果发现统计信息没过期，则要检查采样率
select owner,
       table_name,
       num_rows,
       sample_size,
       trunc(sample_size/num_row*100) estimate_percent
from dba_tab_statistics
where owner='SCOTT' and table_name='TEST';

以下语句处理查询大批量的表的统计信息是否过期

select '''' || object_owner || '''','''' || object_name || ''','
   from plan_table
   where object_type='TABLE'
union
---table in the index------
select '''' || table_owner || '''','''' || table_name || '*'','
    from dba_indexes
    where owner in
          (select distinct object_owner from plan_table where rownum>0)
and index_name in
 (select distinct object_name from plan_table where rownum>0)
order by 2;
----再把上面的结果贴入下面的代码--------
select owner,table_name name,object_type,stale_stats,last_analyzed
   from dba_tab_statistics
   where table_name in
   (
   table_name
   )
   and owner='owner'
   and (stale_stats='SYS' or last_analyzed is null);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1816099/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1816099/