《SQL优化核心思想》---阅读笔记2

第二章 统计信息

备注：基于Oracle12C版本进行学习的
我们分析一个SQL语句的性能问题，需要在知道表的信息，怎么去了解这些信息呢？
这里重点讨论表的统计信息
表的统计信息主要包含表的总行数num_rows、 表的块数blocks、以及平均长度avg_row_len，我们可以通过数据字典DBA_TABLES获取表的统计信息。
1、通常我们查看表T_STATS的统计信息采用如下SQL:
select owner,table_name,num_rows,blocks,avg_row_len from dba_table where owner ='SCOTT' and table_name ='T_STATS';
如果是新建的表，那么可以先收集一波信息，使用如下函数：
在命令执行窗口，输入如下函数：
begin 
  DBMS_STATUS.GATHER_TABLE_STATUS(ownname            => 'SCOTT',
                                  tabname            => 'T_SATUS',
                                  estimate_percent   => 100,(根据表的大小设置，表示采样率，范围是0.000001-100)
                                  method_opt         => 'for all columns size auto',
                                  no_invalidate      => FALSE,
                                  degree             => 1,（根据表大小，CPU资源和负载设置）
                                  cascade            => TRUE);
end;
/
estimate_percent：采样率，我们一般建议小于1GB的数据100%采样，1GB-5GB 建议50%采样，更多的数据量进行采样时，建议不低于30%  
method_opt：用于控制收集直方图策略
①for all columns size 1 表示所有列都不收集直方图
②for all columns size skewonly 表示对表中所有列收集自动判断是否收集直方图
③for all columns size auto  表示对出现在where条件中的列自动判断是否收集直方图 
no_invalidate：表示共享池中涉及该表的游标是否立即实效，默认是DBMS_STATUS.AUTO_INVALIDATE表示让Oracle自己决定是否立即实效
degree：表示统计信息的并行度，默认是NULL
cascade:表示收集表的统计信息的时候，是否级联收集索引的统计信息，默认是DBMS_STATUS.AUTO_CASCADE，表示让Oracle自己判断

2、列的统计信息，主要包括列的基数、列中的空值数量以及列的数据分布情况，我们可以通过DBA_TAB_COL_STATISTICS
select colnumn_name,num_distinct,num_nulls,num_buckets,histogram from dba_tab_col_statistics where owner ='SCOTT' and table_name ='T_STATS';

3、索引的统计信息主要包含索引blevel(索引高度-1)、叶子快的个数(leaf_blocks)以及集群因子(clustering_factor),收集索引信息的sql如下：
select blevel,leaf_blocks,clustering_factor,status from dba_indexes where owner = 'SCOTT' and index_name =='IDX_T_STATUS_ID';
如果没有信息，也需要先统计一波：
begin 
  DBMS_STATUS.GATHER_INDEX_STATUS(owner  => 'SCOTT',
                                  indname =>'IDX_T_STATUS_ID');
end;
/

剩下检查索引信息是否过期、扩展统计信息、动态采样、定制统计信息收集策略等小节，深入到了DBA环节，自行深入了解。