在数据库运行过程中,当表中数据发生较大变化时,之前的统计信息可能不准确,此时如果执行SQL语句查询此表的数据,基于代价的执行计划可能不准确,导致执行效率不高。
数据库提供对系统中表统计信息的自动收集功能,可以按照固定时间周期性执行自定义任务,或根据表数据的变化率执行自定义任务,进行表数据统计信息的收集,使得统计信息更加精确,从而提高执行效率。
自动收集定时任务
数据库创建的同时,默认在SYS用户下创建两个定时任务:
定时任务一:收集全库的统计信息,此任务每天凌晨1:00执行,缺省数据采样率为10%。
定时任务二:收集变化的数据表的统计信息,此任务每隔15分钟执行一次,系统会判断数据库中所有表数据的变化率,仅当某个表的数据变化率大于等于10%时,才收集该数据表的统计信息,缺省数据采样率为10%。
使用SYS用户通过SELECT * FROM DB_JOBS;命令可以查看这两个定时任务。
收集统计信息的两个定时任务默认处于关闭状态,如果需要启动自定义任务,需要同时满足如下两个条件:
调用DBMS_JOB.RUN接口启动定时任务,其中jobid可通过SELECT * FROM SYS_JOBS;获取。
exec DBMS_JOB.RUN(jobid);
commit;
将系统参数CBO设置为ON。
ALTER SYSTEM SET CBO=ON SCOPE=BOTH;
收集统计信息存储过程
系统内置了两个存储过程:GATHER_DB_STATS和GATHER_CHANGE_STATS。
GATHER_DB_STATS:按指定采样率收集整库数据表的统计信息(系统表除外)。
GATHER_CHANGE_STATS:按指定采样率准实时收集系统中达到指定变化率的数据表的统计信息。
配置收集统计信息参数
收集统计信息会消耗大量系统资源,尤其是I/O。通过三个系统配置参数可以控制收集统计信息时对I/O的占用率。
添加流控模式参数:
STATS_COST_LIMIT每次流控限速的采样页面间隔,即每采样多少个页面进行一次I/O sleep。
STATS_COST_DELAY每次sleep的时间。
这两个参数控制统计采样页面时对I/O的持续占用,通过限速使I/O资源每隔一段时间空出,供其他业务使用。从而降低对I/O的持续占用。
添加强制最大采样限制:
ENABLE_SAMPLE_LIMIT添加强制最大采样数据size限制,设置为true将只采样STATISTICS_SAMPLE_SIZE值大小的数据。
通过限制最大采样数据来保证当前temp pool不会和temp表空间产生I/O交换,从而避免资源的消耗,达到降低磁盘I/O的目的。