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

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

技术干货，第一时间送达

统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，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.

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

复制代码

从查询中我们可以看到，表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.

我们查看直方图信息。

复制代码

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

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条件中的列自动判断是否收集直方图。

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

复制代码

我们执行下面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'方式对表收集统计信息。

复制代码

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

复制代码

从上面查询我们可以看到，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.

我们收集统计信息。

复制代码

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

复制代码

从上面查询我们可以看到，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进行编写，内容讲解由浅入深，适合各个层次的读者学习。

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

小福利