![824ac00824216cf1cdeb189342888bda.png](https://i-blog.csdnimg.cn/blog_migrate/37d34986ccddc08e878484829e976356.jpeg)
于树文
云技术管理处
在Oracle的11g版本中,统计信息为自动收集功能。在部署安装11g Oracle软件过程中,其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)。有时候在生产环境中,还会对一些对象做手动统计信息的搜集。统计信息对执行计划的准确性有着至关重要的参考,本文介绍自动统计信息搜集及常规操作。
01
什么是统计信息
说统计信息前,先要说下Oracle的优化器。
Oracle数据库中的优化器又叫查询优化器(Query Optimizer)。它是SQL分析和执行的优化工具,负责生成、制定SQL的执行计划。Oracle的优化器有两种,即基于规则的优化器(RBO)与基于代价的优化器(CBO)。
RBO自ORACLE 6以来被采用,一直沿用至ORACLE 9i。ORACLE 10g开始,ORACLE已经彻底丢弃了RBO,它有着一套严格的使用规则,只要按照它去写SQL语句,无论数据表中的内容怎样,也不会影响到“执行计划”,也就是说RBO对数据不“敏感”。它根据ORACLE指定的优先顺序规则,对指定的表进行执行计划的选择。比如在规则中,索引的优先级大于全表扫描;在RBO中,SQL的写法往往会影响执行计划,这就要求开发人员非常了解RBO的各项细则,有时候不理解规则写出来的SQL脚本性能可能非常差。
CBO是一种比RBO更加合理、可靠的优化器。它是从ORACLE 8中开始引入,但到ORACLE 9i中才逐渐成熟,在ORACLE 10g中完全取代RBO。CBO是计算各种可能“执行计划”的“代价”,即COST,从中选用COST最低的执行方案,作为实际运行方案。它依赖数据库对象的统计信息,统计信息的准确与否会影响CBO做出最优的选择。
如果对一次执行SQL时发现涉及对象(表、索引等)没有被分析、统计过,那么ORACLE会采用一种叫做动态采样的技术,动态的收集表和索引上的一些数据信息。
优化器统计范围:
表统计:
--行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;
列统计:
--列中唯一值的数量(NDV),NULL值的数量,数据分布;
--DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;
索引统计:
--叶块数量,等级,聚簇因子;
--DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;
系统统计:
--I/O性能与使用率;
--CPU性能与使用率;
--存储在aux_stats$中,需要使用dbms_stats收集,I/O统计在X$KCFIO中;
02
统计信息自动搜集策略
自动统计收集默认在夜间进行,对所有更改活动中等的对象自动统计应该足够。
使用如下命令查看:
set lines 180 pages 9999
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a70
col ENABL for a15
col DURATION for a15
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
ENABLED,
ACTIVE,
duration
FROM dba_scheduler_windows;
![9e71f4c53b7506d1035bbca9862e6414.png](https://i-blog.csdnimg.cn/blog_migrate/23bef5909826e8345b9bea0cf80c3a93.png)
可以看出默认每周一至周五22:00开始,历时4小时进行统计信息的自动收集;每周六和周日早上06:00开始,历时20小时收集统计信息;
手动收集统计信息的情况:
1. 频繁变化的表在白天的活动期间被TRUNCATE/DROP并重建;
2. 块加载超过本身总大小10%的对象;
3. 对于分区表,如果仅仅是一个分区有了较大改动,只需要收集一个分区的统计,但是收集整个表的分区也是必要的;
4. 其他。
03
自动收集功能启用与禁用
1、查看自动收集统计信息的任务及状态
SQL> select client_name,status from dba_autotask_client;
![dcfc9a9f3d6846337b1ca68de1e16f2e.png](https://i-blog.csdnimg.cn/blog_migrate/555f0f6dba91ec0fb7d48cbdb6a41612.png)
其中"auto optimizer stats collection"便是要寻找的自动收集统计信息的任务名称,它的状态目前是启用状态。
查看该任务所包含的执行窗口
SQL> SELECT *
FROM DBA_SCHEDULER_WINGROUP_MEMBERS
WHERE WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';
![c2ad041a5a363779e4eb4a07964cda1e.png](https://i-blog.csdnimg.cn/blog_migrate/7c4c02913b6ace13e4ea22e351f84136.png)
2、禁止自动收集统计信息的任务
禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成。
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
![fc8964b5dc8a61bf3172c93c31c033a6.png](https://i-blog.csdnimg.cn/blog_migrate/f3a0319be36bbbe2b9a9f582d7818a18.png)
此时"auto optimizer stats collection"任务已经被禁用。
3、启用自动收集统计信息的任务
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
![7aff1be83e2dbb8074f3d20fdc104e66.png](https://i-blog.csdnimg.cn/blog_migrate/67f09c2f48fd719780aafc747d006877.png)
自动功能带来了统计数据采集上的便捷,但是Oracle11g自动收集统计信息的时间是22:00--2:00。这个时间段往往是业务的高峰期,给原本已紧张的系统带来更大的负担。所以,应该把自动执行的时间改到空闲的时段。
4、获得当前自动收集统计信息的执行时间
set lines 180 pages 9999
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a70
col ENABL for a15
col DURATION for a15
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
DURATION
FROM dba_scheduler_windows;
![ab90e8a12316f38768a2750d98e083d0.png](https://i-blog.csdnimg.cn/blog_migrate/249244cf6a60135c4d8696eac30d94cd.png)
其中:
WINDOW_NAME:任务名
REPEAT_INTERVAL:任务重复间隔时间
DURATION:持续时间
修改自动任务步骤如下:
(1)停止任务:
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."FRIDAY_WINDOW"',
force => TRUE);
END;
/
(2)修改任务的持续时间,单位是分钟:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."FRIDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(180,'minute'));
END;
/
(3)开始执行时间,BYHOUR=2,表示2点开始执行:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."FRIDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;
/
(4)开启任务:
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."FRIDAY_WINDOW"');
END;
/
(5)查看修改后的情况:
set lines 180 pages 9999
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a70
col ENABL for a15
col DURATION for a15
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
ENABLED,
ACTIVE,
duration
FROM dba_scheduler_windows;
04
统计信息的导出导入
此操作常用于对表做了大批量的DML,或者数据库刚迁移完,还没到自动统计信息搜集的时候。使用DBMS_STATS包导出与导入统计信息。
1.首先创建保存统计信息的信息表
STAT_TABLE
begin
dbms_stats.create_stat_table(
ownname => 'CLOUD',
stattab => 'STAT_TABLE');
end;
/
2.导出统计信息到stat_table里
begin
dbms_stats.export_table_stats(
ownname => 'CLOUD',
tabname => 'EMP',
stattab => 'STAT_TABLE');
end;
/
保存原有统计信息到STAT_TABLE中,目的是3过程不成功,执行步骤4恢复原表的统计信息。
3.收集统计信息
begin
dbms_stats.gather_table_stats(
ownname => 'CLOUD',
tabname => 'EMP');
end;
/
4.导入统计信息到原表里
begin
dbms_stats.import_table_stats(
ownname => 'CLOUD',
tabname => 'EMP',
stattab => 'STAT_TABLE');
end;
/
05
总 结
Oracle统计信息给优化器带来很大的便利,优化器类似于导航,统计信息相当于统计每个道路的车辆(数据量),使导航能够正确的统计每条路线汽车的数量,以便规划出一条畅通快速且消耗时间较小的导航路线图(执行计划)。希望大家能够从文中对执行计划有简单的认识。
补 充:
其他dbms统计信息常用包如下,这里不做详细介绍,各位有兴趣可查看Oracle官方手册。
EXPORT_COLUMN_STATS:导出列的分析信息
EXPORT_INDEX_STATS:导出索引分析信息
EXPORT_SYSTEM_STATS:导出系统分析信息
EXPORT_TABLE_STATS:导出表分析信息
EXPORT_SCHEMA_STATS:导出方案分析信息
EXPORT_DATABASE_STATS:导出数据库分析信息
IMPORT_COLUMN_STATS:导入列分析信息
IMPORT_INDEX_STATS:导入索引分析信息
IMPORT_SYSTEM_STATS:导入系统分析信息
IMPORT_SCHEMA_STATS:导入方案分析信息
IMPORT_DATABASE_STATS:导入数据库分析信息
GATHER_INDEX_STATS:分析索引信息
GATHER_TABLE_STATS:分析表信息,当cascade为true时,分析表、列(索引)信息
GATHER_SCHEMA_STATS:分析方案信息
GATHER_DATABASE_STATS:分析数据库信息
GATHER_SYSTEM_STATS:分析系统信息
![5b58311a0a53c0695893d645541337f5.gif](https://i-blog.csdnimg.cn/blog_migrate/7ca51641e51bf21f7088fb66cd3df320.gif)
![e71cd0adc5e23a9e3d8dfec661fc5734.gif](https://i-blog.csdnimg.cn/blog_migrate/c570ab27735c4ab1f64b86d531d822a8.gif)