2.表统计信息,索引统计信息,列统计信息相关知识

1.查看统计信息

Oracle数据库统计信息会存储在数据字典里。
可以使用sosi.txt 脚本查看统计信息。
sosi.txt 来自MOS:ID 21412.1 
sosi.txt 支持分区表,它的显示分为三个部分:表级别的统计信息,分区级别的统计信息,子分区级别的 统计信息。

使用时需要传入入用户名和表名。

2.表的统计信息

统计信息用于描述Oracle数据库里表的相信信息,包含了记录数,表块的数量,平均行长度等典型的维度。
这些信息存储在数据字典基表:TAB$,TABPART$,TABSUBPART$,可以通过数据字典：
DBA_TABLES,
DBA_TAB_PARTITIONS,
DBA_TAB_SUBPARTITIONS
来分别查看表,分区表,分区表的分区和分区表的子分区的统计信息。


NUM_ROWS 存储的是目标表的记录数,是计算结果集的Cadinality的基础，而结果集的Cardinality则往往直接
决定了CBO计算的成本值,比如对于嵌套循环连接而言,驱动结果集的Cardinality的值越大,则走嵌套循环连接的成本值就会越大。

create table test(id number,name varchar2(10));

declare 
i number;
begin 
	for i in 1..10000 loop 
		insert into test values(100,'xsq');
	end loop;
	commit; 
end 
/

select dump(100,16) from dual;  --查看100的16进制长度是多少字节。
select dump('xsq',16) from dual; --查看xsq转换为16字节占用多少字节。


--表历史统计信息查看
select object_id from dba_objects where object_name='DJDGDS_TAB';
224099 
alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS';
--查看具体对象历史统计信息情况。
select savtime,rowcnt,blkcnt,avgrln,analyzetime from sys.wri$_optstat_tab_history where obj#=224099;

--在oracle里,一旦一个表被执行了truncate操作,那么它就会成为当天自动收集统计信息作业要收集统计信息的目标对象。

在导入大量数据后应及时收集统计信息后才进行相关后续业务处理(包括查询),
否则可能由于实际数据量和统计信息里记录的数据量存在巨大差异而导致CBO选择错误的执行计划。

3.索引的统计信息 

Oracle数据库里索引的详细信息,它包括索引的层级,叶子块的数量,聚簇因子等典型的维度。
这些信息存储在:IND$,INDPART$,INDCOMPART$,INDSUBPART$ 等。
可以通过 DBA_INDEXES,DBA_IND_PARTITIONS 和 DBA_IND_SUBPARTITIONS 来分别查看索引,分区索引,本地分区索引的自分区。

上述数据字典中的BLEVEL存储的就是目标索引的层级,它表示的是根节点到叶子块的深度,
BLEVEL被CBO用于计算访问索引叶子块的成本,BLEVEL 的值越大,则从根节点到叶子节点所需要
访问的数据块就会越多,耗费的I/O就会越多,访问索引的成本就会越大。

在oracle里,如果要降低目标B树索引的层级,可以通过rebuild该索引的方式来实现。

create table t1 as select * from dba_objects; 
create index idx_t1_obj_id on t1(object_id);
--使用analyze分析索引。
analyze index idx_t1_obj_id validate structure;

HEIGHT=BLEVEL+1  
select name,height 高度,lf_rows 叶子块中行数,lf_blks 叶子块的个数 ,del_lf_rows 删除行数 from index_stats;
IDX_T1_OBJ_ID 2 59463 132 0 
delete from t1 where rownum<59496;
commit;
analyze index idx_t1_obj_id validate structure;
select name,height ,lf_rows ,lf_blks  ,del_lf_rows  from index_stats;
IDX_T1_OBJ_ID 2 59463 132 59463 
--可以看到数据删除后,层级并未改变。


--重建索引
alter index idx_t1_obj_id rebuild;
analyze index idx_t1_obj_id validate structure;
select name,height ,lf_rows ,lf_blks  ,del_lf_rows  from index_stats;
IDX_T1_OBJ_ID 1 0 1 0 

LEAF_BLOCKS:存储的就是目标索引叶子块的数量,它被CBO用于计算对目标索引做索引全扫描,索引范围扫描时的
成本。目标索引叶子块的数量越多,则对目标索引做索引全扫描和索引范围扫描的成本值就会越大。

CLUSTER_FACTOR:存储的就是目标索引的聚簇因子,聚簇因子是指按照索引键值排序的索引行
和存储于对应表中数据行的存储顺序的相似度。

DISTINCT_KEYS:存储的就是目标索引的索引键值的distinct值的数量.对于唯一索引,
在没有NULL值的情况下,DISTINCT_KEYS 的值等于表的记录数。

AVG_LEAF_BLOCKS_PER_KEY:存储的是目标索引每个distinct 索引键值所占用
的叶子块数量的平均值。一个KEY占用几个索引叶子块。

AVG_DATA_BLOCKS_PER_KEY:存储的是目标索引每个distinct 索引键值对应表中
数据行所在数据块数量的平均值。一个KEY占用几个数据块。


如果聚簇因子的值接近对应表的表块的数量,则说明目标索引行和存储于对应表中的数据行的存储顺序相似度非常高。
如果聚簇因子的值接近对应表的记录数,则说明目标索引索引行和存储与对应表中的数据行的存储顺序的相似度极低,
这意味着Oracle走索引范围扫描取得目标rowid再回表访问对应表的数据时,相邻的索引所对应的rowid极有可能
不处于同一个表块中。这样将耗费更多的IO;

聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理IO,
索引聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。

--查看数据在哪个文件哪个块上。
select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location 
from t1 order by location,id; 

Oracle中降低聚簇因子的唯一方法就是将表中的数据按照目标索引的索引键值排序后重新存储。
create table t2 as select * from t1 order by id; 
create index idx_t2 on t2(id);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',method_opt=>'for all columns size auto',CASCADE=>'true',estimate_percent=>100);

索引范围扫描的成本可以近似看作是聚簇因子成正比.

修改索引的聚簇因子:
exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_T1',clstfct=>1000000,no_invalidate=>false);
当把索引的聚簇因子修改到很大的值时,走索引扫描的成本大于走全表扫描的成本,此时不再走索引范围扫描,而是走全表扫描。

4.列统计信息

列的统计信息包含:列的DISTINCT值的数量,列的 NULL值的数量,列的最小值,列的最大值。
列统计信息存储在数据字典基表:HIST_HEAD$
数据字典表有:
DBA_TAB_COL_STATISTICS   --普通表列统计信息
DBA_PART_COL_STATISTICS  --分区表列统计信息
DBA_SUBPART_COL_STISTICS --分区表的子分区统计信息

NUM_DISTINCT:存储目标列的DISTINCT值的数量。CBO用它来做等值查询的SELECTIVITY;
NUM_NULLS:目标列NULL值数量:CBO会用NUM_NULLS的值来调整对有NULL值的目标列做等值查询的可选择率。
LOW_VALUE:目标列最小值。CBO用LOW_VALUE,HIGH_VALUE来评估对目标列做范围查询时的可选择率。
HIGH_VALUE:目标列最大值。
DENSITY:目标列的密度。与直方图有关。
NUM_BUCKETS:目标列所用的桶的数量。与直方图有关。

create table t7 as select * from dba_objects;
select count(*) from t7 where object_id is null;
--不收集直方图的方式收集统计信息。
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T7',method_opt=>'for all columns size 1',CASCADE=>'true',estimate_percent=>100);

--查看OBJECT_ID列的统计信息。
SELECT LOW_VALUE,HIGH_VALUE,NUM_DISTINCT,NUM_NULLS FROM DBA_TAB_COL_STATISTICS 
WHERE TABLE_NAME='T7' AND COLUMN_NAME='OBJECT_ID';

SELECTIVITY=(1/NUM_DISTINCT)*((NUM_ROWS-NUM_NULLS)/NUM_ROWS)
=非空值的比例/唯一值的个数。

--SQL绑定变量的值的获取:
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,
dbms_sqltune.extract_bind(bind_data,5).value_string bind5,
dbms_sqltune.extract_bind(bind_data,6).value_string bind6
from dba_hist_sqlstat 
where sql_id='d3zcyuqhgnfpj'
order by snap_id;

--查询表的大小,分析时间等信息。
select table_name,num_rows,blocks,avg_row_len,
partitioned,to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),num_rows*avg_row_len/1024/1024/0.9 est_m 
from dba_tables where table_name='TRANS_STBL';

--查看列的统计信息
select low_value,high_value form dba_tab_col_statistics where table_name='TRANS_STBL' and column_name='TRF_DATE';
LOW_VALUE      HIGH_VALUE
786D0B9010101  786E0BF161609 

--日期的16进制转换为10进制。
var temp date;
exec dbms_stats.convert_raw_value('786D0B9010101',:temp);
2009-11-9 

var temp date;
exec dbms_stats.convert_raw_value('786E0BF161609',:temp);
2010-11-15 21:21:08 

where条件中的值不在LOW_VALUE和HIGH_VALUE之间就会出现谓词越界,CBO无法判断这对该列的查询条件的可选择率。
只能使用估算值,如果估算值与实际严重不符,估算出的CADINALITY与实际有偏差就会走错执行计划。

5.总结

     无论是表的统计信息，索引的统计信息，列的统计信息都是非常重要的，是CBO生成执行计划时的重要依据。

     这里比较经典的语句是:

（1）获取给定SQL_ID的SQL对应的绑定变量的值。

（2）修改聚簇因子的大小干预SQL走索引扫描还是全表扫描。

（3）16进制的数据通过Oracle的函数转为为10进制方便读取的方法。

（4）表的历史统计信息收集查看。

这些经典的语句对于性能优化非常重要。