当前位置：首页 > article >正文

SQL优化（二）统计信息

article 2024/9/18 5:49:15

收集统计信息

数据库的统计信息非常重要，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL就有可能走错执行计划，也就会出现性能问题。

统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息，我们主要关注表、列、索引的统计信息。

注：本文涉及到的数据库版本为Oracle Database 19c Enterprise Edition Release

表的统计信息

表的统计信息主要包含表的总行数（num_rows）、表的块数（blocks）以及行平均长度（avg_row_len），通过查询数据字典DBA_TABLES获取表的统计信息。

假设现在我们队FSBZDJ这张表进行统计，可以用以下语句查询统计信息：

 select owner, table_name, num_rows, blocks, avg_row_len
     from dba_tables
     where owner = 'WODEV' --表所属账户
     and table_name = 'FSBZDJ'; --表名

如果是新建的表，可能没有收集过统计信息，有可能查出来是空的，执行下面的存储过程进行收集。

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
            ownname             => 'WODEV',--表所属账户
            tabname             => 'FSBZDJ',--表名
            estimate_percent    => 100,--采样率，小表100%即可，超大表可以部分采样
            method_opt          => 'for all columns size auto',--控制直方图收集策略
            no_invalidate       => FALSE,
            degree              => 1,--根据表大小，CPU资源和负载设置
            cascade             => TRUE
);
END;

通过查询结果，可以看出该表总数据行数6916行，1016个数据块，平均行长度为777字节。

列的统计信息

列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况（直方图）。可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。

可以通过以下语句收集FSBZDJ的列信息：

 select column_name, num_distinct, num_nulls, num_buckets, histogram
    from dba_tab_col_statistics
    where owner = 'WODEV'
    and table_name = 'FSBZDJ';

由于列数过多，只展示部分列。

NUM_BUCKETS代表直方图的桶数，HISTOGRAM代表直方图类型。

可以用下列语句一起查出基数，选择性等指标。

select a.column_name,
           b.num_rows,
          a.num_nulls,
          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 = 'WODEV'
      and a.table_name = 'FSBZDJ';

索引的统计信息

集群因子

集群因子是索引的一个统计属性，用于判断索引回表需要消耗的物理I/O次数。

集群因子的计算跟ROWID有关。

可以用以下语句查询FSBZDJ这个表下IDX_FSBZDJ_DJBH索引的集群因子

 select owner, index_name, clustering_factor
     from dba_indexes
     where owner = 'WODEV'
     and index_name = 'IDX_FSBZDJ_DJBH';

集群因子的计算需要查出rowid，然后相邻行间作比较

select * from 
(select fsbzdj_djbh,rowid from fsbzdj order by fsbzdj_djbh)  
where rownum<10

首先我们比较1、2行对应的ROWID是否在同一个数据块，如果在同一个数据块，CLUSTERING_FACTOR+0；如果不在同一个数据块，那么CLUSTERING_FACTOR+1。

然后我们比较2、3对应的ROWID是否在同一个数据块，如果在同一个数据块，CLUSTERING_FACTOR+0；如果不在同一个数据块，那么CLUSTERING_FACTOR+1。

再比较3、4对应的ROWID是否在同一个数据块，如果在同一个数据块，CLUSTERING_FACTOR+0；如果不在同一个数据块，那么CLUSTERING_FACTOR+1。

一直这样有序地比较下去，直到比较完索引中最后一个键值，由此可知

集群因子的值介于表的块数和表行数之间。

计算sql如下：

 select sum(case when block#1 = block#2 and file#1 = file#2 then 0
              else 1 end) CLUSTERING_FACTOR
      from (select 
    dbms_rowid.rowid_relative_fno(rowid) file#1,
    lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by fsbzdj_djbh) file#2,
    dbms_rowid.rowid_block_number(rowid) block#1,
    lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by fsbzdj_djbh) block#2
    
    from fsbzdj
    where fsbzdj_djbh is not null
    );

计算结果与统计的结果接近，不是完全相等因为表的统计信息没有实时更新。

如果集群因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成。

如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。

集群因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。

FSBZDJ这个表块数是1016个块，表行数为6916，集群因子为5184，更接近表的行数，说明表的数据和索引顺序差异很大，如果SQL执行中发生回表，将会有严重的性能影响。

索引的统计信息

索引的统计信息主要包含索引blevel（索引高度-1）、叶子块的个数（leaf_blocks）以及集群因子（clustering_factor）。可以通过数据字典DBA_INDEXES查看索引的统计信息。

创建索引的时候会自动收集索引的统计信息，可以用以下SQL收集统计信息。

--查询某个索引
select  blevel, leaf_blocks, clustering_factor,status
     from dba_indexes
     where owner = 'WODEV'
     and index_name = 'IDX_FSBZDJ_DJBH';


--单独收集某个索引的统计信息
BEGIN
      DBMS_STATS.GATHER_INDEX_STATS(ownname => 'WODEV',
                                    indname => 'IDX_FSBZDJ_DJBH');
END;

统计用的数据库函数

DBMS_STATS.GATHER_TABLE_STATS

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代表表名。

estimate_percent代表采样率，范围是0.000001～100。

一般对小于1GB的表进行100%采样，因为表很小，即使100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。

对表大小在1GB～5GB的表采样50%，对大于5GB的表采样30%。如果表特别大，有几十甚至上百GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。

采样率不要低于30%。查看某个表采样率用以下SQL:

SELECT owner,
       table_name,
       num_rows,
       sample_size,
       round(sample_size / num_rows * 100) estimate_percent --采样率
     FROM DBA_TAB_STATISTICS
    WHERE owner='WODEV' AND table_name='FSBZDJ';

method_opt用于控制收集直方图策略。

='for all columns size 1' 表示所有列都不收集直方图。

='for all columns size skewonly' 表示对表中所有列收集自动判断是否收集直方图，实际很少使用。

='for all columns size auto'(参数默认值) 表示对出现在where条件中的列自动判断是否收集直方图。

='for all columns size repeat' 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。

='for columns 列名 size skewonly' 表示单独对该列收集直方图，其余列之前如果收集过直方图，本次也继续收集直方图。

no_invalidate表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS.AUTO_INVALIDATE，表示让Oracle自己决定是否立即失效。建议将no_invalidate参数设置为false，立即失效。

degree表示收集统计信息的并行度，默认为NULL。如果表没有设置degree，收集统计信息的时候后就不开并行；如果表设置了degree，收集统计信息的时候就按照表的degree来开并行。可以查询DBA_TABLES.degree来查看表的degree，一般情况下，表的degree都为1。

granularity表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。数据库默认采用AUTO方式。

cascade表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息。一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。

查看表的统计信息是否过期

如果表中有大量数据发生变化，这时表的统计信息就过期了，需要重新收集表的统计信息，如果不重新收集，可能会导致执行计划走错。Oracle数据库中，表中只要有超过10%的数据发生变化，统计信息就会过期。

查询统计信息是否过期：

 begin
    dbms_stats.flush_database_monitoring_info;--刷新数据库监控信息
 end;

 select owner, table_name , object_type, stale_stats, last_analyzed
     from dba_tab_statistics
     where owner = 'WODEV'
     and table_name = 'FSBZDJ';