1.需求描述:
Oracle不定时更新数据表的统计信息,当数据量增多时,更新频率逐渐变慢;
当编写某些大的查询sql时,如访问最近一个月的销售汇总,调优后的评估值在1000左右,执行时间在10几分钟;但是一两个月后,执行时间突然增长,4,5个小时都查询不出了结果;
2.需求分析:
通过分析发现,某些大表的统计日期已经有2,3个月没有更新了。
select table_name, num_rows, last_analyzed from user_tables ;
通过更新大表的统计时间,执行时间又达到预期的效果:
call dbms_stats.gather_table_stats('用户名','表名');
3.自动化脚本
将如下两个脚本存放到同一目录下,注意修改数据库配置参数,然后使用windows计划任务定期执行动态更新;
脚本1:自动更新Oracle统计信息.bat
title 自动更新Oracle统计信息[%date%%time%]
sqlplus userName/passWord@192.168.0.1/db_name @gen_sqls.sql | find "call dbms_stats.gather_table_stats">stats_sqls.sql
echo exit>>stats_sqls.sql
sqlplus userName/passWord@192.168.0.1/db_name @stats_sqls.sql
echo 脚本执行完成
脚本2:gen_sqls.sql
select
'call dbms_stats.gather_table_stats(''用户名'','''|| TABLE_NAME ||''');' as sqls
from user_tables a
where a.last_analyzed <sysdate-7
and num_rows > 1000
order by num_rows ;
exit;