oracle统计信息(转)

1.   为什么数据库要收集统计信息？

你每天开车来公司，是不是有好多种路可以走？

百度地图能给出三种相对最快的方案。

 

好，从这个栗子，你应该有了概念：

从家到公司，就是你SQL语句艰难的执行之路。

百度地图，就是Oracle指挥中心——CBO——基于成本的优化器，后台当然是各种烧脑的算法。

百度地图左边红框给你推荐的三条线路，就是你的SQL可以选择的执行计划，照着计划走，肯定能到公司，也肯定能执行成功。

百度地图给你用蓝框写了个“推荐”，告诉你，走这条路到华为的路程和时间综合来说是最短的。同样，Oracle的CBO也能经过各种牛B的算法得出，走哪个执行计划，你能最快得到执行结果。当然，与百度地图不同的是，它不会告诉你，它只会默默的帮你按照这个计划走。

 

由于计算机做出的东西总是有时候会不靠谱这个定律，可以得出百度地图和CBO有时候都可能脑子犯糊涂。所以李彦宏和拉里埃里森肯定会在背后让勤勤恳恳的码农吭哧吭哧的设计更高级更牛B的算法。

码农心想，这不行啊，光靠时间+距离这个维度不可能完全设计好最优化线路啊，得需要收集一些路况信息才行。于是开始用逼格data收集天气状况、交通繁忙度、是不是早高峰啊、道路是几车道啊流量啊什么的交通数据信息（我瞎扯的）。。。

同理，Oracle的码农也不甘示弱，要收集你SQL中涉及的表的信息，包括但不限于：

表有多少行数据、占用多少数据块、有多少没有用的数据块、平均每行长度多少、有多少行链接。。。这些都在dba_tables里，自己看。

表上每个列有多少行、这个列有多少空值、多少唯一值、最大值和最小值是多少、选择度怎样（比如这个字段只有2个值：男、女，表示可筛选度差）。。。这些都在all_tab_columns里。

索引有多少层，有多少个叶子块、聚簇因子多大（越接近表的数据块数越好）、等等我懒得说了。。在dba_indexes里。

于是，前面一大段废话后，终于说道重点了。

收集统计信息的作用，就相当于百度地图收集交通流量，干的活就是收集表、索引、列上的信息，以提供给CBO计算SQL的执行计划。

 

2.   如何收集统计信息？

这个地球人都会，然后我再贴一遍。

--收集表的统计信息

exec  dbms_stats.gather_table_stats(ownname=>'大写的数据库用户名',tabname=>'大写的表名',degree=>4,cascade=>true,force=>true,no_invalidate=>false);

--收集用户的统计信息

exec  dbms_stats.gather_schema_stats(ownname=> '大写的数据库用户名',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,degree=>4,cascade=>true,force=>true,no_invalidate=>false);

 

3.  如果我不想手动收集，有木有自动收集的办法？

有。

Oracle自带BGM，有自动收集的定时任务，你只要保证是任务和时间窗都是开启状态，辣么，这活Oracle帮你干了。

 

4.   为啥自动维护任务开启了，可是我第二天一看，统计信息还是没收集？

RP不好通常会有这种现象。

当你的表发生数据量变化（insert+delete+update或truncate），上一次收集统计信息得出的数据量相差10%，Oracle才会自动帮你收集统计信息。

你想，你那表每天都没啥变化，收集有啥意义。这叫节约资源。

通过下列语句可以查询你的表是否需要收集：

--1.刷新监控缓存到user_tab_modifications表

 exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

--2. 检查需要收集统计信息的表

SELECT m.table_name,sum(m.inserts + m.updates +

 m.deletes),num_rows

  FROM user_tab_modifications m, user_tables u

 where m.table_name = u.table_name

 group by m.table_name,num_rows

having sum(m.inserts + m.updates + m.deletes) >num_rows*0.1;

 

--3. 检查需要收集统计信息的分区表

SELECT m.table_name,m.partition_name,sum(m.inserts + m.updates +m.deletes),u.num_rows

  FROM user_tab_modifications m, user_tab_partitions u

 where m.partition_name = u.partition_name

 group by m.table_name,num_rows,m.partition_name

having sum(m.inserts + m.updates + m.deletes) >num_rows*0.1;

 

5.   我这表好脆弱，稍微变动5%，执行计划就变了，有木有办法让Oracle改成变动5%就自动收集啊？

恭喜你，有。

调整统计信息收集频率

--1).查询表自上次收集统计信息后，统计出来的行数

 SELECT A.num_rows,A.last_analyzed FROM dba_tables A where table_name='大写的表名' and owner='大写用户名';

--2).刷新监控缓存到user_tab_modifications表

 exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

--3).查询自上一次收集统计信息后，表的变动情况

SELECT * FROM user_tab_modifications where table_name='大写的表名';

--观察几天，统计inserts，updates，deletes，timestamp，truncate字段的变化量。

--4). 调整收集频率百分比

exec dbms_stats.set_table_prefs('大写的数据库用户名','大写的表名','STALE_PERCENT','填写收集百分比(数字)');

 --查询调整是否成功

SELECT * FROM dba_tab_stat_prefs where owner='大写的数据库用户名' and table_name='大写的表名';

 

 

6.   统计信息收集好了，是不是执行计划就万无一失了？

请重复阅读FAQ1中提及的定律。