详情
本书共10章内容。第一章介绍SQL优化的基础知识；第二章讲解统计信息相关内容；第三章讲解执行计划，快速找出SQL性能瓶颈；第四章讲解常见的访问路径；第五章讲解表的各种连接方式；第六章介绍单表访问以及索引扫描的成本计算，引出优化思想。第七章讲解查询变换；第八章讲解优化技巧；第九章分享经典案例；第十章介绍全自动SQL审核。
书名：SQL优化核心思想
ISBN：978-7-115-47849-8
本书由人民邮电出版社发行数字版。版权所有，侵权必究。
您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。
我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。
如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。
著 罗炳森 黄 超 钟 侥
责任编辑 胡俊英
人民邮电出版社出版发行 北京市丰台区成寿寺路11号
邮编 100164 电子邮件 315@ptpress.com.cn
读者服务热线：(010)81055410
反盗版热线：(76010)81055315
结构化查询语言（Structured Query Language，SQL）是一种功能强大的数据库语言。它基于关系代数运算，功能丰富、语言简洁、使用方便灵活，已成为关系数据库的标准语言。
本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。本书共分10章，从SQL基础知识、统计信息、执行计划、访问路径、表连接方式、成本计算、查询变换、调优技巧、经典案例、全自动SQL审核等角度介绍了有关SQL优化的方方面面。
本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员，无论是初学者还是有一定基础的读者，都将从中获益。
近年来，随着系统的数据量逐年增加，并发量也成倍增长，SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。面对日益增多的SQL性能问题，如何下手以及如何提前审核已经成为越来越多的IT从业者必须要考虑的问题。
现在将8年专职SQL优化的经验和心得与大家一起分享，以揭开SQL优化的神秘面纱，让一线工程师在实际开发中不再寝食难安、谈虎色变，最终能够对SQL优化技能驾轻就熟。
编写本书也是对多年学习积累的一个总结，鞭策自己再接再厉。如果能够给各位读者在SQL优化上提供一点帮助，也不枉个中辛苦。
2014年，作者罗炳森与有教无类（网名）联合编写了《Oracle查询优化改写技巧与案例》一书，该书主要侧重于SQL优化改写技巧。到目前为止，该书仍然是市面上唯一一本专门讲解SQL改写技巧的图书。
因为《Oracle查询优化改写技巧与案例》只专注于SQL改写技巧，并没有涉及SQL优化的具体思想、方法和步骤，本书可以看作是对《Oracle查询优化改写技巧与案例》一书的进一步补充。
本书共10章，各章的主要内容如下。
第1章详细介绍了SQL优化的基础知识以及初学者切实需要掌握的基本内容，本章可以帮助初学者快速入门。
第2章详细讲解统计信息定义、统计信息的重要性、统计信息相关参数设置方案以及统计信息收集策略。
第3章详细讲解执行计划、各种执行计划的使用场景以及执行计划的阅读方法，通过定制执行计划，读者可以快速找出SQL性能瓶颈。
第4章详细讲解常见的访问路径，这是阅读执行计划中比较重要的环节，需要掌握各种常见的访问路径。
第5章详细讲解表的各种连接方式、各种表连接方式的等价改写以及相互转换，这也是本书的核心章节。
第6章介绍单表访问以及索引扫描的成本计算方法，并由此引出SQL优化的核心思想。
第7章讲解常见的查询变换，分别是子查询非嵌套、视图合并和谓词推入。如果要对复杂的SQL（包含各种子查询的SQL）进行优化，读者就必须掌握查询变换技巧。
第8章讲解各种优化技巧，其中涵盖分页语句优化思想、分析函数减少表扫描次数、超大表与超大表关联优化方法、dblink优化思路，以及大表的rowid切片优化技巧。掌握这些调优技巧往往能够事半功倍。
第9章分享在SQL优化实战中遇到的经典案例，读者可以在欣赏SQL优化案例的同时学习罗老师多年专职SQL优化的经验，同时学到很多具有实战意义的优化思想以及优化方法与技巧。
第10章讲解全自动SQL审核，将有性能问题的SQL扼杀在“摇篮”里，确保系统上线之后，不会因为SQL写法导致性能问题，同时还能抓出不符合SQL编码规范但是已经上线的SQL。
本书对系统面临性能压力挑战的一线工程师、运维工程师、数据库管理员（DBA）、系统设计与开发人员，具有极大的参考价值。
为了满足不同层次的读者需求，本书在写作的内容上尽量由浅入深，前5章比较浅显易懂，适合SQL优化初学者阅读。通读完前5章之后，初学者能够对SQL优化有一定认识。后5章属于进阶和高级内容，适合有一定基础的人阅读。通读完后5章的内容之后，无论是初学者或是有一定基础的读者都能从中获益良多。
本书专注于SQL优化技巧，因此书中不会涉及太多数据库系统优化的内容。
虽然本书是基于Oracle编写的，但是关系型数据库的优化方法都殊途同归，因此无论是DB2从业者、SQL SERVER从业者、MYSQL从业者，亦或是PostGre SQL从业者等，都能从本书中学到所需要的SQL优化知识。
因水平有限，本书在编写过程中难免有错漏之处，恳请读者批评、指正。联系我们的方式如下：692162374@qq.com（QQ好友数已达上限）或者327165427@qq.com（新开QQ账号）。
如果有读者想进一步学习SQL优化技能或者一些公司或机构需要开展SQL优化方面的培训，都可以联系作者。另外，作者还开设了实体培训班，可以实现零基础学习，结业后可以顺利就业，欢迎联系罗老师。
在阅读本书之前请读者安装好Oracle数据库并且配置好示例账户Scott，因为本书均以Scott账户进行讲解。推荐读者安装与本书相同版本的数据库进行测试，具有专研精神的读者请安装好Oracle12c进行对比实验，这样一来，你将发现Oracle12c CBO的一些新特征。本书使用的版本是Oracle11gR2。
SQL> select * from v$version where rownum=1;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> show user
USER is "SYS"
SQL> grant dba to scott;
Grant succeeded.
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott identified by tiger;
User altered.
SQL> conn scott/tiger
Connected.
SQL> create table test as select * from dba_objects;
Table created.某个列唯一键（Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。基数的高低影响列的数据分布。
以测试表test为例，owner列和object_id列的基数分别如下所示。
SQL> select count(distinct owner),count(distinct object_id),count(*) from test;
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) COUNT(*)
-------------------- ------------------------ ----------
29 72462 72462TEST表的总行数为72 462，owner列的基数为29，说明owner列里面有大量重复值，object_id列的基数等于总行数，说明object_id列没有重复值，相当于主键。owner列的数据分布如下。
SQL> select owner,count(*) from test group by owner order by 2 desc;
OWNER COUNT(*)
-------------------- ----------
SYS 30808
PUBLIC 27699
SYSMAN 3491
ORDSYS 2532
APEX_030200 2406
MDSYS 1509
XDB 844
OLAPSYS 719
SYSTEM 529
CTXSYS 366
WMSYS 316
EXFSYS 310
SH 306
ORDDATA 248
OE 127
DBSNMP 57
IX 55
HR 34
PM 27
FLOWS_FILES 12
OWBSYS_AUDIT 12
ORDPLUGINS 10
OUTLN 9
BI 8
SI_INFORMTN_SCHEMA 8
ORACLE_OCM 8
SCOTT 7
APPQOSSYS 3
OWBSYS 2owner列的数据分布极不均衡，我们运行如下SQL。
select * from test where owner='SYS';SYS有30 808条数据，从72 462条数据里面查询30 808条数据，也就是说要返回表中42.5%的数据。
SQL> select 30808/72462*100 "Percent" from dual;
Percent
----------
42.5160774那么请思考，你认为以上查询应该使用索引吗？现在我们换一种查询语句。
select * from test where owner='SCOTT';SCOTT有7条数据，从72 462条数据里面查询7条数据，也就是说要返回表中0.009%的数据。
SQL> select 7/72462*100 "Percent" from dual;
Percent
----------
.009660236请思考，返回表中0.009%的数据应不应该走索引？
如果你还不懂索引，没关系，后面的章节我们会详细介绍。如果你回答不了上面的问题，我们先提醒一下。当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
当然了，返回表中5%以内的数据走索引，返回超过5%的数据就使用全表扫描，这个结论太绝对了，因为你还没掌握后面章节的知识，这里暂且记住5%这个界限就行。我们之所以在这里讲5%，是怕一些初学者不知道上面问题的答案而纠结。
现在有如下查询语句。
select * from test where owner=:B1;语句中，“:B1”是绑定变量，可以传入任意值，该查询可能走索引也可能走全表扫描。
现在得到一个结论：如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描。在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用select列，count(*) from表group by列order by 2 desc来查看列的数据分布。
如果SQL语句是单表访问，那么可能走索引，可能走全表扫描，也可能走物化视图扫描。在不考虑有物化视图的情况下，单表访问要么走索引，要么走全表扫描。现在，回忆一下走索引的条件：返回表中5%以内的数据走索引，超过5%的时候走全表扫描。相信大家读到这里，已经搞懂了单表访问的优化方法。
我们来看如下查询。
select * from test where object_id=:B1;不管object_id传入任何值，都应该走索引。
我们再思考如下查询语句。
select * from test where object_name=:B1;不管给object_name传入任何值，请问该查询应该走索引吗？
请你去查看object_name的数据分布。写到这里，其实有点想把本节名称改为“数据分布”。大家在以后的工作中一定要注意列的数据分布！
基数与总行数的比值再乘以100%就是某个列的选择性。
在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。
下面我们查看test表各个列的基数与选择性，为了查看选择性，必须先收集统计信息。关于统计信息，我们在第2章会详细介绍。下面的脚本用于收集test表的统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 100,
5 method_opt => 'for all columns size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.下面的脚本用于查看test表中每个列的基数与选择性。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'SCOTT'
11 and a.table_name = 'TEST';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- --------- -----------
OWNER 72462 29 .04 NONE 1
OBJECT_NAME 72462 44236 61.05 NONE 1
SUBOBJECT_NAME 72462 106 .15 NONE 1
OBJECT_ID 72462 72462 100 NONE 1
DATA_OBJECT_ID 72462 7608 10.5 NONE 1
OBJECT_TYPE 72462 44 .06 NONE 1
CREATED 72462 1366 1.89 NONE 1
LAST_DDL_TIME 72462 1412 1.95 NONE 1
TIMESTAMP 72462 1480 2.04 NONE 1
STATUS 72462 1 0 NONE 1
TEMPORARY 72462 2 0 NONE 1
GENERATED 72462 2 0 NONE 1
SECONDARY 72462 2 0 NONE 1
NAMESPACE 72462 21 .03 NONE 1
EDITION_NAME 72462 0 0 NONE 0
15 rows selected.请思考：什么样的列必须建立索引呢？
有人说基数高的列，有人说在where条件中的列。这些答案并不完美。基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。
当一个列选择性大于20%，说明该列的数据分布就比较均衡了。测试表test中object_name、object_id的选择性均大于20%，其中object_name列的选择性为61.05%。现在我们查看该列数据分布（为了方便展示，只输出前10行数据的分布情况）。
SQL> select *
2 from (select object_name, count(*)
3 from test
4 group by object_name
5 order by 2 desc)
6 where rownum <= 10;
OBJECT_NAME COUNT(*)
------------------ ----------
COSTS 30
SALES 30
SALES_CHANNEL_BIX 29
COSTS_TIME_BIX 29
COSTS_PROD_BIX 29
SALES_TIME_BIX 29
SALES_PROMO_BIX 29
SALES_PROD_BIX 29
SALES_CUST_BIX 29
DBMS_REPCAT_AUTH 5
10 rows selected.由上面的查询结果我们可知，object_name列的数据分布非常均衡。我们查询以下SQL。
select * from test where object_name=:B1;不管object_name传入任何值，最多返回30行数据。
什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。
下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。
也许有人会说：“我有个表很小，只有几百条，但是该表经常进行DML，会产生热点块，也会出性能问题。”对此我们并不想过多地讨论此问题，这属于应用程序设计问题，不属于SQL优化的范畴。
下面我们将通过实验为大家分享本书第一个全自动优化脚本。
抓出必须创建索引的列（请读者对该脚本适当修改，以便用于生产环境）。
首先，该列必须出现在where条件中，怎么抓出表的哪个列出现在where条件中呢？有两种方法，一种是可以通过V$SQL_PLAN抓取，另一种是通过下面的脚本抓取。
先执行下面的存储过程，刷新数据库监控信息。
begin
dbms_stats.flush_database_monitoring_info;
end;运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。
select r.name owner,
o.name table_name,
c.name column_name,
equality_preds, ---等值过滤
equijoin_preds, ---等值JOIN 比如where a.id=b.id
nonequijoin_preds, ----不等JOIN
range_preds, ----范围过滤次数 > >= < <= between and
like_preds, ----LIKE过滤
null_preds, ----NULL 过滤
timestamp
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'SCOTT'
and o.name = 'TEST';下面是实验步骤。
我们首先运行一个查询语句，让owner与object_id列出现在where条件中。
SQL> select object_id, owner, object_type
2 from test
3 where owner = 'SYS'
4 and object_id < 100
5 and rownum <= 10;
OBJECT_ID OWNER OBJECT_TYPE
---------- -------------------- -----------
20 SYS TABLE
46 SYS INDEX
28 SYS TABLE
15 SYS TABLE
29 SYS CLUSTER
3 SYS INDEX
25 SYS TABLE
41 SYS INDEX
54 SYS INDEX
40 SYS INDEX
10 rows selected.其次刷新数据库监控信息。
SQL> begin
2 dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.然后我们查看test表有哪些列出现在where条件中。
SQL> select r.name owner, o.name table_name, c.name column_name
2 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
3 where o.obj# = u.obj#
4 and c.obj# = u.obj#
5 and c.col# = u.intcol#
6 and r.name = 'SCOTT'
7 and o.name = 'TEST';
OWNER TABLE_NAME COLUMN_NAME
---------- ---------- ------------------------------
SCOTT TEST OWNER
SCOTT TEST OBJECT_ID接下来我们查询出选择性大于等于20%的列。
SQL> select a.owner,
2 a.table_name,
3 a.column_name,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity
5 from dba_tab_col_statistics a, dba_tables b
6 where a.owner = b.owner
7 and a.table_name = b.table_name
8 and a.owner = 'SCOTT'
9 and a.table_name = 'TEST'
10 and a.num_distinct / b.num_rows >= 0.2;
OWNER TABLE_NAME COLUMN_NAME SELECTIVITY
---------- ---------- ------------- -----------
SCOTT TEST OBJECT_NAME 61.05
SCOTT TEST OBJECT_ID 100最后，确保这些列没有创建索引。
SQL> select table_owner, table_name, column_name, index_name
2 from dba_ind_columns
3 where table_owner = 'SCOTT'
4 and table_name = 'TEST';
未选定行把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。
SQL> select owner,
2 column_name,
3 num_rows,
4 Cardinality,
5 selectivity,
6 'Need index' as notice
7 from (select b.owner,
8 a.column_name,
9 b.num_rows,
10 a.num_distinct Cardinality,
11 round(a.num_distinct / b.num_rows * 100, 2) selectivity
12 from dba_tab_col_statistics a, dba_tables b
13 where a.owner = b.owner
14 and a.table_name = b.table_name
15 and a.owner = 'SCOTT'
16 and a.table_name = 'TEST')
17 where selectivity >= 20
18 and column_name not in (select column_name
19 from dba_ind_columns
20 where table_owner = 'SCOTT'
21 and table_name = 'TEST')
22 and column_name in
23 (select c.name
24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
25 where o.obj# = u.obj#
26 and c.obj# = u.obj#
27 and c.col# = u.intcol#
28 and r.name = 'SCOTT'
29 and o.name = 'TEST');
OWNER COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NOTICE
---------- ------------- ---------- ----------- ----------- ----------
SCOTT OBJECT_ID 72462 72462 100 Need index前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。
下面我们还是以测试表test为例，用实验讲解直方图。
首先我们对测试表test收集统计信息，在收集统计信息的时候，不收集列的直方图，语句for all columns size 1表示对所有列都不收集直方图。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 100,
5 method_opt => 'for all columns size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.Histogram为none表示没有收集直方图。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'SCOTT'
11 and a.table_name = 'TEST';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- --------- -----------
OWNER 72462 29 .04 NONE 1
OBJECT_NAME 72462 44236 61.05 NONE 1
SUBOBJECT_NAME 72462 106 .15 NONE 1
OBJECT_ID 72462 72462 100 NONE 1
DATA_OBJECT_ID 72462 7608 10.5 NONE 1
OBJECT_TYPE 72462 44 .06 NONE 1
CREATED 72462 1366 1.89 NONE 1
LAST_DDL_TIME 72462 1412 1.95 NONE 1
TIMESTAMP 72462 1480 2.04 NONE 1
STATUS 72462 1 0 NONE 1
TEMPORARY 72462 2 0 NONE 1
GENERATED 72462 2 0 NONE 1
SECONDARY 72462 2 0 NONE 1
NAMESPACE 72462 21 .03 NONE 1
EDITION_NAME 72462 0 0 NONE 0
15 rows selected.owner列基数很低，现在我们对owner列进行查询。
SQL> set autot trace
SQL> select * from test where owner='SCOTT';
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K| 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 2499 | 236K| 289 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')请注意看粗体字部分，查询owner＝'SCOTT'返回了7条数据，但是CBO在计算Rows的时候认为owner='SCOTT'返回2 499条数据，Rows估算得不是特别准确。从72 462条数据里面查询出7条数据，应该走索引，所以现在我们对owner列创建索引。
SQL> create index idx_owner on test(owner);
Index created.
```
我们再来查询一下。
```
SQL> select * from test where owner='SCOTT';
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K | 73 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID |TEST | 2499 | 236K | 73 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN |IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')``` 现在我们查询`owner='SYS'`。
SQL> select * from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')注意粗字体部分，查询owner='SYS'返回了30 808条数据。从72 462条数据里面返回30 808条数据能走索引吗？很明显应该走全表扫描。也就是说该执行计划是错误的。
为什么查询owner='SYS'的执行计划会用错呢？因为owner这个列基数很低，只有29，而表的总行数是72 462。前文着重强调过，当列没有收集直方图统计信息的时候，CBO会认为该列数据分布是均衡的。正是因为CBO认为owner列数据分布是均衡的，不管owner等于任何值，CBO估算的Rows永远都是2 499。而这2 499是怎么来的呢？答案如下。
SQL> select round(72462/29) from dual;
round(72462/29)
--------------
2499现在大家也知道了，执行计划里面的Rows是假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。很多DBA到现在还不知道执行计划中Rows是假的这个真相，真是令人遗憾。
在做SQL优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows。注意：我们说的是比较准确的Rows。CBO是无法得到精确的Rows的，因为对表收集统计信息的时候，统计信息一般都不会按照100%的标准采样收集，即使按照100%的标准采样收集了表的统计信息，表中的数据也随时在发生变更。另外计算Rows的数学公式目前也是有缺陷的，CBO永远不可能计算得到精确的Rows。
如果CBO每次都能计算得到精确的Rows，那么相信我们这个时候只需要关心业务逻辑、表设计、SQL写法以及如何建立索引了，再也不用担心SQL会走错执行计划了。
Oracle12c的新功能SQL Plan Directives在一定程度上解决了Rows估算不准而引发的SQL性能问题。关于SQL Plan Directives，本书不做过多讨论。
为了让CBO选择正确的执行计划，我们需要对owner列收集直方图信息，从而告知CBO该列数据分布不均衡，让CBO在计算Rows的时候参考直方图统计。现在我们对owner列收集直方图。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 100,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.查看一下owner列的直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'SCOTT'
11 and a.table_name = 'TEST';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- ---------- -----------
OWNER 72462 29 .04 FREQUENCY 29
OBJECT_NAME 72462 44236 61.05 NONE 1
SUBOBJECT_NAME 72462 106 .15 NONE 1
OBJECT_ID 72462 72462 100 NONE 1
DATA_OBJECT_ID 72462 7608 10.5 NONE 1
OBJECT_TYPE 72462 44 .06 NONE 1
CREATED 72462 1366 1.89 NONE 1
LAST_DDL_TIME 72462 1412 1.95 NONE 1
TIMESTAMP 72462 1480 2.04 NONE 1
STATUS 72462 1 0 NONE 1
TEMPORARY 72462 2 0 NONE 1
GENERATED 72462 2 0 NONE 1
SECONDARY 72462 2 0 NONE 1
NAMESPACE 72462 21 .03 NONE 1
EDITION_NAME 72462 0 0 NONE 0
15 rows selected.现在我们再来查询上面的SQL，看执行计划是否还会走错并且验证Rows是否还会算错。
SQL> select * from test where owner='SCOTT';
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 679 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 679 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER| 7 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
SQL> select * from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30808 | 2918K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 30808 | 2918K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')对owner列收集完直方图之后，CBO估算的Rows就基本准确了，一旦Rows估算对了，那么执行计划也就不会出错了。
大家是不是很好奇，为什么收集完直方图之后，Rows计算得那么精确，收集直方图究竟完成了什么操作呢？对owner列收集直方图其实就相当于运行了以下SQL。
select owner,count(*) from test group by owner;直方图信息就是以上SQL的查询结果，这些查询结果会保存在数据字典中。这样当我们查询owner为任意值的时候，CBO总会算出正确的Rows，因为直方图已经知道每个值有多少行数据。
如果SQL使用了绑定变量，绑定变量的列收集了直方图，那么该SQL就会引起绑定变量窥探。绑定变量窥探是一个老生常谈的问题，这里不多做讨论。Oracle11g引入了自适应游标共享（Adaptive Cursor Sharing），基本上解决了绑定变量窥探问题，但是自适应游标共享也会引起一些新问题，对此也不做过多讨论。
当我们遇到一个SQL有绑定变量怎么办？其实很简单，我们只需要运行以下语句。
select 列, count(*) from test group by 列 order by 2 desc;如果列数据分布均衡，基本上SQL不会出现问题；如果列数据分布不均衡，我们需要对列收集直方图统计。
关于直方图，其实还有非常多的话题，比如直方图的种类、直方图的桶数等，本书在此不做过多讨论。在我们看来，读者只需要知道直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。
什么样的列需要收集直方图呢？当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。
下面我们为大家分享本书第二个全自动化优化脚本。
抓出必须创建直方图的列（大家可以对该脚本进行适当修改，以便用于生产环境）。
SQL> select a.owner,
2 a.table_name,
3 a.column_name,
4 b.num_rows,
5 a.num_distinct,
6 trunc(num_distinct / num_rows * 100,2) selectivity,
7 'Need Gather Histogram' notice
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = 'SCOTT'
10 and a.table_name = 'TEST'
11 and a.owner = b.owner
12 and a.table_name = b.table_name
13 and num_distinct / num_rows<0.01
14 and (a.owner, a.table_name, a.column_name) in
15 (select r.name owner, o.name table_name, c.name column_name
16 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
17 where o.obj# = u.obj#
18 and c.obj# = u.obj#
19 and c.col# = u.intcol#
20 and r.name = 'SCOTT'
21 and o.name = 'TEST')
22 and a.histogram ='NONE';
OWNER TABLE COLUM NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE
----- ----- ----- ---------- ------------ ----------- ----------------------
SCOTT TEST OWNER 72462 29 .04 Need Gather Histogram当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。
在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！
大家还记得1.3节中错误的执行计划吗？
SQL> select * from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据，回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）。该SQL返回了30 808行数据，那么回表一共就需要30 808次。
请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？
为了得到答案，请大家在SQLPLUS中进行实验。为了消除arraysize参数对逻辑读的影响，设置arraysize=5000。arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。
SQL> set arraysize 5000
SQL> set autot trace
SQL> select owner from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 373050211
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 14994 | 6 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OWNER | 2499 | 14994 | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
74 consistent gets
0 physical reads
0 redo size
155251 bytes sent via SQL*Net to client
486 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30808 rows processed从上面的实验可见，索引扫描只耗费了74个逻辑读。
SQL> select * from test where owner='SYS';
30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
877 consistent gets
0 physical reads
0 redo size
3120934 bytes sent via SQL*Net to client
486 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30808 rows processed
SQL> set autot off
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
2 from test
3 where owner = 'SYS';
BLOCKS
----------
796SQL在有回表的情况下，一共耗费了877个逻辑读，那么这877个逻辑读是怎么来的呢？
SQL返回的30 808条数据一共存储在796个数据块中，访问这796个数据块就需要消耗796个逻辑读，加上索引扫描的74个逻辑读，再加上7个逻辑读[其中7=ROUND（30808/5000）]，这样累计起来刚好就是877个逻辑读。
因此我们可以判断，该SQL的性能确实绝大部分损失在回表中！
更糟糕的是：假设30 808条数据都在不同的数据块中，表也没有被缓存在buffer cache中，那么回表一共需要耗费30 808个物理I/O，这太可怕了。
大家看到这里，是否能回答为什么返回表中5%以内的数据走索引、超过表中5%的数据走全表扫描？根本原因就在于回表。
在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表次数太多，从而导致性能严重下降。
Oracle12c的新功能批量回表（TABLE ACCESS BY INDEX ROWID BATCHED）在一定程度上改善了单行回表（TABLE ACCESS BY INDEX ROWID）的性能。关于批量回表本书不做讨论。
什么样的SQL必须要回表？
Select * from table where ...这样的SQL就必须回表，所以我们必须严禁使用Select *。那什么样的SQL不需要回表？
Select count(*) from table这样的SQL就不需要回表。
当要查询的列也包含在索引中，这个时候就不需要回表了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。
当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID前面有“*”），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。
关于如何创建组合索引，这问题太复杂了，我们在本书8.3节、9.1节以及第10章都会反复提及如何创建组合索引。
集群因子用于判断索引回表需要消耗的物理I/O次数。
我们先对测试表test的object_id列创建一个索引idx_id。
SQL> create index idx_id on test(object_id);
Index created.然后我们查看该索引的集群因子。
SQL> select owner, index_name, clustering_factor
2 from dba_indexes
3 where owner = 'SCOTT'
4 and index_name = 'IDX_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR
---------- ---------- -----------------
SCOTT IDX_ID 1094索引idx_id的叶子块中有序地存储了索引的键值以及键值对应行所在的ROWID。
SQL> select * from (
2 select object_id, rowid
3 from test
4 where object_id is not null
5 order by object_id) where rownum<=5;
OBJECT_ID ROWID
---------- ------------------
2 AAASNJAAEAAAAITAAw
3 AAASNJAAEAAAAITAAF
4 AAASNJAAEAAAAITAAx
5 AAASNJAAEAAAAITAAa
6 AAASNJAAEAAAAITAAV集群因子的算法如下。
首先我们比较2、3 对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor值加1。
然后我们比较3、4对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor值不变；如果不在同一个数据块，那么Clustering Factor值加1。
接下来我们比较4、5对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor值加1。
像上面步骤一样，一直这样有序地比较下去，直到比较完索引中最后一个键值。
根据算法我们知道集群因子介于表的块数和表行数之间。
如果集群因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成。
如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。
集群因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。
集群因子不会影响索引唯一扫描（INDEX UNIQUE SCAN），因为索引唯一扫描只返回一条数据。集群因子更不会影响索引快速全扫描（INDEX FAST FULL SCAN），因为索引快速全扫描不回表。
下面是根据集群因子算法人工计算集群因子的SQL脚本。
SQL> select sum(case
2 when block#1 = block#2 and file#1 = file#2 then
3 0
4 else
5 1
6 end) CLUSTERING_FACTOR
7 from (select dbms_rowid.rowid_relative_fno(rowid) file#1,
8 lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,
9 dbms_rowid.rowid_block_number(rowid) block#1,
10 lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2
11 from test
12 where object_id is not null);
CLUSTERING_FACTOR
-----------------
1094我们来查看索引idx_id的集群因子接近表的总行数还是表的总块数。
通过前面的章节我们知道，表的总行数为72 462行。
表的总块数如下可知。
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
2 from test;
BLOCKS
----------
1032集群因子非常接近表的总块数。现在，我们来查看下面SQL语句的执行计划。
SQL> set arraysize 5000
SQL> set autot trace
SQL> select * from test where object_id < 1000;
942 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3946039639
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 970 | 94090 | 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 970 | 94090 | 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 970 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
86510 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
942 rows processed该SQL耗费了17个逻辑读。
现在我们新建一个测试表test2并且对数据进行随机排序。
SQL> create table test2 as select * from test order by dbms_random.value;
Table created.我们在object_id列创建一个索引idx_id2。
SQL> create index idx_id2 on test2(object_id);
Index created.我们查看索引idx_id2的集群因子。
SQL> select owner, index_name, clustering_factor
2 from dba_indexes
3 where owner = 'SCOTT'
4 and index_name = 'IDX_ID2';
OWNER INDEX_NAME CLUSTERING_FACTOR
---------- ---------- -----------------
SCOTT IDX_ID2 72393索引idx_id2的集群因子接近于表的总行数，回表的时候会读取更多的数据块，现在我们来看一下SQL的执行计划。
SQL> set arraysize 5000
SQL> set autot trace
SQL> select /*+ index(test2) */ * from test2 where object_id <1000;
942 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3711990673
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 942 | 190K| 855 (0)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 942 | 190K| 855 (0)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IDX_ID2 | 942 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
943 consistent gets
0 physical reads
0 redo size
86510 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
942 rows processed通过上面实验我们得知，集群因子太大会严重影响索引回表的性能。
集群因子究竟影响的是什么性能呢？集群因子影响的是索引回表的物理I/O次数。我们假设索引范围扫描返回了1 000行数据，如果buffer cache中没有缓存表的数据块，假设这1000行数据都在同一个数据块中，那么回表需要耗费的物理I/O就只需要一个；假设这1000行数据都在不同的数据块中，那么回表就需要耗费1 000个物理I/O。因此，集群因子影响索引回表的物理I/O次数。
请注意，不要尝试重建索引来降低集群因子，这根本没用，因为表中的数据顺序始终没变。唯一能降低集群因子的办法就是根据索引列排序对表进行重建（create table new_table as select * from old_table order by 索引列），但是这在实际操作中是不可取的，因为我们无法照顾到每一个索引。
怎么才能避免集群因子对SQL查询性能产生影响呢？其实前文已经有了答案，集群因子只影响索引范围扫描和索引全扫描。当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响。
再次强调一遍，在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。
如果无法避免回表，怎么做才能消除回表对SQL查询性能产生影响呢？当我们把表中所有的数据块缓存在buffer cache中，这个时候不管集群因子多大，对SQL查询性能也没有多大影响，因为这时不需要物理I/O，数据块全在内存中访问速度是非常快的。
在本书第6章中我们还会进一步讨论集群因子。
关系型数据库中，表与表之间会进行关联，在进行关联的时候，我们一定要理清楚表与表之间的关系。表与表之间存在3种关系。一种是1∶1关系，一种是1∶N关系，最后一种是N∶N关系。搞懂表与表之间关系，对于SQL优化、SQL等价改写、表设计优化以及分表分库都有巨大帮助。
两表在进行关联的时候，如果两表属于1∶1关系，关联之后返回的结果也是属于1的关系，数据不会重复。如果两表属于1∶N关系，关联之后返回的结果集属于N的关系。如果两表属于N∶N关系，关联之后返回的结果集会产生局部范围的笛卡儿积，N∶N关系一般不存在内/外连接中，只能存在于半连接或者反连接中。
如果我们不知道业务，不知道数据字典，怎么判断两表是什么关系呢？我们以下面SQL为例子。
select * from emp e, dept d where e.deptno = d.deptno;我们只需要对两表关联列进行汇总统计就能知道两表是什么关系。
SQL> select deptno, count(*) from emp group by deptno order by 2 desc;
DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3
SQL> select deptno, count(*) from dept group by deptno order by 2 desc;
DEPTNO COUNT(*)
---------- ----------
10 1
40 1
30 1
20 1从上面查询我们可以知道两表emp与dept是N∶1关系。搞清楚表与表之间关系对于SQL优化很有帮助。
2013年，我们曾遇到一个案例，SQL运行了12秒，SQL文本如下。
select count(*) from a left join b on a.id=b.id;案例中a与b是1∶1关系，a与b都是上千万数据量。因为a与b是使用外连接进行关联，不管a与b是否关联上，始终都会返回a的数据，SQL语句中求的是两表关联后的总行数，因为两表是1∶1关系，关联之后数据不会翻番，那么该SQL等价于如下文本。
select count(*) from a;我们将SQL改写之后，查询可以秒出。如果a与b是n∶1关系，我们也可以将b表去掉，因为两表关联之后数据不会翻倍。如果b表属于n的关系，这时我们不能去掉b表，因为这时关联之后数据量会翻番。
在本书后面的标量子查询等价改写、半连接等价改写以及SQL优化案例章节中我们就会用到表与表之间关系这个重要的概念。
前面提到，只有大表才会产生性能问题，那么怎么才能让优化器知道某个表多大呢？这就需要对表收集统计信息。我们在第一章提到的基数、直方图、集群因子等概念都需要事先收集统计信息才能得到。
统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL的执行计划就会跑偏，SQL也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。
统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。
关于系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息本书不做讨论，本书重点讨论表的统计信息、列的统计信息以及索引的统计信息。
表的统计信息主要包含表的总行数（num_rows）、表的块数（blocks）以及行平均长度（avg_row_len），我们可以通过查询数据字典DBA_TABLES获取表的统计信息。
现在我们创建一个测试表T_STATS。
SQL> create table t_stats as select * from dba_objects;
Table created.我们查看表T_STATS常用的表的统计信息。
SQL> select owner, table_name, num_rows, blocks, avg_row_len
2 from dba_tables
3 where owner = 'SCOTT'
4 and table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT T_STATS因为T_STATS是新创建的表，没有收集过统计信息，所以从DBA_TABLES查询数据是空的。
现在我们来收集表T_STATS的统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们再次查看表的统计信息。
SQL> select owner, table_name, num_rows, blocks, avg_row_len
2 from dba_tables
3 where owner = 'SCOTT'
4 and table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT T_STATS 72674 1061 97从查询中我们可以看到，表T_STATS一共有72 674行数据，1 061个数据块，平均行长度为97字节。
列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况（直方图）。我们可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。
现在我们查看表T_STATS常用的列统计信息。
SQL> select column_name, num_distinct, num_nulls, num_buckets, histogram
2 from dba_tab_col_statistics
3 where owner = 'SCOTT'
4 and table_name = 'T_STATS';
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS HISTOGRAM
--------------- ------------ ---------- ----------- --------------------
EDITION_NAME 0 72674 0 NONE
NAMESPACE 21 1 1 NONE
SECONDARY 2 0 1 NONE
GENERATED 2 0 1 NONE
TEMPORARY 2 0 1 NONE
STATUS 2 0 1 NONE
TIMESTAMP 1592 1 1 NONE
LAST_DDL_TIME 1521 1 1 NONE
CREATED 1472 0 1 NONE
OBJECT_TYPE 45 0 1 NONE
DATA_OBJECT_ID 7796 64833 1 NONE
OBJECT_ID 72673 1 1 NONE
SUBOBJECT_NAME 140 72145 1 NONE
OBJECT_NAME 44333 0 1 NONE
OWNER 31 0 1 NONE
15 rows selected.上面查询中，第一个列表示列名字，第二个列表示列的基数，第三个列表示列中NULL值的数量，第四个列表示直方图的桶数，最后一个列表示直方图类型。
在工作中，我们经常使用下面脚本查看表和列的统计信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 NONE 1
15 rows selected.索引的统计信息主要包含索引blevel（索引高度-1）、叶子块的个数（leaf_blocks）以及集群因子（clustering_factor）。我们可以通过数据字典DBA_INDEXES查看索引的统计信息。
我们在OBJECT_ID列上创建一个索引。
SQL> create index idx_t_stats_id on t_stats(object_id);
Index created.创建索引的时候会自动收集索引的统计信息，运行下面脚本查看索引的统计信息。
SQL> select blevel, leaf_blocks, clustering_factor,status
2 from dba_indexes
3 where owner = 'SCOTT'
4 and index_name = 'IDX_T_STATS_ID';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- ----------------
1 161 1127 VALID如果要单独对索引收集统计信息，可以使用下面脚本收集。
SQL> BEGIN
2 DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
3 indname => 'IDX_T_STATS_ID');
4 END;
5 /
PL/SQL procedure successfully completed.在本书第6章中，我们会详细介绍表的统计信息、列的统计信息以及索引的统计信息是如何被应用于成本计算的。
我们通常使用下面脚本收集表和索引的统计信息。
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，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用AUTO方式，也就是数据库默认方式，因此，在后面的脚本中，省略该选项。
estimate_percent 表示采样率，范围是0.000 001～100。
我们一般对小于1GB的表进行100%采样，因为表很小，即使100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。因此我们建议对小表100%采样。
我们一般对表大小在1GB～5GB的表采样50%，对大于5GB的表采样30%。如果表特别大，有几十甚至上百GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。
一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于30%。
我们可以使用下面脚本查看表的采样率。
SQL> SELECT owner,
2 table_name,
3 num_rows,
4 sample_size,
5 round(sample_size / num_rows * 100) estimate_percent
6 FROM DBA_TAB_STATISTICS
7 WHERE owner='SCOTT' AND table_name='T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
--------------- --------------- ---------- ----------- ----------------
SCOTT T_STATS 72674 72674 100从上面查询我们可以看到，对表T_STATS是100%采样的。现在我们将采样率设置为30%。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 30,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> SELECT owner,
2 table_name,
3 num_rows,
4 sample_size,
5 round(sample_size / num_rows * 100) estimate_percent
6 FROM DBA_TAB_STATISTICS
7 WHERE owner='SCOTT' AND table_name='T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
--------------- --------------- ---------- ----------- ----------------
SCOTT 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'表示所有列都不收集直方图，如下所示。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 NONE 1
15 rows selected.从上面查询我们看到，所有列都没有收集直方图。
method_opt => 'for all columns size skewonly'表示对表中所有列收集自动判断是否收集直方图，如下所示。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看直方图信息，如下所示。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 FREQUENCY 21
SECONDARY 72674 0 2 0 FREQUENCY 2
GENERATED 72674 0 2 0 FREQUENCY 2
TEMPORARY 72674 0 2 0 FREQUENCY 2
STATUS 72674 0 2 0 FREQUENCY 2
TIMESTAMP 72674 1 1592 2.19 HEIGHT BALANCED 254
LAST_DDL_TIME 72674 1 1521 2.09 HEIGHT BALANCED 254
CREATED 72674 0 1472 2.03 HEIGHT BALANCED 254
OBJECT_TYPE 72674 0 45 .06 FREQUENCY 45
DATA_OBJECT_ID 72674 64833 7796 10.73 HEIGHT BALANCED 254
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 FREQUENCY 140
OBJECT_NAME 72674 0 44333 61 HEIGHT BALANCED 254
OWNER 72674 0 31 .04 FREQUENCY 31
15 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> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们执行下面SQL，以便将owner列放入where条件中。
SQL> select count(*) from t_stats where owner='SYS';
COUNT(*)
----------
30850接下来我们刷新数据库监控信息。
SQL> begin
2 dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.然后我们查看直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 FREQUENCY 31
15 rows selected.从上面查询我们可以看到，Oracle自动地对owner列收集了直方图。
思考，如果将选择性比较高的列放入where条件中，会不会自动收集直方图？现在我们将OBJECT_NAME列放入where条件中。
SQL> select count(*) from t_stats where object_name='EMP';
COUNT(*)
----------
3然后我们刷新数据库监控信息。
SQL> begin
2 dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.我们收集统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看OBJECT_NAME列是否收集了直方图。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 FREQUENCY 31
15 rows selected.从上面查询我们可以看到，OBJECT_NAME列没有收集直方图。由此可见，使用AUTO方式收集直方图很智能。mothod_opt默认的参数就是 for all columns size auto。
method_opt => 'for all columns size repeat'表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。
当前只对OWNER列收集了直方图，现在我们使用REPEAT方式收集直方图。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size repeat',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 FREQUENCY 31
15 rows selected.从查询中我们可以看到，使用REPEAT方式延续了上次收集直方图的策略。对一个运行稳定的系统，我们应该采用REPEAT方式收集直方图。
method_opt => 'for columns object_type size skewonly'表示单独对OBJECT_TYPE列收集直方图，对于其余列，如果之前收集过直方图，现在也收集直方图。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for columns object_type size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 FREQUENCY 45
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 FREQUENCY 31
15 rows selected.从查询中我们可以看到，OBJECT_TYPE列收集了直方图，因为之前收集过owner列直方图，现在也跟着收集了owner列的直方图。
在实际工作中，我们需要对列收集直方图就收集直方图，需要删除某列直方图就删除其直方图，当系统趋于稳定之后，使用REPEAT方式收集直方图。
no_invalidate表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS. AUTO_INVALIDATE，表示让Oracle自己决定是否立即失效。我们建议将no_invalidate参数设置为FALSE，立即失效。因为我们发现有时候SQL执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为false。
degree表示收集统计信息的并行度，默认为NULL。如果表没有设置degree，收集统计信息的时候后就不开并行；如果表设置了degree，收集统计信息的时候就按照表的degree来开并行。可以查询DBA_TABLES.degree来查看表的degree，一般情况下，表的degree都为1。我们建议可以根据当时系统的负载、系统中CPU的个数以及表大小来综合判断设置并行度。
cascade表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息。我们一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。
收集完表的统计信息之后，如果表中有大量数据发生变化，这时表的统计信息就过期了，我们需要重新收集表的统计信息，如果不重新收集，可能会导致执行计划走偏。
以T_STATS为例，我们先在owner列上创建一个索引。
SQL> create index idx_t_stats_owner on t_stats(owner);
Index created.我们收集owner列的直方图信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们执行下面SQL并且查看执行计划（为了方便排版，省略了执行计划中的Time列）。
SQL> select * from t_stats where owner='SCOTT';
122 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3912915053
-----------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 122 | 11834 | 5 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T_STATS | 122 | 11834 | 5 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_STATS_OWNER | 122 | | 1 (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
13440 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
122 rows processedSQL的过滤条件是where owner='SCOTT'，因为收集了owner列的直方图统计，优化器能准确地估算出SQL返回122行数据，该SQL走的是索引范围扫描，执行计划是正确的。
现在我们更新表中的数据，将object_id<=10000的owner更新为'SCOTT'。
SQL> update t_stats set owner='SCOTT' where object_id<=10000;
9709 rows updated.
SQL> commit;
Commit complete.我们再次执行SQL并且查看执行计划。
SQL> select * from t_stats where owner='SCOTT';
9831 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3912915053
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 11834 | 5 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T_STATS | 122 | 11834 | 5 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_STATS_OWNER | 122 | | 1 (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1502 consistent gets
0 physical reads
3236 redo size
1005607 bytes sent via SQL*Net to client
7625 bytes received via SQL*Net from client
657 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9831 rows processed从执行计划中可以看到，SQL一共返回了9 831行数据，但是优化器评估只返回122行数据，因为优化器评估where owner='SCOTT'只返回122行数据，所以执行计划走了索引，但是实际上应该走全表扫描。
为什么优化器会评估where owner='SCOTT'只返回122行数据呢？原因在于表中有大量数据发生了变化，但是统计信息没有得到及时更新，优化器还是采用的老的（过期的）统计信息来估算返回行数。
我们可以使用下面方法检查表统计信息是否过期，先刷新数据库监控信息。
SQL> begin
2 dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.然后我们执行下面查询。
SQL> select owner, table_name , object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where owner = 'SCOTT'
4 and table_name = 'T_STATS';
OWNER TABLE_NAME OBJECT_TYPE STALE_STATS LAST_ANALYZED
---------- --------------- --------------- --------------- -------------
SCOTT T_STATS TABLE YES 24-MAY-17STALE_STATS显示为YES表示表的统计信息过期了。如果STALE_STATS显示为NO，表示表的统计信息没有过期。
我们可以通过下面查询找出统计信息过期的原因。
SQL> select table_owner, table_name, inserts, updates, deletes, timestamp
2 from all_tab_modifications
3 where table_owner = 'SCOTT'
4 and table_name = 'T_STATS';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
--------------- --------------- ---------- ---------- ---------- ---------
SCOTT T_STATS 0 9709 0 24-MAY-17从查询结果我们可以看到，从上一次收集统计信息到现在，表被更新了9 709行数据，所以表的统计信息过期了。
现在我们重新收集表的统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们再次查看SQL的执行计划。
SQL> select * from t_stats where owner='SCOTT';
9831 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1525972472
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9831 | 931K| 187 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_STATS | 9831 | 931K| 187 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1690 consistent gets
0 physical reads
0 redo size
418062 bytes sent via SQL*Net to client
7625 bytes received via SQL*Net from client
657 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9831 rows processed重新收集完统计信息之后，优化器估算返回9 831行数据，这次SQL没走索引扫描而是走的全表扫描，SQL走了正确的执行计划。
细心的读者可能会认为走索引扫描的性能高于全表扫描，因为索引扫描逻辑读为1 502，而全表扫描逻辑读为1 690，所以索引扫描性能高。其实这是不对的，衡量一个SQL的性能不能只看逻辑读，还要结合SQL的物理I/O次数综合判断。本书第4章会就为什么这里全表扫描性能比索引扫描性能更高给出详细解释。
Oracle是怎么判断一个表的统计信息过期了呢？当表中有超过10%的数据发生变化（INSERT，UPDATE，DELETE），就会引起统计信息过期。
现在我们查看表一共有多少行数据。
SQL> select count(*) from t_stats;
COUNT(*)
----------
72674删除表中10%的数据，然后我们查看表的统计信息是否过期。
SQL> delete t_stats where rownum<=72674*0.1+1;
7268 rows deleted.
SQL> commit;我们刷新数据库监控信息。
SQL> begin
2 dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.我们检查表统计信息是否过期。
SQL> select owner, table_name, object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where owner = 'SCOTT'
4 and table_name = 'T_STATS';
OWNER TABLE_NAME OBJECT_TYP STALE_STATS LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT T_STATS TABLE YES 24-MAY-17STALE_STATS显示为YES，说明表的统计信息过期了。
我们查看统计信息过期原因。
SQL> select table_owner, table_name, inserts, updates, deletes, timestamp
2 from all_tab_modifications
3 where table_owner = 'SCOTT'
4 and table_name = 'T_STATS';
TABLE_OWNE TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
---------- ---------- ---------- ---------- ---------- ------------------
SCOTT T_STATS 0 0 7268 24-MAY-17从上面查询我们可以看到表被删除了7 268行数据，从而导致表的统计信息过期。
在进行SQL优化的时候，我们需要检查表的统计信息是否过期，如果表的统计信息过期了，要及时更新表的统计信息。
数据字典all_tab_modifications还可以用来判断哪些表需要定期降低高水位，比如一个表经常进行insert、delete，那么这个表应该定期降低高水位，这个表的索引也应该定期重建。除此之外，all_tab_modifications还可以用来判断系统中哪些表是业务核心表、表的数据每天增长量等。
如果一个SQL有七八个表关联或者有视图套视图等，怎么快速检查SQL语句中所有的表统计信息是否过期呢？
现有如下SQL。
select * from emp e,dept d where e.deptno=d.deptno;我们可以先用explain plan for命令，在plan_table中生成SQL的执行计划。
SQL> explain plan for select * from emp e,dept d where e.deptno=d.deptno;
Explained.然后我们使用下面脚本检查SQL语句中所有的表的统计信息是否过期。
SQL> select owner, table_name, object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where (owner, table_name) in
4 (select object_owner, object_name
5 from plan_table
6 where object_type like '%TABLE%'
7 union
8 select table_owner, table_name
9 from dba_indexes
10 where (owner, index_name) in
11 (select object_owner, object_name
12 from plan_table
13 where object_type like '%INDEX%'));
OWNER TABLE_NAME OBJECT_TYP STALE_STATS LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT DEPT TABLE NO 05-DEC-16
SCOTT EMP TABLE YES 22-OCT-16最后我们可以使用下面脚本检查SQL语句中表统计信息的过期原因。
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%'));当where条件中有多个谓词过滤条件，但是这些谓词过滤条件彼此是有关系的而不是相互独立的，这时我们可能需要收集扩展统计信息以便优化器能够估算出较为准确的行数（Rows）。
我们创建一个表T。
SQL> create table t as
2 select level as id, level || 'a' as a, level || level || 'b' as b
3 from dual
4 connect by level < 100;
Table created.在T表中，知道A列的值就知道B列的值，A和B这样的列就叫作相关列。
我们一直执行insert into t select * from t;直到T表中有3244032行数据。
我们对T表收集统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T',
4 estimate_percent => 100,
5 method_opt => 'for all columns size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看T表的统计信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'SCOTT'
11 and a.table_name = 'T';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- --------------- -----------
ID 3244032 99 0 FREQUENCY 99
A 3244032 99 0 FREQUENCY 99
B 3244032 99 0 FREQUENCY 99我们创建两个索引。
SQL> create index idx1 on t(a);
Index created.
SQL> create index idx2 on t(a,b);
Index created.现有如下SQL及其执行计划。
SQL> select * from t where a='1a' and b='11b';
32768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2303463401
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 331 | 4303 | 84 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 331 | 4303 | 84 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX2 | 331 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"='1a' AND "B"='11b')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11854 consistent gets
78 physical reads
0 redo size
775996 bytes sent via SQL*Net to client
24444 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed优化器估算返回331行数据，但是实际上返回了32 768行数据。为什么优化器估算返回的行数与真实返回的行数有这么大差异呢？这是因为优化器不知道A与B的关系，所以在估算返回行数的时候采用的是总行数A的选择性B的选择性。
SQL> select round(1/99/99*3244032) from dual;
round(1/99/99*3244032)
---------------------
331因为A列的值可以决定B列的值，所以上述SQL可以去掉B列的过滤条件。
SQL> select * from t where a='1a';
32768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32768 | 416K| 1775 (3)| 00:00:22 |
|* 1 | TABLE ACCESS FULL| T | 32768 | 416K| 1775 (3)| 00:00:22 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='1a')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10118 consistent gets
0 physical reads
0 redo size
441776 bytes sent via SQL*Net to client
24444 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed这时优化器能正确地估算返回的Rows。如果不想改写SQL，怎么才能让优化器得到比较准确的Rows呢？在Oracle11g之前可以使用动态采样（至少Level 4）。
SQL> alter session set optimizer_dynamic_sampling=4;
Session altered.
SQL> select * from t where a='1a' and b='11b';
32768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33845 | 429K| 1778 (3)| 00:00:22 |
|* 1 | TABLE ACCESS FULL| T | 33845 | 429K| 1778 (3)| 00:00:22 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='1a' AND "B"='11b')
Note
-----
- dynamic sampling used for this statement (level=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10118 consistent gets
0 physical reads
0 redo size
441776 bytes sent via SQL*Net to client
24444 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed使用动态采样Level4采样之后，优化器估算返回33 845行数据，实际返回了32 768行数据，这已经比较精确了。在Oracle11g以后，我们可以使用扩展统计信息将相关的列组合成一个列。
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T', '(A, B)') FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T','(A,B)')
----------------------------------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X现在我们对表重新收集统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T',
4 estimate_percent => 100,
5 method_opt => 'for columns SYS_STUNA$6DVXJXTP05EH56DTIR0X size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们查看T表的统计信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'SCOTT'
11 and a.table_name = 'T';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- ---------- --------
ID 3244032 99 0 FREQUENCY 99
A 3244032 99 0 FREQUENCY 99
B 3244032 99 0 FREQUENCY 99
SYS_STUNA$6DVXJXTP05EH56DTIR0X 3244032 99 0 FREQUENCY 99重新收集统计信息之后，扩展列SYS_STUNA$6DVXJXTP05EH56DTIR0X也收集了直方图。
我们再次执行SQL。
SQL> select * from t where a='1a' and b='11b';
32768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32768 | 416K| 1778 (3)| 00:00:22 |
|* 1 | TABLE ACCESS FULL| T | 32768 | 416K| 1778 (3)| 00:00:22 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='1a' AND "B"='11b')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10118 consistent gets
0 physical reads
0 redo size
441776 bytes sent via SQL*Net to client
24444 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed收集完扩展统计信息之后，优化器就能估算出较为准确的Rows。
需要注意的是，扩展统计信息只能用于等值查询，不能用于非等值查询。
在本书的SQL优化案例赏析章节中，我们将会为各位读者分享一个经典的扩展统计信息优化案例。
如果一个表从来没收集过统计信息，默认情况下Oracle会对表进行动态采样（Level=2）以便优化器估算出较为准确的Rows，动态采样的最终目的就是为了让优化器能够评估出较为准确的Rows。
现在我们创建一个测试表T_DYNA。
SQL> create table t_dyna as select * from dba_objects;
Table created.我们执行下面SQL并且查看执行计划。
SQL> select count(*) from t_dyna;
Execution Plan
----------------------------------------------------------
Plan hash value: 3809964769
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_DYNA | 65305 | 187 (1)| 00:00:03 |
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)因为表T_DYNA是才创建的新表，没有收集过统计信息，所以会启用动态采样。执行计划中dynamic sampling used for this statement (level=2)表示启用了动态采样，level表示采样级别，默认情况下采样级别为2。
动态采样的级别分为11级。
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自动决定。
在2.4节中我们已经演示过动态采样level 4的用途，现在将为各位读者演示动态采样level 3的用途。
我们执行下面SQL并且查看执行计划。
SQL> select * from t_dyna where substr(owner,4,3)='LIC';
27699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1744410282
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23044 | 4658K| 190 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_DYNA | 23044 | 4658K| 190 (3)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("OWNER",4,3)='LIC')
Note
-----
- dynamic sampling used for this statement (level=2)因为T_DYNA没有收集过统计信息，启用了动态采样，采样级别默认为level 2，动态采样估算的Rows(23 044)与真实的Rows(27 699)比较接近。
现在我们对表T_DYNA收集统计信息。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'T_DYNA',
4 estimate_percent => 100,
5 method_opt => 'for all columns size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.我们再次查看执行计划。
SQL> select * from t_dyna where substr(owner,4,3)='LIC';
27699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1744410282
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 728 | 70616 | 190 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_DYNA | 728 | 70616 | 190 (3)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("OWNER",4,3)='LIC')对表T_DYNA收集了统计信息之后，因为统计信息中没有包含substr(owner,4,3)的统计，所以优化器无法估算出较为准确的Rows，优化器估算返回了728行数据，而实际上返回了27 699行数据。现在我们将动态采样level设置为3。
SQL> alter session set optimizer_dynamic_sampling=3;
Session altered.我们执行SQL并且查看执行计划。
SQL> select * from t_dyna where substr(owner,4,3)='LIC';
27699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1744410282
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28795 | 2727K| 191 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_DYNA | 28795 | 2727K| 191 (3)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("OWNER",4,3)='LIC')
Note
-----
- dynamic sampling used for this statement (level=3)将动态采样设置为level 3之后，优化器发现where条件中有substr(owner,4,3)，无法估算出准确的Rows，因此对SQL启用了动态采样，动态采样估算返回了28 795行数据，接近于真实的行数27 699。
除了设置参数optimizer_dynamic_sampling启用动态采样外，我们还可以添加HINT启用动态采样。
SQL> alter session set optimizer_dynamic_sampling=2;
Session altered.
SQL> select /*+ dynamic_sampling(3) */ * from t_dyna where substr(owner,4,3)='LIC';
27699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1744410282
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28795 | 2727K| 191 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_DYNA | 28795 | 2727K| 191 (3)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("OWNER",4,3)='LIC')
Note
-----
- dynamic sampling used for this statement (level=3)如果表已经收集过统计信息并且优化器能够准确地估算出返回的Rows，即使添加了动态采样的HINT或者是设置了动态采样的参数为level 3，也不会启用动态采样。
SQL> select /*+ dynamic_sampling(3) */ * from t_dyna where owner='SYS';
30928 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1744410282
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30928 | 2929K| 188 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_DYNA | 30928 | 2929K| 188 (2)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')因为表T_DYNA收集过统计信息，优化器能够直接根据统计信息估算出较为准确的Rows，所以，即使添加了HINT：/*+ dynamic_sampling(3) */，也没有启用动态采样。
什么时候需要启用动态采样呢？
当系统中有全局临时表，就需要使用动态采样，因为全局临时表无法收集统计信息，我们建议对全局临时表至少启用level 4进行采样。
当执行计划中表的Rows估算有严重偏差的时候，例如相关列问题，或者两表关联有多个连接列，关联之后Rows算少，或者是where过滤条件中对列使用了substr、instr、like，又或者是where过滤条件中有非等值过滤，或者group by之后导致Rows估算错误，此时我们可以考虑使用动态采样，同样，我们建议动态采样至少设置为level 4。
在数据仓库系统中，有些报表SQL是采用Obiee/SAP BO/Congnos自动生成的，此类SQL一般都有几十行甚至几百行，SQL的过滤条件一般也比较复杂，有大量的AND和OR过滤条件，同时也可能有大量的where子查询过滤条件，SQL最终返回的数据量其实并不多。对于此类SQL，如果SQL执行缓慢，有可能是因为SQL的过滤条件太复杂，从而导致优化器不能估算出较为准确的Rows而产生了错误的执行计划。我们可以考虑启用动态采样level 6观察性能是否有所改善，我们曾利用该方法优化了大量的报表SQL。
最后，需要注意的是，不要在系统级更改动态采样级别，默认为2就行，如果某个表需要启用动态采样，直接在SQL语句中添加HINT即可。
优化器在计算执行计划的成本时依赖于统计信息，如果没有收集统计信息，或者是统计信息过期了，那么优化器就会出现严重偏差，从而导致性能问题。因此要确保统计信息准确性。虽然数据库自带有JOB每天晚上会定时收集数据库中所有表的统计信息，但是如果数据库特别大，自带的JOB无法完成全库统计信息收集。一些资深的DBA会关闭数据库自带的统计信息收集JOB，根据实际情况自己定制收集统计信息策略。
下面脚本用于收集SCOTT账户下统计信息过期了或者是从没收集过统计信息的表的统计信息，采样率也根据表的段大小做出了相应调整。
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;
/在实际工作中，我们可以根据自身数据库中实际情况，对以上脚本进行修改。
全局临时表无法收集统计信息，我们可以抓出系统中的全局临时表，抓出系统中使用到全局临时表的SQL，然后根据实际情况，对全局临时表进行动态采样，或者是人工对全局临时表设置统计信息（DBMS_STATS.SET_TABLE_STATS）。
下面脚本抓出系统中使用到全局临时表的SQL。
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;
