第5章：Oracle里的统计信息

统计信息"/>

第5章：Oracle里的统计信息

1 Oracle统计信息简介

1. 统计信息是一组存在数据字典中的数据，它门从多个维度描述了Oracle数据库里对象的详细信息

2. CBO会利用统计信息来计算目标SQL的成本，最后从所有可能的执行路径中，选取成本最小的作为最终执行计划

3. 统计信息按描述的对象的不同分为六种

   1. 表的统计信息
   2. 索引的统计信息
   3. 列的统计信息
   4. 数据字典的统计信息
   5. 系统的统计信息
   6. 内部对象的统计信息

2 收集与查看统计信息

2.1 收集统计信息

1. 可以通过analyze或dbms_stats来收集统计信息

2.1.1 analyze

1. 示例

   --1. 索引建立后会自动建立统计信息，可以通过如下语句删除该索引统计信息
   analyze index idx_t2 delete statistics;
   --2. 对t2表，以估算模式(15%抽样)收集统计信息--a. 可以通过estimate估算模式或compute计算模式收集统计信息--b. 计算模式会扫描目标对象所有数据，所以统计信息和实际信息匹配--c. estimate模式收集到的统计信息与实际信息不完全匹配
   analyze table t2 estimate statistics sample 15 percent for table;
   --3. 对t2表，以计算模式收集统计信息
   analyze table t2 compute statistics for table;
   --4. 对t2表的object_name，object_id列，以计算模式收集统计信息--a. 使用analyze对同一对象(t2)多次收集统计信息，最后一次的统计信息会将之前一次的统计信息覆盖
   analyze table t2 compute statistics for columns object_name，object_id;
   --5. 对表、列同时收集统计信息
   analyze table t2 compute statistics for table for columns object_name，object_id;
   --6. 对索引收集统计信息，注意idex_t2和之前的t2不是一个对象，所以t2的统计信息不会被抹掉
   analyze index idx_t2 compute statistics;
   --7. 同时删除表、表上的列、表中所有索引的统计信息，其实就是之前的收集方式不再加for table/columns
   analyze table t2 delete statistics;
   --8. 同时收集表、表上的列、表中所有索引的统计信息，其实就是之前的收集方式不再加for table/columns
   analyze table t2 compute statistics;
   

2.1.2 dbms_stats

1. dbms_stats可以看作是加强版的analyze

2. gather_table_stats：收集表、列、索引的统计信息

3. gather_index_stats：收集索引统计信息

4. gather_schema_stats：收集指定schema下所有对象统计信息

5. gather_database_stats：收集全库所有对象统计信息

6. 示例

   --1. 对scott用户上的表t2，以估算模式(15%抽样)，收集统计信息
   dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);
   --2. 计算模式：就是将estimate_percent参数值设为100或null
   --3. 对表t2、object_name列、object_id列，以计算模式收集统计信息，注意dbms_stats无法做到像analyze一样，只对列而不对表收集统计信息
   dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name object_id',cascade=>false);
   --4. 以计算模式，收集索引统计信息
   exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'IDX_T2',estimate_percent=>100);
   --5. 删除表、列、索引的统计信息
   exec dbms_stats.delete_table_stats(ownname=>'SCOTT',tabname=>'T2');
   --6. 收集表、表中列、表上索引的统计信息，cascade表示级联收集，也就是同时收集索引统计信息
   exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);
   

2.1.3 analyze和dbms_stats区别

1. analyze无法正确收集分区表统计信息，因为analyze只能收集最低层次对象统计信息，然后再汇总推导出高一级对象的统计信息，例如对于有子分区的分区表，analyze只能收集子分区的统计信息，然后推导出分区级和表级的统计信息，对于表的总行数这种可以推导得到的信息，不会有影响，但对于列上的distinct值数(num_distinct)这种无法进行推导的统计信息，就会收集不准确

2. analyze无法并行收集统计信息，导致收集统计信息时间过长，dbms_stats可以

   --指定并行度4
   dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true,degree=>4);
   

3. dbms_stats无法收集与CBO无关的统计信息

2.2 查看统计信息

1. 查看统计信息脚本：.txt

   set echo off
   set scan on
   set lines 150
   set pages 66
   set verify off
   set feedback off
   set termout off
   column uservar new_value Table_Owner noprint
   select user uservar from dual;
   set termout on
   column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
   select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
   /
   undefine table_name
   undefine owner
   prompt
   accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
   accept table_name  prompt 'Please enter Table Name to show Statistics for: '
   column TABLE_NAME heading "Table|Name" format a15
   column PARTITION_NAME heading "Partition|Name" format a15
   column SUBPARTITION_NAME heading "SubPartition|Name" format a15
   column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
   column BLOCKS heading "Blocks" format 999,990
   column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990column AVG_SPACE heading "Average|Space" format 9,990
   column CHAIN_CNT heading "Chain|Count" format 999,990
   column AVG_ROW_LEN heading "Average|Row Len" format 990
   column COLUMN_NAME  heading "Column|Name" format a25
   column NULLABLE heading Null|able format a4
   column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
   column NUM_NULLS heading "Number|Nulls" format 9,999,990
   column NUM_BUCKETS heading "Number|Buckets" format 990
   column DENSITY heading "Density" format 990
   column INDEX_NAME heading "Index|Name" format a15
   column UNIQUENESS heading "Unique" format a9
   column BLEV heading "B|Tree|Level" format 90
   column LEAF_BLOCKS heading "Leaf|Blks" format 990
   column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
   column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
   column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
   column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
   column COLUMN_POSITION heading "Col|Pos" format 990
   column col heading "Column|Details" format a24
   column COLUMN_LENGTH heading "Col|Len" format 9,990
   column GLOBAL_STATS heading "Global|Stats" format a6
   column USER_STATS heading "User|Stats" format a6
   column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
   column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10prompt
   prompt ***********
   prompt Table Level
   prompt ***********
   prompt
   select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_tables t
   where owner = upper(nvl('&&Owner',user))
   and table_name = upper('&&Table_name')
   /
   selectCOLUMN_NAME,decode(t.DATA_TYPE,'NUMBER',t.DATA_TYPE||'('||decode(t.DATA_PRECISION,null,t.DATA_LENGTH||')',t.DATA_PRECISION||','||t.DATA_SCALE||')'),'DATE',t.DATA_TYPE,'LONG',t.DATA_TYPE,'LONG RAW',t.DATA_TYPE,'ROWID',t.DATA_TYPE,'MLSLABEL',t.DATA_TYPE,t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||decode(t.nullable,'N','NOT NULL','n','NOT NULL',NULL) col,NUM_DISTINCT,DENSITY,NUM_BUCKETS,NUM_NULLS,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_tab_columns t
   where table_name = upper('&Table_name')
   and owner = upper(nvl('&Owner',user))
   /select INDEX_NAME,UNIQUENESS,BLEVEL BLev,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_indexes t
   where table_name = upper('&Table_name')
   and table_owner = upper(nvl('&Owner',user))
   /
   break on index_name
   selecti.INDEX_NAME,i.COLUMN_NAME,i.COLUMN_POSITION,decode(t.DATA_TYPE,'NUMBER',t.DATA_TYPE||'('||decode(t.DATA_PRECISION,null,t.DATA_LENGTH||')',t.DATA_PRECISION||','||t.DATA_SCALE||')'),'DATE',t.DATA_TYPE,'LONG',t.DATA_TYPE,'LONG RAW',t.DATA_TYPE,'ROWID',t.DATA_TYPE,'MLSLABEL',t.DATA_TYPE,t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||decode(t.nullable,'N','NOT NULL','n','NOT NULL',NULL) col
   from dba_ind_columns i,dba_tab_columns t
   where i.table_name = upper('&Table_name')
   and owner = upper(nvl('&Owner',user))
   and i.table_name = t.table_name
   and i.column_name = t.column_name
   order by index_name,column_position
   /prompt
   prompt ***************
   prompt Partition Level
   prompt ***************selectPARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_tab_partitions t
   where table_owner = upper(nvl('&&Owner',user))
   and table_name = upper('&&Table_name')
   order by partition_position
   /break on partition_name
   selectPARTITION_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS,NUM_NULLS,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_PART_COL_STATISTICS t
   where table_name = upper('&Table_name')
   and owner = upper(nvl('&Owner',user))
   /break on partition_name
   select t.INDEX_NAME,t.PARTITION_NAME,t.BLEVEL BLev,t.LEAF_BLOCKS,t.DISTINCT_KEYS,t.NUM_ROWS,t.AVG_LEAF_BLOCKS_PER_KEY,t.AVG_DATA_BLOCKS_PER_KEY,t.CLUSTERING_FACTOR,t.GLOBAL_STATS,t.USER_STATS,t.SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_ind_partitions t, dba_indexes i
   where i.table_name = upper('&Table_name')
   and i.table_owner = upper(nvl('&Owner',user))
   and i.owner = t.index_owner
   and i.index_name=t.index_name
   /prompt
   prompt ***************
   prompt SubPartition Level
   prompt ***************select PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,GLOBAL_STATS,USER_STATS,SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_tab_subpartitions t
   where table_owner = upper(nvl('&&Owner',user))
   and table_name = upper('&&Table_name')
   order by SUBPARTITION_POSITION
   /
   break on partition_name
   select p.PARTITION_NAME,t.SUBPARTITION_NAME,t.COLUMN_NAME,t.NUM_DISTINCT,t.DENSITY,t.NUM_BUCKETS,t.NUM_NULLS,t.GLOBAL_STATS,t.USER_STATS,t.SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_SUBPART_COL_STATISTICS t, dba_tab_subpartitions p
   where t.table_name = upper('&Table_name')
   and t.owner = upper(nvl('&Owner',user))
   and t.subpartition_name = p.subpartition_name
   and t.owner = p.table_owner
   and t.table_name=p.table_name
   /break on partition_name
   select t.INDEX_NAME,t.PARTITION_NAME,t.SUBPARTITION_NAME,t.BLEVEL BLev,t.LEAF_BLOCKS,t.DISTINCT_KEYS,t.NUM_ROWS,t.AVG_LEAF_BLOCKS_PER_KEY,t.AVG_DATA_BLOCKS_PER_KEY,t.CLUSTERING_FACTOR,t.GLOBAL_STATS,t.USER_STATS,t.SAMPLE_SIZE,to_char(t.last_analyzed,'MM-DD-YYYY')
   from dba_ind_subpartitions t, dba_indexes i
   where i.table_name = upper('&Table_name')
   and i.table_owner = upper(nvl('&Owner',user))
   and i.owner = t.index_owner
   and i.index_name=t.index_name
   /clear breaks
   set echo on
   

3 表的统计信息

1. 查看表的统计信息

   --表
   select * from dba_tables;
   --分区
   select * from dba_tab_partitions;
   --子分区
   select * from dba_tab_subpartitions;
   

2. 相关字段含义

   --1. NUM_ROWS：目标表记录数，计算Cardinality时使用，Cardinality大小会决定CBO计算执行计划的成本大小
   --2. BLOCKS：目标表的数据占用数据块的数量，会决定CBO计算全表扫描的成本
   --3. AVG_ROW_LEN：目标表平均行长，用目标表所有记录占用字节数/目标表总行数得到，被用于计算结果集占用内存大小
   --4. EMPTY_BLOCKS：表中空块数量，和CBO无关，因此无法使用dbms_stats收集，只能使用analyze收集
   --5. CHAIN_CNT：行迁移\链接数量，也和CBO无关
   select t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,t.EMPTY_BLOCKS,t.CHAIN_CNT,t.* from dba_tables t;
   

3. 数据占用字节数查看

   --结果中Len=9，即表示占9字节
   select dump('吴思含',16) from dual;
   

4. 当表执行truncate操作后，Oracle下一次的自动收集统计信息作业，就一定会对该表收集统计信息

5. 使用insert语句插表后，表的统计信息是不会更新的，因此导入大量数据后，如果不马上收集统计信息就使用该表，会导致Oracle走出错误的执行计划

6. analyze命令是ddl语句、dbms_stats包中的存储过程内部包含了commit语句，它们都会导致事务提交，因此如果应用对事务有强一致性要求，就不能在导入数据的事务中收集统计信息，如果遇到需要在同一个事务中先插入大量数据到某个表，然后马上使用该表的情况，可以考虑在使用该表的SQL中加入hint或sql profile来保证Oracle走出理想的执行计划

7. 查看统计信息的历史收集情况

   --1. 查看表的object_id
   select object_id from dba_objects where object_name='TF_MDM_AC_REL';
   --2. savtime：表登记时间，rowcnt：记录数，blkcnt：块数，avgrln：平均行长，analyzetime：该统计信息收集的时间
   select savtime,rowcnt,blkcnt,avgrln,analyzetime from sys.wri$_optstat_tab_history where obj#=79707;
   

4 索引的统计信息

4.1 索引统计信息简介

1. 查看索引的统计信息

   --索引
   select * from DBA_INDEXES;
   --分区索引的分区
   select * from DBA_IND_PARTITIONS;
   --局部分区索引的子分区
   select * from DBA_IND_SUBPARTITIONS;
   

2. 相关字段含义

   --1. BLEVEL：存储目标索引的层级，0表示索引只有1层，根节点和叶子块是同一个块，CBO使用
   --2. LEAF_BLOCKS：叶子块数，被CBO用于计算对目标索引做索引全扫描和索引范围扫描的成本
   --3. CLUSTERING_FACTOR：聚簇因子，CBO使用
   --4. DISTINCT_KEYS：索引键值的distinct值数，对于唯一索引，没有null值情况下，distinct_keys值就是表的记录数
   --5. AVG_LEAF_BLOCKS_PER_KEY：每个distinct值所占用叶子块数量平均值，对于唯一索引，该值一定为1
   --6. AVG_DATA_BLOCKS_PER_KEY：每个distinct值所对应表中数据行占用数据块数量的平均值
   --7. NUM_ROWS：索引总行数
   select t.BLEVEL,t.LEAF_BLOCKS,t.CLUSTERING_FACTOR,t.DISTINCT_KEYS,t.AVG_LEAF_BLOCKS_PER_KEY,t.AVG_DATA_BLOCKS_PER_KEY,t.NUM_ROWS,t.*
   from DBA_INDEXES t;
   

3. 使用rebuild降低B树索引层级

   --1. 分析索引，执行该语句后，在与其同一个session下查询index_stats表，就会出现分析后的数据
   analyze index TF_MDM_AC_REL_I5 validate structure;
   --2. name：索引名、height：索引高度，也就是BLEVEL+1，lf_rows：索引总行数，del_lf_rows：删除的索引行数，lf_blks：叶子块数
   --如果del_lf_rows接近lf_rows，说明大部分索引行都被删除了，此时如果索引高度还是很高，就应该考虑重建索引来降低索引高度
   select name, height, lf_rows, del_lf_rows,lf_blks from index_stats;
   --3. 在线重建索引
   alter index idx_t1 rebuild online;
   

4.2 聚簇因子

1. 索引行是有序的，聚簇因子指索引行的排序和表中数据行的存储顺序的相似程度

2. 聚簇因子计算方法：聚簇索引初始值为1，Oracle从索引最左边的叶子块的第一个索引行开始顺序扫描所有索引行，如果当前索引行与前一个索引行对应的表中数据行的rowid不在同一个数据块中，聚簇因子++，如果指向同一个表块，聚簇因子不变

3. 聚簇因子越大，走索引范围扫描时，就会耗费更多的物理IO，CBO计算的成本也越高

   1. 聚簇因子越小，越接近表的数据块数，说明索引行和表中数据行存储顺序相似度更高，这就意味着Oracle走索引范围扫描后取得表中数据行的rowid再回表访问对应表块的数据时，相邻索引行所对应的rowid极有可能处于同一表块中，即Oracle在通过索引行记录的rowid回表第一次读取对应的表块并将该表块缓存在buffer cache中后，当再通过相邻索引行记录的rowid回表，第二次读取对应的表块时，不需要再产生物理IO，因为这次要访问的和上次已经访问过的块是同一个块，Oracle已经将其缓存在buffer cache中

   2. 聚簇因子越大，越接近表中记录数，意味着索引范围扫描会产生大量物理IO

4. 我们只需要依次向表中插入数据1、3、5、7、9…，这样相邻的数据就不会存放在同一表块中，那么此时再建立索引，就能构造出下图这种极端的情况，图中聚簇因子为20
   

5. 查看t1表是否符合要求

   --id，文件号_数据块号，可以发现1、3、5、7、9的文件号和数据块号相同，11、13、15、17、19的文件号和数据块号相同
   select id, dbms_rowid.ROWID_RELATIVE_FNO(rowid) || '_' || dbms_rowid.ROWID_BLOCK_NUMBER(rowid) location
   from t1;
   

6. 聚簇因子过大，可能导致Oracle认为走全表扫描成本要小于索引范围扫描，从而走出错误的执行计划

7. Oracle中CBO计算索引范围扫描成本公式

   --1. 可以看出走索引范围扫描的成本可以看作是和聚簇因子成正比，因此聚簇因子的大小实际上对CBO判断是否走相关索引起着至关重要的作用
   --2. 如果Oracle认为走索引范围扫描的成本要高于走全表扫描，那么就出现了不走索引的情况
   --3. 注意，此处不要弄混，决定走索引范围扫描还是全表扫描，取决于两种方式的成本大小，而不是可选择率大小，可选择率会影响索引范围扫描成本，但同样会影响全表扫描的成本
   --IRS：Index Range Scan
   IRS Cost = I/O Cost+CPU Cost
   I/O Cost = Index Access I/O Cost+Table Access I/O Cost
   --IX_SEL：INDEX SELECTIVITY
   Index Access I/O Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL)
   Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)
   

8. 手工调整聚簇因子值

   --将聚簇因子调整为1000000
   exec dbms_stats.SET_INDEX_STATS(owname=>'C50HST',indname='IDX_T1',clstfct=>1000000,no_invalidate=>false);
   

5 列的统计信息

5.1 列的统计信息简介

1. 查看列的统计信息

   --表
   select * from dba_tab_col_statistics;
   --分区表的分区
   select * from dba_part_col_statistics;
   --分区表的子分区
   select * from dba_subpart_col_statistics;
   

2. 相关字段含义

   --1. NUM_DISTINCT：distinct值数，CBO用这个值来评估目标列做等值查询的可选择率
   --2. NUM_NULLS：null值数，CBO用该值评估目标列施加is null或is not null条件后，返回的结果集的Cardinality
   --3. LOW_VALUE：最小值
   --4. HIGH_VALUE：最大值
   --5. DENSITY：密度
   --6. NUM_BUCKETS：直方图所用桶数
   select t.NUM_DISTINCT,t.NUM_NULLS,t.LOW_VALUE,t.HIGH_VALUE,t.DENSITY,t.NUM_BUCKETS,t.* from dba_tab_col_statistics t;
   

3. 当列上没有直方图，对该列进行等值查询时，可选择率计算

   selectivity = (1/NUM_DISTINCT) * Null_Adjust
   Null_Adjust = ((NUM_ROWS-NUM_NULLS)/NUM_ROWS)
   

4. 当列上没有直方图，对该列进行范围询时，可选择率计算

   --列上无直方图，对col1列进行范围查询(col1>val)，且val处于low_value和high_value之间，使用如下公式
   --1. 可选择率=加条件后记录数/加条件前记录数
   --2. 由于没有直方图信息，所以Oracle认为目标列的数据在LOW_VALUE到HIGH_VALUE间均匀分布
   --3. 所以Oracle认为，加上col1>val条件后记录数/总记录数 = (high_value-val) / (high_value-low_value)
   --4. 又由于该列有空值存在，因此需要将结果乘一个空值调整(Null_Adjust)
   --5. 其他情况公式不进行记录，需要时可以在p451查询
   selectivity = ((high_value-val) / (high_value-low_value)) * Null_Adjust
   

5. Buffer Gets：逻辑读，如果一个执行计划当中，逻辑读非常大，但Cardinality非常小，要么是因为CBO成本计算公式存在缺陷，要么是因为统计信息不准确

6. 我们可以先自己考虑下这个语句实际上Cardinality的大小，例如如果一个语句查询一天的数据，那么可以想象，如果可选择率正确的情况下，Cardinality值应该为平均一天的数据量，如果Cardinality确实远小于该值，那么此时就可以开始着手研究可选择率为何不准确

7. 可以先还原当时执行的语句，通过下面方式获取当时执行计划的绑定变量值

   --获取历史绑定变量的值，snap_id从小到大
   select t.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 bind2,t.*
   from DBA_HIST_SQLSTAT t
   where t.SQL_ID = '2q93zsrvbdw48'
   order by t.SNAP_ID;
   

5.2 谓词越界

1. 谓词越界可能造成上面描述的逻辑读非常大，但Cardinality非常小的情况
2. 谓词越界：对目标列指定的where查询条件不在该列的最大值和最小值之间，此时CBO无法评估出针对该列的查询条件的可选择率，所以只能用一个估算值作为该目标列的查询条件的可选择率，如果这个估算的可选择率与实际情况严重不符，就会导致CBO评估的Cardinality出现严重偏差
3. 当语句出现谓词越界，对这个sql做10053事件后，trace文件中会出现Using prorated density:9.7933e-08 of col #30 as selectivity of out-of-range value pred字样，表示30号列谓词越界，Oracle使用非常小的值9.7933e-08来作为了可选择率
4. 谓词越界可以使用两种方案解决
   1. 方案一：重新对表收集统计信息，但该表每天40w，共1.6亿，如果用oracle自动统计信息收集作业收集统计信息，那么谓词越界现象以后可能还会出现
   2. 方案二：使用hint强制使用正确的执行计划

5.3 直方图

5.3.1 直方图含义

1. 生活中也有直方图(柱状图)的概念，生活中常用直方图描述数据分布，Oracle中引入直方图概念是为了正确评估分布不均匀的列的可选择率和Cardinality

2. 查看直方图信息

   select * from dba_tab_histograms;
   select * from dba_part_histograms;
   select * from dba_subpart_histograms;
   

3. 当某列上存在直方图，Oracle就认为该列上数据分布不均匀，就会使用该列上直方图信息来计算对该列施加查询条件后的可选择率和Cardinality，进而计算成本并选择相应的执行计划

4. 我们可以简单的理解为，Oracle对dba_tab_histograms中每一条数据，都叫做一个bucket

5. 目标列的直方图占用的bucket数，存放于DBA_TAB_COL_STATISTICS.NUM_BUCKETS中

6. Oracle直方图缺陷

   1. oracle收集文本类型字段的直方图时，只会将该文本值的前32个字节取出来，并转换成浮点数存放在endpoint_value中
   2. 如果不同文本的前32字节相同，oracle会认为他们的文本值完全相同，但实际上他们并不相同
   3. 这种先天缺陷会直接影响CBO对文本类型字段的可选择率及Cadinality的评估

5.3.2 直方图类型

5.3.2.1 Frequency直方图

1. 把目标列的每一个distinct值、以及该值数据量，记录在数据字典中，因此如果以计算模式收集Frequency直方图，那么bucket数等于目标列的distinct值数，如果以估算模式，可能会有部分distinct值丢失，bucket数会少于distinct值数

2. 对于Frequency直方图，dba_tab_histograms的endpoint_value列为distinct的值，endpoint_number表示到此distinct值为止，总共有多少条记录

3. Frequency直方图在oracle12c以前，bucket数不能超过254，也就是说Frequency直方图，只适用于目标列distinct值数量小于等于254的情况

4. 模拟对表TF_MDM_AC_REL中SIGN_BR_NO列收集直方图后，表dba_tab_histograms中记录

   SELECT T.SIGN_BR_NO,COUNT(*) AS CARDINALITY,SUM(COUNT(*)) OVER(ORDER BY T.SIGN_BR_NO RANGE UNBOUNDED PRECEDING)FROM TF_MDM_AC_REL TGROUP BY T.SIGN_BR_NO;
   

5. 收集直方图信息

   --1. for columns：表示对列收集统计信息
   --2. size auto：表示让Oracle自行决定到底是否对列SIGN_BR_NO收集直方图，以及使用哪种类型的直方图
   dbms_stats.gather_table_stats(ownname=>'C50HST',tabname=>'TF_MDM_AC_REL',method_opt=>'for columns size auto SIGN_BR_NO',cascade=>true);
   --3. 收集成功后，HISTOGRAM就应该由NONE变为FREQUENCY或HEIGHT BALANCED
   select t.HISTOGRAM,t.* from dba_tab_col_statistics t where T.table_name='TF_MDM_AC_REL' and t.column_name='SIGN_BR_NO';
   --4. 注意，oracle收集直方图信息前，会先查看sys.col_usage$表，看该列是否使用过，如果从未使用过就不会对其收集直方图，所以如果一个表刚建立，需要先人为使用一下该列
   select * from TF_MDM_AC_REL where SIGN_BR_NO='00017';
   --5. 查询对应列是否使用过，即查询sys.col_usage$表
   --99834
   select t.OBJECT_ID from dba_objects t where t.OBJECT_NAME='TF_MDM_AC_REL';
   --1
   select t.intcol# from sys.col$ t where t.obj#='99834' and t.name='SIGN_BR_NO';
   select * from sys.col_usage$ t where t.obj#='99834' and t.intcol#='1';
   

5.3.2.2 Height Balanced直方图

1. Oracle收集直方图时，如果发现distinct值数超过254，或人为指定收集的直方图bucket数小于distinct值数，那么Oracle会改为收集Height Balanced直方图

   --人为控制bucket数为5，小于distinct值数，此时就会收集Height Balanced直方图
   exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TF_MDM_AC_REL',method_opt=>'for columns size 5 SIGN_BR_NO',cascade=>true);
   

2. Height Balanced直方图收集过程

   1. Oracle先通过总记录数/想使用的bucket数得到每个bucket中描述的记录数(此处为8000)

   2. 表dba_tab_histograms中endpoint_number字段记录bucket号，从0到N

   3. 0号bucket的endpoint_value存放目标列的最小值

   4. 其他bucket的endpoint_value记录截止当前bucket中目标列的最大值，也就是执行如下语句得到的值

      --计算除0号桶外，其他桶对应的endpoint_value值
      --1. 8000为总记录数/桶数
      SELECT MAX(T.SIGN_BR_NO)FROM (SELECT T.SIGN_BR_NO FROM TF_MDM_AC_REL T ORDER BY T.SIGN_BR_NO)WHERE ROWNUM <= 8000 * 桶号
      

   5. 如果连续的bucket，endpoint_number不同，但endpoint_value相同，dba_tab_histograms中只保留最大的endpoint_number那条记录，这种导致表dba_tab_histograms中数据合并的endpoint_value，oracle称之为popular value，意思就是该列大部分都是该值，popular value所在记录的endpoint_number与它上一条的endpoint_number值差距越大， 说明这个popular value在目标表中占比也越大，对应的Cardinality也越大

5.3.3 直方图的收集

1. METHOD_OPT

   --[]中内容，表示任选其一，可写可不写，{}中内容表示必选其一
   --写法一
   for all [indexed | hidden] columns [size_clause]
   --写法二
   --1. for columns size_clause：表示对所有列默认采用该size_clause进行处理，如果某些列需要特殊处理，可以通过"列名+size_clause"重新进行指定，如果只写"列名"，表示使用默认的size_clause
   for columns [size_clause] column|attribute [size_clause] [column|attribute]..
   --2. size_clause
   size {integer | repeat | auto | skewonly}
   

2. 示例

   --1. size + integer：1表示删除该列上直方图信息，其他值为bucket数
   --2. size + auto：表示让oracle自动决定是否收集直方图、收集哪种类型的直方图
   --3. size + repeat：表示对已有直方图的列再次收集直方图
   dbms_stats.gather_table_stats(ownname=>'C50HST',tabname=>'TF_MDM_AC_REL',method_opt=>'for columns size 5 SIGN_BR_NO');
   --4. 对所有索引包含的列收集直方图
   dbms_stats.gather_table_stats(ownname=>'C50HST',tabname=>'TF_MDM_AC_REL',method_opt=>'for all indexed columns size auto');
   --5. sign_br_no列使用size5，mdm_code列使用size 10
   --如果写为for columns size 10 sign_br_no size 5 mdm_code，表示默认使用size 10，但对sign_br_no特殊处理，使用size 5，mdm_code使用默认的size 10
   dbms_stats.gather_table_stats(ownname=>'C50HST',tabname=>'TF_MDM_AC_REL',method_opt=>'for columns sign_br_no size 5 mdm_code size 10');
   --6. 只删除sign_br_no列上的直方图
   dbms_stats.gather_table_stats(ownname=>'C50HST',tabname=>'TF_MDM_AC_REL',method_opt=>'for columns sign_br_no size 1');
   

5.3.4 直方图注意事项

1. 直方图会对Shared Cursor造成影响

   1. 目标列上有直方图后，CBO会认为对该列施加的等值查询条件，是一个不安全的谓词条件，所谓不安全，指sql执行计划可能会随着具体的查询值的不同而不同
   2. 根据之前学习的内容，将参数cursor_sharing参数设置为similar后，Oracle会自动用系统产生的绑定变量替换SQL里where条件中针对目标列传入的具体值，以达到尽可能重用Shared Cursor中存储的解析树和执行计划，降低硬解析，但如果目标列上有直方图，Oracle会对目标列传入的每个distinct值产生一个Child Cursor这实际上还是硬解析，变相导致cursor_sharing设置失效

2. 直方图会对可选择率造成影响

   1. 列上无直方图的等值查询

      selectivity = dba_tab_col_statistics.DENSITY
      DENSITY = 1/NUM_DISTINCT
      

   2. 列上有Frequency直方图的等值查询

      1. 查询值存在于dba_tab_histograms

         selectivity = BucketSize / NUM_ROWS
         BucketSize = Current_ENDPOINT_NUMBER-Previous_ENDPOINT_NUMBER
         

      2. 查询值不在dba_tab_histograms中

         --1. 查询值不在dba_tab_histograms中，可能是因为收集直方图时，采样数(DBA_TAB_COL_STATISTICS.sample_size)很少导致
         --2. 数量少不影响不同distinct值之间比例，因此不影响Oracle对数据分布的判断
         --3. 查询的值不在dba_tab_histograms中，Oracle会认为是采样时错过了该值，那么说明该值一定非常少，所以可以认为其选择率是当前DBA_TAB_HISTOGRAMS中数量最少的那个值的选择率的一半，所以
         selectivity= min(bucketsize) / (2*NUM_ROWS)
         

   3. 列上有Height Balanced直方图的等值查询

      1. 情况太复杂，可以参考p486

3. 如果目标列的数据是均匀分布的，例如主键列、有唯一索引

4. 配置oracle 10g引入的自动统计信息收集作业时，需要特别注意对直方图统计信息的收集策略