节选自《基于Oracle的SQL优化》第五章

--收集统计信息
--估算模式，采样比例15%
analyze table t2 estimate statistics sample 15 percent for table;
--计算模式,只收集表的
analyze table t2 compute statistics for table;
--收集所有
analyze table t2 compute statistics;
--删除
analyze index idx_t2 delete statistics;
--针对某列
analyze table t2 compute statistics for columns object_name,object_id;
--一次性收集所有
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);
--分析行迁移，分析索引
analyze table xxx list chained rows into yyy;
analyze index xxx validate structure;

ANALYZE和DBMS_STATS区别

analyze命令不能收集分区表统计信息
analyze不能并行收集统计信息
dbms_stats只能收集与cbo有关的统计信息，其他如行迁移、行链接可以通过analyze分析。

查看统计信息

mos:3112.1 or http://www.dbsnake.net/books —sosi.txt

表的统计信息

--查看历史收集的统计信息
select savtime,rowcnt,blkcnt,avgrln,analyzetime from sys.wri$_optstat_tab_history where obj#=224099;
--truncate 操作后，oracle会自动收集统计信息

索引的统计信息

--降低索引层次
alter index idx_t1 rebuild;
analyze index idx_t1 validate structure;
select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

聚簇引子的含义及重要性

--查看表位置，文件号，block号
select id,dbms_rowid.rowid_relative_fno(rowid||'_'||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id;
--手动调整索引的集簇引子
exec dbms_stats.set_index_stats(ownname='IPRA',indname=>'IDX_T1',clstfct => 100000,no_invalidate=>false);

列的统计信息

列统计信息实际存放在 hist_head$中，包含了列的distinct值的数量，null数量、列最大值、最小值等，dba_tab_col_statistics可查

--查看绑定变量的值
select snap_id,
dbms_sqltune.extract_bind(bind_data,1),value_string bind1,
dbms_sqltune.extract_bind(bind_data,2),value_string bind2,
dbms_sqltune.extract_bind(bind_data,3),value_string bind3,
dbms_sqltune.extract_bind(bind_data,4),value_string bind4 from dba_hist_sqlstat
where sql_id='' order by snap_id;
--检查列统计信息
select low_value,high_value from dba_tab_col_statistics where table_name='' and column_name='';
--换算成时间
var temp date;
exec dbms_stats.convert_raw_value('LOW_VALUE',:temp);
--谓词越界，超出了列统计信息中的范围。最大值 最小值

直方图

在Oracle中，CBO默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间是均匀分布的。并按照此原则计算成本，基数等。

直方图存在HISTGRM$表中，dba_tab_histograms

直方图的类型

Frequency类型直方图

目标列直方图的Bucket的数量等于目标列distinct值的数量。 ENDPOINT_VALUE记录了这些distinct值，ENDPOINT_NUMBER则记录了到此distinct为止总共有多少记录，该值为累加记录。

Frequency类型直方图所对应的Bucket数量不能超过254.Oracle12c不再限制，只适用于那些目标列的distinct值数量小于或等于254的情形。

--收集直方图信息，只有用过的列才会被收集
--eg  auto自动决定使用哪种类型直方图
select * from t where x='x';
exec dbms_stats.gather_table_stats(ownname='MYTEST',tabname=>'T',method_opt=>'for columns size auto X',cascade=>true);
--可以通过以下视图检查
select object_id from dba_objects where object_name='X';
select name,intcol# from sys.col$ where obj#=;
select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=;
select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name='';
select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='';

如果针对文本类型收集直方图，Oracle只会将该文本类型字段的文本值的头32个字节给取出来（实际上15个字节），将其转化为一个浮点数。

Height Balanced 类型直方图

目标列直方图的Bucket的数量会小于目标列的distinct值的数量。

--收集直方图，指定5个Bucket
exec dbms_stats.gather_table_stats(ownname=>'MYTEST',tabname=>'T',method_opt=>'for columns size 5 x',cascade=>true,estimate_percent=>100);

直方图的收集方法

--所有有索引列收集直方图
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto');
--SIZE {integer |REPEAT |AUTO |SKEWONLY}
--只删除empno的直方图信息  integer:1-254,1表示删除
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns empno size 1');

直方图对CBO的影响

当某个列有了直方图统计信息后，cbo会人为对该目标列施加的等值查询条件是一个不安全的为此条件。尤其cursor_sharing设置为similar后。产生更多硬解析。 shared Cursor影响比较大。

再个就是对可选择率的影响。dba_tab_col_statistics DENSITY的值可以卡看做是对目标列施加等值查询条件后的可选择率，在没有直方图统计信息时，值等于1/num_distinct.

使用直方图注意事项

直方图是为了准确评估分布不均匀的列可选择率、结果的Cardinality被引入的，如果目标列分布均匀，不需要收集直方图统计信息。
对于那些从来没有在sql语句where条件中出现的列，无需收集
直方图统计信息可能会影响Shared Cursor的共享，特别是有绑定变量联用时。
在配置Oracle 10g引入的自动统计信息收集作业时，需要特别注意对直方图统计信息的收集策略。

全局统计信息

是只从直接对象本身这一级收集的统计信息，只能通过dbms_stats包来收集

应使用DBMS_STATS包来对分区表收集全局统计信息
应用 DBMS_STATS包中的相关存储过程来删除分区表统计信息
收集分区表统计信息时应一致使用GRANULARITY参数。
尽量不要使用analyze命令来对分区表收集统计信息，尤其不要与DBMS_STATS联合使用。

动态采样

为了更准确地估算where条件出现有关联关系的列时整个where条件的组合可选择率，进而更准确的估算返回结果集Cardinality，Oracle引入了动态采样。

两方面作用

估算准确的选择率
可一定程度上解决这种因临时表没有统计信息而导致的CBO选错执行计划的问题。

动态采样仅适用于那些针对单表或多表关联的第一个驱动表的select、update和delete语句。

动态采样的值(功能默认开启,optimizer_dynamic_sampling)

0:不启用
1：对没有统计信息且没有索引、数据块总数大于默认采样数据块数量
2：对没有统计信息的表启用，使用默认采样数据块数量
3：对没有统计信息和有但cbo无法准确估算启用，默认块数
4：除了满足3，再加那些针对单表且查询条件中至少出现了两列的表启用。默认块数
5：满足4，且采样块数是2倍默认数
6：满足5, 且采样块数是4倍默认数
7：满足6，且采样块数是8倍默认数
8：满足7，且采样块数是32倍默认数
9: 满足8，且采样块数是128倍默认数
10：满足9，采样时使用所有目标表数据块。

默认采样块数为32块，隐含参数”_OPTIMIZER_DYN_SMP_BLKS”。 hint方式：/ + dynamic_sampling(t2,4) /

多列统计信息

可以人为指定存在关联关系的一组目标表上的一个组合列，然后使用DBMS_STATS针对组合列收集统计信息。

select * from t2 where n1=3 and n2=3 and c1='a';
--创建组合列
set serveroutput on
declare
  cg_name varchar2(30);
begin
  cg_name := sys.dbms_stats.create_extended_stats('SOCTT','T2','(n1,n2)');
  dbms_output.put_line(cg_name);
end;
/
--收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',method_opt=>'for columns(n1,n2) size auto',estimate_percent => 100);

系统统计信息

用来描述Oracle数据库所在服务器的系统处理能力的辅助统计信息，主要包括cpu主频、单块读的平均耗费时间、多块读的平均耗费时间等。

收集方法

执行DBMS_STATS.GATHER_SYSTEM_STATS(‘start’)开始收集
接着开始实际地使用系统，最好是能以相同典型的负载实际使用一段时间
最后执行DBMS_STATS.GATHER_SYSTEM_STATS(‘stop’).

系统统计信息会存在于视图 aux_stats$

数据字典统计信息

exec dbms_stats.gather_dictionary_stats;

内部对象统计信息

x$ 相关表,可通过执行计划查看访问了哪些内部表. 内存结构，如果收集统计信息，rac每个节点都需要执行。

exec dbms_stats.gather_fixed_objects_stats;

Oracle里的自动统计信息收集

--oracle10g  gather_stats_job
select program_action from dba_scheduler_programs 
where program_name='GATHER_STATS_PROG';
select * from dba_scheduler_wingroup_members 
where window_group_name='MAINTENANCE_WINDOW_GROUP';
--查询运行时间
select window_name,repeat_interval,duration from dba_scheduler_windows 
where window_name in () and enabled='TRUE';
--查看是否自愿控制，如果没有，会不限制的消耗资源
select window_name,resource_plan from dba_scheduler_windows;
--查看实际执行情况
select actual_start_date,job_name,status from
(select *from dba_scheduler_job_run_details where
job_name='GATHER_STATS_JOB' order by log_date desc)
where rownum<=4;

缺点：

维护窗口太少，不够灵活
没有施加资源控制，过度消耗资源影响数据库运行

--Oracle11g add:GATHER_STATS_PROG
select client_name,task_name,operation_name,status from dba_autotask_task;
--维护窗口
select window_name,autotask_status from dba_autotask_window_clients;
--运行时间
select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE';
--查看是否资源限制
select window_name,resource_plan from dba_scheduler_windows;
--查看历史执行信息
select * from dba_autotask_job_history
where client_name='auto optimizer stats collection' order by window_start_time desc;
--可以看 创建脚本
$?/rdbms/admin/catmwin.sql
--自上次自动统计信息收集后，mon_mods_all$记录了dml操作的记录数是否超过目标表在tab$总记录数的10%
select obj#,inserts,updates,deletes,flags from sys.mon_mods_all$ where obj#=;
--手动监控
exec dbms_stats.flush_database_monitoring_info();

禁用

--10g all
exec dbms_scheduler.disable('GATHER_STATS_JOB');
--11G ALL
exec dbms_auto_task_admin.disable(
client_name=>'auto optimizer stats collection',
operation=> NULL, window_name=>NULL);
--10g one of all
begin
  dbms_scheduler.disable(
  name=>'WEEKNIGHT_WINDOW',
  force=>TRUE);
end;
/
--11g one of all
begin
  dbms_scheduler.disable(
  name=>'MONDAY_WINDOW',
  force=TRUE);
  end;
  /
--oracle10g 默认收集直方图，修改为止收集之前有直方图信息的表
select  dbms_stats.get_param('METHOD_OPT') from dual;
exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
--oracle11g
exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

Oracle里应如何收集统计信息

建议额外收集下系统统计信息
系统有大量数据插入、删除，及时收集
新上线系统及时收集
日期字段及时收集统计信息，避免为此越界

【统计信息】Oracle统计信息

ANALYZE和DBMS_STATS区别

查看统计信息

表的统计信息

索引的统计信息

聚簇引子的含义及重要性

列的统计信息

直方图

直方图的类型

Frequency类型直方图

Height Balanced 类型直方图

直方图的收集方法

直方图对CBO的影响

使用直方图注意事项

全局统计信息

动态采样

多列统计信息

系统统计信息

数据字典统计信息

内部对象统计信息

Oracle里的自动统计信息收集

禁用

Oracle里应如何收集统计信息