oracle 边界前开后毕,统计信息的偏差，导致SQL性能影响的一则案例

机场部门的同事，咨询了一个问题，

有个机场昨天上线一台新装基于Windows的Oracle 10g服务器，XXX应用在使用时基本正常，但有一项功能执行后没有反应，是个多表联查的SQL。这个在旧的服务器上操作是正常的。

于是做了些试验，把下面where条件中in的范围缩小到4个，能出来结果，扩大到8个，执行就明显变慢，再扩大就干脆没响应了

where a.id in ( 0, 5 , 25 , 31 , 34 , 11      , 13 , 14 , 21 , 22 , 35 , 8 , 24 , 27 , 32 , 33 , 7 , 9 , 15 , 16      , 29 , 4 , 12 , 17 , 28 , 1 , 2 , 19 , 20 , 23 , 3 , 6 , 18 , 30 ,      10 , 26 ) and b.id in ( 0, 5 , 25 , 31 , 34 , 11 , 13      , 14 , 21 , 22 , 35 , 8 , 24 , 27 , 32 , 33 , 7 , 9 , 15 , 16 , 29      , 4 , 12 , 17 , 28 , 1 , 2 , 19 , 20 , 23 , 3 , 6 , 18 , 30 , 10 ,      26 )

昨天下午以为是TEMP表空间的问题，扩大到500M，没有改观。

昨天晚上10点多，系统突然就好了，当地未做任何修改。后来切到了备机进行观察。

根据现象，我追问了些问题，

1.a表和b表的数据量约为？

2. 这两张表用的是A或A_BAK表空间么？

3. “旧的服务器”，您指的是Oracle数据库装在了一台新的Windows服务器上？

4. 若3描述正确，且昨天晚上10点正常了，请问这台新的服务器，是从前一天晚上10点之后，做的数据迁移，并启动数据库？

5. 如果4描述正确，请问在此期间，DBA是否没有手工采集过统计信息？

6. 能否帮忙生成gqyr7h3t1snmz和ckmq9wkn92x4t这两个sql_id对应的SQL      AWR？若不清楚操作，可以参考《http://blog.csdn.net/bisal/article/details /54565444》。

朋友回复，

1. a数据量约为7000-10000，b数据量约为100-200，数量级都不大，A表有索引。。

2. 这两张表用的都是A表空间。A_BAK是备库，白天不涉及，夜间job会将3天前的历史数据转到备库。

3. 是的，新旧服务器都是windows的，只是旧服务器时间太长担心有隐患，因此新采购了新机器。

4. 新库基本不涉及数据迁移，只有几个表有静态数据，1中两个表中的数据，是从ZZZ应用直接下载下来的，下载的时间是前一天晚上11:20之后。白天安装，晚上23:00正式启用。

5. 应该没做过统计信息采集。这个有没日志能看出来？

6. 联系当地了，正在让他们做。

可能有些朋友，看见上述信息，就能猜出一二了。

select table_name, num_rows, last_analyzed from user_tables where table_name in ('A','B');

显示A表和B表均为3.15 22:00采集，A表num_rows为13000，B表num_rows为100。静态数据表或这几天没有业务数据的表，last_analyzed显示3.13 22:00，其他这两天有业务数据，则是3.15 22:00。从last_analyzed来看，可以推测，表示自动采集的，不是人工采集。

使用如下SQL，查看历史统计信息记录，

select savtime, rowcnt, blkcnt, avgrln, analyzetime from sys.wri$_optstat_tab_history where obj#=?;

10g以上的数据库，如果安装的时候，采用默认配置，默认会在每天22:00，根据规则，自动采集表、索引等的统计信息。

统计信息的作用，就是让Oracle可以根据这些数据，计算得出正确执行这条SQL的执行计划。

执行计划，就是执行这条SQL的路径，比如是用索引，还是全表扫描，如果用索引，具体用什么索引，两表连接，是用哈希连接，还是嵌套循环。可以说执行计划，就决定了这条SQL的执行效率。

再说现象，13日22:00开启新服务器，一直到14日22:00之前，运行一直比较慢。是因为这期间，有可能因为没有手工采集表的统计信息，对于Oracle来说，这是一张空表，因为错误的统计信息，导致Oracle选择了错误的执行计划，因此从现象上看是执行慢了。

14日22:00，Oracle自动采集统计信息任务开始，采集了正确的统计信息，此时Oracle就有了可能选择正确的执行计划。

IN中值不同，执行效率不同，是因为使用IN，Oracle会自动优化处理，IN值多了，就可能选择全表扫描，IN值少了，就有可能使用索引范围扫描，但是没有一个明确的边界，这是由Oracle根据统计信息，评估相应的成本决定的。因此有可能出现您说的，IN值少，执行快，IN值多，执行慢。

13日-14日22:00之间，数据为空，说明这期间没有采集统计信息，Oracle认为是空表，至少认为统计信息为空，所以选择何种执行计划，都是有可能的。

综合来讲，就问题现象来说，如果数据量、参数配置没变的前提，主要就是由于统计信息不准，导致的SQL执行效率抖动，当人工/自动采集统计信息，在准确的情况下，Oracle就有可能选择正确的执行计划。

比较疑惑的是，从AWR看，SQL语句每一步读取的rows都是1行，这和实际有些出入的，单就执行计划看，并不是一个高效的步骤，但是若按记录数，记录数很少，所以就掩盖了执行计划的不足，

A表采集的rowcnt是200多，实际上应该至少有几千。

我猜测，这可能和默认选择率有关，Oracle会根据默认选择率(10%)，采集表的统计信息。

曾经研究了一番，不同版本的默认采集率，并不是相同的，10g的值默认比较小，可能和这有关。

默认采集率比较小些，所以rows返回小了。

优化器在不同版本演进。其实我觉得，这两张表数据量比较小，即使采样100%，也是可以。采样率越高，执行时间就越久，这是影响。

要避免后续发生，可以有两种方法，

1. 安装完数据库，若有大量数据的导入，可以手工采集统计信息。

2. 安装完数据库，若条件允许，不要马上投入生产，可以先试运行，再投入使用。

如果新库，有大量数据迁移，建议导入完成，手工采集统计信息，这样可以让Oracle根据正确的数据，选择正确的统计信息。有了数据，再采集统计信息，才有意义。否则在默认统计信息采集前，由于采用错误的统计信息，可能导致SQL效率。所以建议要有大量数据的导入操作，一定要手工采集统计信息，这是dbsnake书中强调的。

其实简单来讲，Oracle选择执行计划，就需要知道这张表、索引，有什么样的数据量、有什么样的索引、索引有怎样的数据量，这些数据准确了，才能选择正确的执行计划。

这些数据就是来自统计信息，统计信息可以手工采集，也可以自动采集，自动采集使用默认选择率， 手工采集可以指定选择率，选择率越高，统计数据越准，但相应需要一些成本消耗，因此需要在准确性和可用性之间，做一些选择。

对于小表，有可能错误的执行计划，也会执行比较快，这是正常的，但是一旦数据量增加了，不同执行计划，执行效率就会有不同了。

同事的回复，

十分感谢，终于明白这个问题了，这几天一直奇怪，没有遇到过，现在看，之前其他机场是因为系统装完后都做了一段时间测试，有了测试数据，所以oracle执行了默认的采集动作，这个机场是下午装的数据库，也没测，晚上直接就上线了，正好赶上了。还一致怀疑和TEMP表空间太小有关系。

针对这一个问题，如果TEMP表小，其实无论是前端，还是数据库日志，都会记录，既然没有记录，说明这并不是根本原因了。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，