统计信息

时间：2024-04-21 07:10:07

统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL的执行计划就会跑偏，SQL也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。

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

1.1、表的统计信息

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

创建一个测试表T_STATS：

create table t_stats as select * from DBA_OBJECTS;

我们查看表T_STATS常用的表的统计信息：

select owner, table_name, num_rows, blocks, avg_row_len from dba_tables where owner = 'SCOTT' and table_name = 'T_STATS';

在这里插入图片描述
因为T_STATS是新创建的表，没有收集过统计信息，所以从DBA_TABLES查询数据是空的。

现在我们来收集表T_STATS的统计信息。

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                  tabname => 'T_STATS',
                                  estimate_percent => 100,
                                  method_opt => 'for all columns size auto',
                                  no_invalidate => FALSE,
                                  degree => 1,
                                  cascade => TRUE);
END;

这段SQL代码是一个PL/SQL块，它调用了DBMS_STATS包中的GATHER_TABLE_STATS过程，用于收集指定表的统计信息。下面是对这段代码的详细分析：

  1. BEGIN ... END;：这是一个匿名PL/SQL块，用于执行一组SQL语句。在这个块中，你可以执行多个命令，它们作为一个整体事务来处理。

  2. DBMS_STATS.GATHER_TABLE_STATS：这是Oracle提供的一个过程，用于收集表的统计信息。统计信息对于优化器来说非常重要，因为优化器依赖这些信息来选择最佳的执行计划。

  3. ownname => 'SCOTT'：这个参数指定了要收集统计信息的表的所有者（schema）名称。在这个例子中，所有者名称是SCOTT。

  4. tabname => 'T_STATS'：这个参数指定了要收集统计信息的表的名称。在这个例子中，表的名称是T_STATS。

  5. estimate_percent => 100：这个参数指定了统计信息的收集方式。100表示使用全表扫描来收集统计信息，这将提供最准确的统计数据，但可能需要更多的时间。如果设置为DBMS_STATS.AUTO_SAMPLE_SIZE，优化器将自动决定采样的行数。

  6. method_opt => 'for all columns size auto'：这个参数指定了收集统计信息的方法。for all columns表示为表中的所有列收集统计信息，size auto表示让优化器自动决定每个列的统计信息收集的粒度。

  7. no_invalidate => FALSE：这个参数指定在收集统计信息后是否使表的统计信息保持有效。FALSE表示收集统计信息后，如果表的数据发生了变化，将使统计信息无效，以便在下次查询时重新收集。

  8. degree => 1：这个参数指定了用于收集统计信息的并行度。1表示使用单线程（非并行）来收集统计信息。如果你的系统有多核处理器，增加这个值可能会加快统计信息收集的速度。

  9. cascade => TRUE：这个参数指定是否递归地收集依赖于该表的所有对象（如索引、视图、序列等）的统计信息。

总的来说，这段SQL代码的作用是为SCOTT用户下的T_STATS表收集详尽的统计信息，以便优化器可以更准确地评估查询成本并选择最佳的执行计划。通过设置estimate_percent为100，确保了统计信息的准确性，同时通过cascade参数，确保了所有相关对象的统计信息也是最新的。

我们再次查看表的统计信息：

select owner, table_name, num_rows, blocks, avg_row_len from dba_tables where owner = 'SCOTT' and table_name = 'T_STATS';

在这里插入图片描述
从查询中我们可以看到，表T_STATS一共有76508行数据，1115个数据块，平均行长度为98字节。

1.2、列的统计信息

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

现在我们查看表T_STATS常用的列统计信息:

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

在这里插入图片描述
上面查询中，第一个列表示列名字，第二个列表示列的基数，第三个列表示列中NULL值的数量，第四个列表示直方图的桶数，最后一个列表示直方图类型。

在工作中，我们经常使用下面脚本查看表和列的统计信息：

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 = 'SCOTT'
  and a.table_name = 'T_STATS';

在这里插入图片描述

1.3、索引的统计信息

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

我们在OBJECT_ID列上创建一个索引：

create index idx_t_stats_id on t_stats(object_id);

创建索引的时候会自动收集索引的统计信息，运行下面脚本查看索引的统计信息：

select BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,STATUS from DBA_INDEXES where OWNER='SCOTT' and INDEX_NAME='IDX_T_STATS_ID';

在这里插入图片描述
如果要单独对索引收集统计信息，可以使用下面脚本收集：

BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
                                  indname => 'IDX_T_STATS_ID');
END;

相关文章