SQL调优技巧：统计信息（文末福利）

点击上方“异步社区”，选择“置顶公众号”

技术干货，第一时间送达

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

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

关于系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息本文不做讨论，本文重点讨论表的统计信息、列的统计信息以及索引的统计信息。

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

现在我们创建一个测试表T_STATS。

1SQL> create table t_stats as select * from dba_objects;
2Table created.

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

1SQL> select owner, table_name, num_rows, blocks, avg_row_len
2  2    from dba_tables
3  3   where owner = 'SCOTT'
4  4     and table_name = 'T_STATS';
5OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
6--------------- --------------- ---------- ---------- -----------
7SCOTT           T_STATS

因为T_STATS是新创建的表，没有收集过统计信息，所以从DBA_TABLES查询数据是空的。

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

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size auto',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

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

1SQL> select owner, table_name, num_rows, blocks, avg_row_len
2  2    from dba_tables
3  3   where owner = 'SCOTT'
4  4     and table_name = 'T_STATS';
5OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
6--------------- --------------- ---------- ---------- -----------
7SCOTT           T_STATS              72674       1061          97

从查询中我们可以看到，表T_STATS一共有72?674行数据，1?061个数据块，平均行长度为97字节。

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

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

1SQL> select column_name, num_distinct, num_nulls, num_buckets, histogram
2  2    from dba_tab_col_statistics
3  3   where owner = 'SCOTT'
4  4     and table_name = 'T_STATS';
5COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM
6--------------- ------------ ---------- ----------- --------------------
7EDITION_NAME               0      72674           0 NONE
8NAMESPACE                 21          1           1 NONE
9SECONDARY                  2          0           1 NONE
10GENERATED                  2          0           1 NONE
11TEMPORARY                  2          0           1 NONE
12STATUS                     2          0           1 NONE
13TIMESTAMP               1592          1           1 NONE
14LAST_DDL_TIME           1521          1           1 NONE
15CREATED                 1472          0           1 NONE
16OBJECT_TYPE               45          0           1 NONE
17DATA_OBJECT_ID          7796      64833           1 NONE
18OBJECT_ID              72673          1           1 NONE
19SUBOBJECT_NAME           140      72145           1 NONE
20OBJECT_NAME            44333          0           1 NONE
21OWNER                     31          0           1 NONE
2215 rows selected.

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

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

1SQL> select a.column_name,
2  2         b.num_rows,
3  3         a.num_nulls,
4  4         a.num_distinct Cardinality,
5  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6  6         a.histogram,
7  7         a.num_buckets
8  8    from dba_tab_col_statistics a, dba_tables b
9  9   where a.owner = b.owner
10 10     and a.table_name = b.table_name
11 11     and a.owner = 'SCOTT'
12 12     and a.table_name = 'T_STATS';
13COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
14--------------- ---------- ---------- ----------- ----------- --------------- -------
15EDITION_NAME         72674      72674           0           0 NONE                  0
16NAMESPACE            72674          1          21         .03 NONE                  1
17SECONDARY            72674          0           2           0 NONE                  1
18GENERATED            72674          0           2           0 NONE                  1
19TEMPORARY            72674          0           2           0 NONE                  1
20STATUS               72674          0           2           0 NONE                  1
21TIMESTAMP            72674          1        1592        2.19 NONE                  1
22LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
23CREATED              72674          0        1472        2.03 NONE                  1
24OBJECT_TYPE          72674          0          45         .06 NONE                  1
25DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
26OBJECT_ID            72674          1       72673         100 NONE                  1
27SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
28OBJECT_NAME          72674          0       44333          61 NONE                  1
29OWNER                72674          0          31         .04 NONE                  1
3015 rows selected.

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

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

1SQL> create index idx_t_stats_id on t_stats(object_id);
2Index created.

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

1SQL> select blevel, leaf_blocks, clustering_factor,status
2  2    from dba_indexes
3  3   where owner = 'SCOTT'
4  4     and index_name = 'IDX_T_STATS_ID';
5    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
6---------- ----------- ----------------- ----------------
7         1         161              1127 VALID

如果要单独对索引收集统计信息，可以使用下面脚本收集。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
3  3                                  indname => 'IDX_T_STATS_ID');
4  4  END;
5  5  /
6PL/SQL procedure successfully completed.

在以后章节中，我们会详细介绍表的统计信息、列的统计信息以及索引的统计信息是如何被应用于成本计算的。

我们通常使用下面脚本收集表和索引的统计信息。

1BEGIN
2  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TAB_OWNER',
3                                tabname          => 'TAB_NAME',
4                                estimate_percent => 根据表大小设置,
5                                method_opt       => 'for all columns size repeat',
6                                no_invalidate    => FALSE,
7                                degree           => 根据表大小，CPU资源和负载设置,
8                                granularity      => 'AUTO',
9                                cascade          => TRUE);
10END;
11/

ownname表示表的拥有者，不区分大小写。

tabname表示表名字，不区分大小写。

granularity表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用AUTO方式，也就是数据库默认方式，因此，在后面的脚本中，省略该选项。

estimate_percent 表示采样率，范围是0.000?001～100。

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

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

一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于30%。

我们可以使用下面脚本查看表的采样率。

1SQL> SELECT owner,
2  2         table_name,
3  3         num_rows,
4  4         sample_size,
5  5         round(sample_size / num_rows * 100) estimate_percent
6  6    FROM DBA_TAB_STATISTICS
7  7   WHERE owner='SCOTT' AND table_name='T_STATS';
8OWNER           TABLE_NAME        NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
9--------------- --------------- ---------- ----------- ----------------
10SCOTT           T_STATS              72674       72674              100

从上面查询我们可以看到，对表T_STATS是100%采样的。现在我们将采样率设置为30%。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 30,
5  5                                  method_opt       => 'for all columns size auto',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.
12SQL> SELECT owner,
13  2         table_name,
14  3         num_rows,
15  4         sample_size,
16  5         round(sample_size / num_rows * 100) estimate_percent
17  6    FROM DBA_TAB_STATISTICS
18  7   WHERE owner='SCOTT' AND table_name='T_STATS';
19OWNER           TABLE_NAME        NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
20--------------- --------------- ---------- ----------- ----------------
21SCOTT           T_STATS              73067       21920               30

从上面查询我们可以看到采样率为30%，表的总行数被估算为73?067，而实际上表的总行数为72?674。设置采样率30%的时候，一共分析了21?920条数据，表的总行数等于round(21?920*100/30)，也就是73?067。

除非一个表是小表，否则没有必要对一个表100%采样。因为表一直都会进行DML操作，表中的数据始终是变化的。

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

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

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size 1',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

我们查看直方图信息。

1SQL> select a.column_name,
2  2         b.num_rows,
3  3         a.num_nulls,
4  4         a.num_distinct Cardinality,
5  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6  6         a.histogram,
7  7         a.num_buckets
8  8    from dba_tab_col_statistics a, dba_tables b
9  9   where a.owner = b.owner
10 10     and a.table_name = b.table_name
11 11     and a.owner = 'SCOTT'
12 12     and a.table_name = 'T_STATS';
13COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
14--------------- ---------- ---------- ----------- ----------- --------------- -------
15EDITION_NAME         72674      72674           0           0 NONE                  0
16NAMESPACE            72674          1          21         .03 NONE                  1
17SECONDARY            72674          0           2           0 NONE                  1
18GENERATED            72674          0           2           0 NONE                  1
19TEMPORARY            72674          0           2           0 NONE                  1
20STATUS               72674          0           2           0 NONE                  1
21TIMESTAMP            72674          1        1592        2.19 NONE                  1
22LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
23CREATED              72674          0        1472        2.03 NONE                  1
24OBJECT_TYPE          72674          0          45         .06 NONE                  1
25DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
26OBJECT_ID            72674          1       72673         100 NONE                  1
27SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
28OBJECT_NAME          72674          0       44333          61 NONE                  1
29OWNER                72674          0          31         .04 NONE                  1
3015 rows selected.

从上面查询我们看到，所有列都没有收集直方图。

method_opt => 'for all columns size skewonly'表示对表中所有列收集自动判断是否收集直方图，如下所示。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size skewonly',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

我们查看直方图信息，如下所示。

1SQL> select a.column_name,
2  2         b.num_rows,
3  3         a.num_nulls,
4  4         a.num_distinct Cardinality,
5  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6  6         a.histogram,
7  7         a.num_buckets
8  8    from dba_tab_col_statistics a, dba_tables b
9  9   where a.owner = b.owner
10 10     and a.table_name = b.table_name
11 11     and a.owner = 'SCOTT'
12 12     and a.table_name = 'T_STATS';
13COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
14--------------- ---------- ---------- ----------- ----------- --------------- -------
15EDITION_NAME         72674      72674           0           0 NONE                  0
16NAMESPACE            72674          1          21         .03 FREQUENCY            21
17SECONDARY            72674          0           2           0 FREQUENCY             2
18GENERATED            72674          0           2           0 FREQUENCY             2
19TEMPORARY            72674          0           2           0 FREQUENCY             2
20STATUS               72674          0           2           0 FREQUENCY             2
21TIMESTAMP            72674          1        1592        2.19 HEIGHT BALANCED     254
22LAST_DDL_TIME        72674          1        1521        2.09 HEIGHT BALANCED     254
23CREATED              72674          0        1472        2.03 HEIGHT BALANCED     254
24OBJECT_TYPE          72674          0          45         .06 FREQUENCY            45
25DATA_OBJECT_ID       72674      64833        7796       10.73 HEIGHT BALANCED     254
26OBJECT_ID            72674          1       72673         100 NONE                  1
27SUBOBJECT_NAME       72674      72145         140         .19 FREQUENCY           140
28OBJECT_NAME          72674          0       44333          61 HEIGHT BALANCED     254
29OWNER                72674          0          31         .04 FREQUENCY            31
3015 rows selected.

从上面查询我们可以看到，除了OBJECT_ID列和EDITION_NAME列，其余所有列都收集了直方图。因为EDITION_NAME列全是NULL，所以没必要收集直方图。OBJECT_ID列选择性为100%，没必要收集直方图。

在实际工作中千万不要使用method_opt => 'for all columns size skewonly' 收集直方图信息，因为并不是表中所有的列都会出现在where条件中，对没有出现在where条件中的列收集直方图没有意义。

method_opt => 'for all columns size auto'表示对出现在where条件中的列自动判断是否收集直方图。

现在我们删除表中所有列的直方图。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size 1',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

我们执行下面SQL，以便将owner列放入where条件中。

1SQL> select count(*) from t_stats where owner='SYS';
2  COUNT(*)
3----------
4     30850

接下来我们刷新数据库监控信息。

1SQL> begin
2  2    dbms_stats.flush_database_monitoring_info;
3  3  end;
4  4  /
5PL/SQL procedure successfully completed.

我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size auto',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

然后我们查看直方图信息。

1SQL> select a.column_name,
2  2         b.num_rows,
3  3         a.num_nulls,
4  4         a.num_distinct Cardinality,
5  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6  6         a.histogram,
7  7         a.num_buckets
8  8    from dba_tab_col_statistics a, dba_tables b
9  9   where a.owner = b.owner
10 10     and a.table_name = b.table_name
11 11     and a.owner = 'SCOTT'
12 12     and a.table_name = 'T_STATS';
13COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
14--------------- ---------- ---------- ----------- ----------- --------------- -------
15EDITION_NAME         72674      72674           0           0 NONE                  0
16NAMESPACE            72674          1          21         .03 NONE                  1
17SECONDARY            72674          0           2           0 NONE                  1
18GENERATED            72674          0           2           0 NONE                  1
19TEMPORARY            72674          0           2           0 NONE                  1
20STATUS               72674          0           2           0 NONE                  1
21TIMESTAMP            72674          1        1592        2.19 NONE                  1
22LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
23CREATED              72674          0        1472        2.03 NONE                  1
24OBJECT_TYPE          72674          0          45         .06 NONE                  1
25DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
26OBJECT_ID            72674          1       72673         100 NONE                  1
27SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
28OBJECT_NAME          72674          0       44333          61 NONE                  1
29OWNER                72674          0          31         .04 FREQUENCY            31
3015 rows selected.

从上面查询我们可以看到，Oracle自动地对owner列收集了直方图。

思考，如果将选择性比较高的列放入where条件中，会不会自动收集直方图？现在我们将OBJECT_NAME列放入where条件中。

1SQL> select count(*) from t_stats where object_name='EMP';
2  COUNT(*)
3----------
4         3

然后我们刷新数据库监控信息。

1SQL> begin
2  2    dbms_stats.flush_database_monitoring_info;
3  3  end;
4  4  /
5PL/SQL procedure successfully completed.

我们收集统计信息。

1SQL> BEGIN
2  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
3  3                                  tabname          => 'T_STATS',
4  4                                  estimate_percent => 100,
5  5                                  method_opt       => 'for all columns size auto',
6  6                                  no_invalidate    => FALSE,
7  7                                  degree           => 1,
8  8                                  cascade          => TRUE);
9  9  END;
10 10  /
11PL/SQL procedure successfully completed.

我们查看OBJECT_NAME列是否收集了直方图。

1SQL> select a.column_name,
2  2         b.num_rows,
3  3         a.num_nulls,
4  4         a.num_distinct Cardinality,
5  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6  6         a.histogram,
7  7         a.num_buckets
8  8    from dba_tab_col_statistics a, dba_tables b
9  9   where a.owner = b.owner
10 10     and a.table_name = b.table_name
11 11     and a.owner = 'SCOTT'
12 12     and a.table_name = 'T_STATS';
13COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
14--------------- ---------- ---------- ----------- ----------- --------------- -------
15EDITION_NAME         72674      72674           0           0 NONE                  0
16NAMESPACE            72674          1          21         .03 NONE                  1
17SECONDARY            72674          0           2           0 NONE                  1
18GENERATED            72674          0           2           0 NONE                  1
19TEMPORARY            72674          0           2           0 NONE                  1
20STATUS               72674          0           2           0 NONE                  1
21TIMESTAMP            72674          1        1592        2.19 NONE                  1
22LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
23CREATED              72674          0        1472        2.03 NONE                  1
24OBJECT_TYPE          72674          0          45         .06 NONE                  1
25DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
26OBJECT_ID            72674          1       72673         100 NONE                  1
27SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
28OBJECT_NAME          72674          0       44333          61 NONE                  1
29OWNER                72674          0          31         .04 FREQUENCY            31
3015 rows selected.

从上面查询我们可以看到，OBJECT_NAME列没有收集直方图。由此可见，使用AUTO方式收集直方图很智能。mothod_opt默认的参数就是 for all columns size auto。

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

本文摘自《SQL优化核心思想》

​《SQL优化核心思想》

罗炳森 黄超 钟侥 著

点击封面购买纸书

​结构化查询语言（Structured Query Language，SQL）是一种功能强大的数据库语言。它基于关系代数运算，功能丰富、语言简洁、使用方便灵活，已成为关系数据库的标准语言。 本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。

本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员，无论是初学者还是有一定基础的读者，都将从中获益。

小福利

关注【异步社区】服务号，转发本文至朋友圈或 50 人以上微信群，截图发送至异步社区服务号后台，并在文章底下留言你学习SQL语言经验，或者试读本书感受，我们将选出3名读者赠送《SQL优化核心思想》1本，赶快积极参与吧！
活动截止时间：2018年 5月10 日

​

​在“异步社区”后台回复“关注”，即可免费获得2000门在线视频课程；推荐朋友关注根据提示获取赠书链接，免费得异步图书一本。赶紧来参加哦！

​扫一扫上方二维码，回复“关注”参与活动！

阅读原文，购买《SQL优化核心思想》

阅读原文​