ITPub博客

首页 > 数据库 > Oracle > oracle 统计信息的收集

oracle 统计信息的收集

原创 Oracle 作者:bestpaydata 时间:2015-10-25 10:09:30 0 删除 编辑
在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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: TokuDB的安装
下一篇: oracle事务
请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985613