验证Oracle自动收集统计信息机制

lsekfe

摘要：Oracle数据库一直在银行业承担着举足轻重的作用，在绝大多数的应用系统中银行都采用了Oracle数据库对业务数据进行存储，这带来的好处显而易见，由于Oracle数据库本身具有处理速度快，安全级别高的特点，给银行业的应用系统提供了极大的支撑，但同时数据库引发的性能问题已经成为了我们不可避免和重之又重需要考虑的问题。在进行银行某系统的性能测试时，发现了Oracle数据库一个非常有趣的现象——Oracle自动收集统计信息机制，通过本机制可以使SQL优化器了解数据的数据量和分布情况，以使SQL优化器能够得到更恰当的SQL执行计划，得到更好的性能表现。

　　问题是这样的，在执行疲劳测试的过程中，Oracle数据库服务器在晚上10点时，CPU利用率突然升高25%左右，并且波动较大，如图一。经过与Oracle公司专家咨询和查阅当天晚上10点的日志信息得知，Oracle数据库有一个自动收集统计信息的机制，由于这种机制的存在，导致数据库CPU在这段时间会进行一个突然的波动。

　图一：数据库服务器CPU利用率（全疲劳测试场景）
　　我们对本问题进行了进一步的验证。通过查阅资料得知Oracle默认的收集统计信息的时间为晚上10点（周一到周五，一般4小时），早上6点（周六，周日，20个小时），首先通过执行SQL查看Oracle数据库默认的收集统计信息：

1. select t1.window_name,t1.enabled,t1.repeat_interval,t1.duration,t1.next_start_date,t1.*
   
2. 
   
3. 　　from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
   
4. 
   
5. 　　where t1.window_name=t2.window_name
   
6. 
   
7. 　　and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

复制代码

结果如下：

由图可知：Oracle默认的收集统计信息的时间确实为晚上10点（周一到周五，一般4小时），早上6点（周六，周日，20个小时）。接着我们通过在非发压测试情况下对10.230.133.145
　　这个数据库从头验证Oracle数据库自动收集统计信息机制的存在。
　　场景一：默认状态下进行监控结果（周二）


由图可知：在默认状态下在晚上10点时数据库CPU有明显提升。
　　场景二：通过执行SQL关闭ORACLE数据库自动收集统计信息机制（周三）
　　exec  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>’auto optimizer stats collection’,operation=>NULL,window_name=>NULL);
　　SQL执行结果如下：


由图可知：周三ENABLED的状态已变更为FALSE，我们再来看监控的结果：

由监控结果可知：关闭周三当天的ORACLE自动收集统计信息机制后，在晚上10点的时候，数据库CPU表现平稳，并没有出现突然升高的现象。
　　为了进一步验证我的猜想，我们再次通过SQL对ORACLE数据库自动收集统计信息的时间进行更改(将周五ORACLE数据库自动收集统计信息的时间更改为上午10点)。
　　场景三：通过执行SQL改变ORACLE数据库自动收集统计信息机制时间（周五）

1. begin
   
2. 
   
3. 　　dbms_scheduler.disable(name => 'TUESDAY_WINDOW');??
   
4. 
   
5. 　　dbms_scheduler.set_attribute(
   
6. 
   
7. 　　name????? => 'TUESDAY_WINDOW',???????????????????????????????
   
8. 
   
9. 　　attribute => 'REPEAT_INTERVAL',???????????????????????????????
   
10. 
    
11. 　　value???? => 'freq=daily;byday=FRI;byhour=10;byminute=0; bysecond=0');?
    
12. 
    
13. 　　dbms_scheduler.enable(name => 'TUESDAY_WINDOW');
    
14. 
    
15. 　　end;

复制代码

SQL执行结果如下：

由图可知：周五的收集统计信息时间更改为了上午10点，我们再来看监控的结果：

由图可知：在将ORACLE数据集自动收集统计信息机制时间更改为上午10点后，在上午10点，CPU出现了明显的升高，进一步验证了ORACLE数据库自动收集统计信息机制的存在。
　　ORACLE自动收集统计信息机制的存在或多或少会给数据库CPU带来一些影响，尤其是执行疲劳测试的时候。我们可以通过场景二中关闭自动收集统计信息机制或通过场景三中改变自动收集统计信息机制的时间来避开由于ORACLE数据库自动收集统计信息对测试带来的影响。
　　希望通过此篇对Oracle数据库自动收集统计信息的验证，对做性能测试的小伙伴起到一定的借鉴意义。