02-SQL优化学习- 统计信息

第2章 统计信息

2.1、统计信息主要分类

表的统计信息

总行数（num_rows）、块数（blocks）、平均长度（avg_row_len），可通过DBA_TABLES获取；

列的统计信息

列的基数、列的空值数量、列的数据分配（直方图），可通过DBA_TAB_COL_STATISTICS获取；

select a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity,
       a.HISTOGRAM,
       a.num_buckets
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
   and a.table_name = 'TEST';

复制

索引的统计信息

索引blevel（索引高度-1）、叶子块的个数（leaf_blocks）以及集群因子（clustering_factor），可通过DBA_INDEXES获取；

单独对索引收集统计信息：
begin
 dbms_stats.gather_index_stats(ownername => 'SCOTT',
                               indname   => 'IDX_T_STATS_ID');
end;
复制

系统的统计信息

数据字典的统计信息

动态性能视图基表的统计信息

2.2、统计信息参数设置

begin
  dbms_stats.gather_table_stats(ownname          => 'TAB_OWNER',
                                tabname          => 'TAB_NAME',
                                estimate_percent => '根据表大小设置',
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => '根据表大小，CPU资源和负载设置',
                                granularity      => 'AUTO',
                                cascade          => TRUE);
end;
ownname表示表的拥有者，不区分大小写；
tabname表示表名字，不区分大小写；
granularity表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO；
estimate_percent表示采样率，范围是0.000001 ~ 100；
method_opt用于控制收集直方图策略；
 > method_opt => 'for all columns size 1'表示所有列都不收集直方图；
 > method_opt => 'for all columns size skewonly'表示对表中所有列收集自动判断是否收集直方图；
 > method_opt => 'for all columns size auto'表示对出现在where条件中的列自动判断是否收集直方图；
 > method_opt => 'for all columns size repeat'表示当前有哪些列收集了直方图，现在就对哪些列收集直方图；
 > method_opt => 'for columns col1 size skewonly'表示单独对列col1收集直方图，对于其他列，如果之前收集过直方图，现在也收集直方图；

no_invalidate:表示共享池中涉及到该表的游标是否立即失效，设置为FALSE代表立即失效；
degree表示收集统计信息的并行度，默认为NULL；
cascade表示收集表的统计信息的时候，是否级联收集索引的统计信息；
复制

2.3、检查统计信息是否过期

dba_tab_statistics:列stale_stats为YES，代表统计信息过期；

当表中有超过10%的数据发生变化（insert、update、delete），就会引起统计信息过期；

2.3.1、如果一个SQL有多个表关联或视图嵌套，如何快速检查SQL语句中所有的表统计信息是否过期？

select * from scott.emp e, scott.dept d where e.deptno = d.deptno;

生成执行plan_table:
explain plan for
  select * from scott.emp e, scott.dept d where e.deptno = d.deptno;

获取是否过期：
select owner, table_name, object_type, stale_stats, last_analyzed
  from dba_tab_statistics
 where (owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));
获取过期原因：
select *
  from all_tab_modifications
 where (table_owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));
复制

2.4、扩展统计信息

扩展统计信息只能用于等值查询，不能用于非等值查询；

create table scott.t as select level as id, level || 'a' as a, level || level || 'b' as b
  from dual
connect by level <= 1000;

insert into scott.t select * from scott.t ;

select count(*) from scott.t; 
--2048000

begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'T',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
end;

select a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity,
       a.HISTOGRAM,
       a.num_buckets
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
   and a.table_name = 'T';
   
create index idx1 on scott.t(a) ;
create index idx2 on scott.t(a,b) ;   

select dbms_stats.create_extended_stats(ownname => 'SCOTT',tabname => 'T',extension => '(A,B)') from dual ;
-- SYS_STUNA$6DVXJXTP05EH56DTIR0X


begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'T',
                                estimate_percent => 100,
                                method_opt       => 'for columns SYS_STUNA$6DVXJXTP05EH56DTIR0X size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
end;

select a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity,
       a.HISTOGRAM,
       a.num_buckets
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
   and a.table_name = 'T';
复制

2.5、动态采样

默认情况是level=2；

2.5.1、动态采样级别

当系统中有全局临时表，就需要动态采样，因为全局临时表无法收集统计信息，建议对全局临时表至少启用level 4进行采样；

当执行计划中标的Rows估算有严重偏差的时候，列入相关列问题，或者量表关联有多个连接列，关联之后Rows算少，或者where过滤条件中对列使用了substr、instr、like，又或者是where过滤条件中有非等值过滤，或者group by之后导致Rows估算错误，此时我们可以考虑动态采样，同样，建议动态采样至少设置为level 4；

level 0：不启用动态采样。

level 1：当表（非分区表）没有收集过统计信息并且这个表要与另外的表进行关联（不能是单表访问），同时该表没有索引，表的数据块必须大于32个，满足这些条件的时候，Oracle会随机扫描表中32个数据块，然后评估返回的Rows。

level 2：对没有收集过统计信息的表启用动态采样，采样的块数为64个，如果表的块数小于64个，表有多少个块就会采样多少个块。

level 3：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是优化器不能准确地估算出返回的Rows，而是靠猜，比如WHERE SUBSTR(owner,1,3)，这时会随机扫描64个数据块进行采样。

level 4：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是表有两个或者两个以上过滤条件（AND/OR），这时会随机扫描64个数据块进行采样，相关列问题就必须启用至少level 4进行动态采样。level4采样包含了level 3的采样数据。

level 5：收集满足level 4采样条件的数据，采样的块数为128个。

level 6：收集满足level 4采样条件的数据，采样的块数为256个。

level 7：收集满足level 4采样条件的数据，采样的块数为512个。

level 8：收集满足level 4采样条件的数据，采样的块数为1 024个。

level 9：收集满足level 4采样条件的数据，采样的块数为4 086个。

level 10：收集满足level 4采样条件的数据，采样表中所有的数据块。

level 11：Oracle自动判断如何采样，采样的块数由Oracle自动决定。

复制

SCOTT@o11g>create table t_dyna as select * from dba_objects;
SCOTT@o11g>alter session set optimizer_dynamic_sampling=3;
SCOTT@o11g>select /*+ dynamic_sampling(3) */ * from t_dyna where substr(owner,4,3)='LIC';

复制

2.6、定制统计信息收集策略

脚本：收集统计信息过期了或者表从没收集过统计信息的表的统计信息

declare
  cursor stale_table is
    select owner,
           segment_name,
           case
             when segment_size < 1 then
              100
             when segment_size >= 1 and segment_size <= 5 then
              50
             when segment_size > 5 then
              30
           end as percent,
           6 as degree
      from (select owner,
                   segment_name,
                   sum(bytes / 1024 / 1024 / 1024) segment_size
              from DBA_SEGMENTS
             where owner = 'SCOTT'
               and segment_name in
                   (select table_name
                      from DBA_TAB_STATISTICS
                     where (last_analyzed is null or stale_stats = 'YES')
                       and owner = 'SCOTT')
             group by owner, segment_name);
begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',
                                  degree           => stale.degree,
                                  cascade          => true);
  end loop;
end;
/
复制

获取全局临时表信息(需单独收集统计信息)：

select b.OBJECT_OWNER, b.OBJECT_NAME, a.temporary, sql_text
  from dba_tables a, v$sql_plan b, v$sql c
 where a.owner = b.object_owner
   and a.temporary = 'Y'
   and a.table_name = b.OBJECT_NAME
   and b.sql_id = c.sql_id;
复制