oracle 10g的自动统计分析

Oracle 10g下cbo的自动统计分析功能很大程序的解决了因为9i下统计信息失效或者陈旧导致的sql语句选择了不合适的执行计划，该统计分析功能是通过dba_scheduler_jobs视图下的gather_stats_job来对系统的对象进行统计分析，gather_stats_job在星期一到星期五在晚上10点执行，双休从早上6点到星期一早上6点，该统计分析由于需要对数据库的所有对象进行分析，所以可能该job并没有完成，查看dba_scheduler_job_run_details可以对job执行是否完毕，可以看出在大型的生产库中很多情况该job都没有执行完毕就中止了。

SQL> select log_date,job_name,status from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_id;

LOG_DATE JOB_NAME STATUS

-------------------------------------------------- -------------------- --------------------

07-4月 -12 06.01.40.434786 上午 +08:00 GATHER_STATS_JOB STOPPED

09-4月 -12 10.27.13.012514 下午 +08:00 GATHER_STATS_JOB STOPPED

10-4月 -12 10.33.26.498880 下午 +08:00 GATHER_STATS_JOB STOPPED

11-4月 -12 10.08.14.335380 下午 +08:00 GATHER_STATS_JOB STOPPED

12-4月 -12 10.24.57.878692 下午 +08:00 GATHER_STATS_JOB STOPPED

13-4月 -12 10.09.23.672033 下午 +08:00 GATHER_STATS_JOB STOPPED

14-4月 -12 06.01.43.666416 上午 +08:00 GATHER_STATS_JOB STOPPED

那么gather_stats_job调用的是什么了，通过视图dba_scheduler_program可以查看

select program_action from dba_scheduler_programs where program_name='GATHER_STATE_GROG'

dbms_stats.gather_database_stats_job_proc

可以看出gather_stats_job调用dbms_stats.gather_database_stats_job_proc的procedure来检测统计信息缺失和失效的对象，然后确定优先级 进行统计和分析信息。

（统计信息缺失可能都知道，但是失效如何解释，oracle中规定了修改的行达到了数据的10%，则就意味统计信息失效，关于这个修改的行则是oracle的另外一个视图sys.dba_tab_modification中的记录来对表等对象的统计信息是否失效来进行判断）

Cbo进行自动统计分析，不过如果查看生产系统中的表可以发现user_tables中的last_analyze_time不是近端时间的表大有所在，cbo进行自动统计分析需要表中的缺少统计信息，或者修改超过10%，如果没有达到，cbo下的自动分析并不会对该对象进行分析。

Cbo正是因为其根据统计信息而采取的最优的执行计划而减小了无谓的消耗，提高sql的性能，可以这么说cbo是完全依赖于统计信息，如果缺失cbo可以采取动态取样，oracle 10g下optimizer_dynamic_sampling默认是2，那么9i下的optimizer_dynamic_sampling是1。

（1） sql语句中必须有相应的至少一个没有采样信息的表

（2） 未分析的表没有索引

（3） 未分析的表占用的数据块要大于动态采样默认的数据块（32个）

9i下需要对表进行alter table tablename monitor 才会对表的修改进行监控，进而写入到dba_tab_monitoring视图中，而10g下默认都会对表进行监控。

这里说一下最近犯过的一个不知道算不算错误，生产库由于出现了大量的db file sequential read影响了性能，由于查看sql语句发现该逻辑读都很小，基本都是主键产生等待，根据awr的sga advisor增加了sga大小，然后重启数据库，居然发现了一个30多g的大表主键不停的fts，这可以说非常的不正常，隐式转换，统计信息失效但是由于设置的optimizer_dynamic_sampling觉得不是统计信息失效造成的，很有可能是隐式转换或者cbo的错误执行计划导致，由于没有metalink也无法去追踪具体的由来的，当时查看自动分析依然存在在晚上10点后会进行自动统计分析，第二天后业务恢复正常，选择了unique index。

不过当时自己其实完全可以利用dbms_stats.restore_table_stats(‘DESKTOP’,’ARTICLE’,sysdate-60/1440)恢复统计信息到一个小时前，不过案例不在，也是一个很痛苦的事情。

而统计信息保存的时间可以通过select dbms_stats.get_stats_hisory_retention from dual;

不过和undo_retention一样该参数并不是一个担保值

Select dbms_stats.get_stats_history_availability from dual;

统计信息的可用时间。

关闭cbo的自动统计分析dbms_scheduler.disable(‘GATHER_STATS_JOB’)，cbo下的自动分析需要消耗一定量的latch可能造成latch竞争，不过合理的统计信息却是执行计划合理的保证，所以这里也没有什么关于是否开启于关闭自动统计分析的建议，不过如果能真正了解cbo具体的行为，那么根据你自己的业务情况，相信肯定有个合适的选择。

[@more@]

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

转载于:http://blog.itpub.net/25362835/viewspace-1058146/