作者：罗炳森 黄超 钟侥
出版社：人民邮电出版社有限公司
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
SQL优化核心思想试读：
前言
近年来，随着系统的数据量逐年增加，并发量也成倍增长，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 - ProductionSQL> show userUSER 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/tigerConnected.SQL> create table test as select * from dba_objects;Table created.第1章 SQL优化必懂概念1.1 基数（CARDINALITY）
某个列唯一键（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 72462
TEST表的总行数为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 30808PUBLIC 27699SYSMAN 3491ORDSYS 2532APEX_030200 2406MDSYS 1509XDB 844OLAPSYS 719SYSTEM 529CTXSYS 366WMSYS 316EXFSYS 310SH 306ORDDATA 248OE 127DBSNMP 57IX 55HR 34PM 27FLOWS_FILES 12OWBSYS_AUDIT 12ORDPLUGINS 10OUTLN 9BI 8SI_INFORMTN_SCHEMA 8ORACLE_OCM 8SCOTT 7APPQOSSYS 3OWBSYS 2
owner列的数据分布极不均衡，我们运行如下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的数据分布。写到这里，其实有点想把本节名称改为“数据分布”。大家在以后的工作中一定要注意列的数据分布！1.2 选择性（SELECTIVITY）
基数与总行数的比值再乘以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 1OBJECT_NAME 72462 44236 61.05 NONE 1SUBOBJECT_NAME 72462 106 .15 NONE 1OBJECT_ID 72462 72462 100 NONE 1DATA_OBJECT_ID 72462 7608 10.5 NONE 1OBJECT_TYPE 72462 44 .06 NONE 1CREATED 72462 1366 1.89 NONE 1LAST_DDL_TIME 72462 1412 1.95 NONE 1TIMESTAMP 72462 1480 2.04 NONE 1STATUS 72462 1 0 NONE 1TEMPORARY 72462 2 0 NONE 1GENERATED 72462 2 0 NONE 1SECONDARY 72462 2 0 NONE 1NAMESPACE 72462 21 .03 NONE 1EDITION_NAME 72462 0 0 NONE 015 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 30SALES 30SALES_CHANNEL_BIX 29COSTS_TIME_BIX 29COSTS_PROD_BIX 29SALES_TIME_BIX 29SALES_PROMO_BIX 29SALES_PROD_BIX 29SALES_CUST_BIX 29DBMS_REPCAT_AUTH 510 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 INDEX10 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 OWNERSCOTT 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.05SCOTT 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 index1.3 直方图（HISTOGRAM）
前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（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 1OBJECT_NAME 72462 44236 61.05 NONE 1SUBOBJECT_NAME 72462 106 .15 NONE 1OBJECT_ID 72462 72462 100 NONE 1DATA_OBJECT_ID 72462 7608 10.5 NONE 1OBJECT_TYPE 72462 44 .06 NONE 1CREATED 72462 1366 1.89 NONE 1LAST_DDL_TIME 72462 1412 1.95 NONE 1TIMESTAMP 72462 1480 2.04 NONE 1STATUS 72462 1 0 NONE 1TEMPORARY 72462 2 0 NONE 1GENERATED 72462 2 0 NONE 1SECONDARY 72462 2 0 NONE 1NAMESPACE 72462 21 .03 NONE 1EDITION_NAME 72462 0 0 NONE 015 rows selected.
owner列基数很低，现在我们对owner列进行查询。SQL> set autot traceSQL> 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 29OBJECT_NAME 72462 44236 61.05 NONE 1SUBOBJECT_NAME 72462 106 .15 NONE 1OBJECT_ID 72462 72462 100 NONE 1DATA_OBJECT_ID 72462 7608 10.5 NONE 1OBJECT_TYPE 72462 44 .06 NONE 1CREATED 72462 1366 1.89 NONE 1LAST_DDL_TIME 72462 1412 1.95 NONE 1TIMESTAMP 72462 1480 2.04 NONE 1STATUS 72462 1 0 NONE 1TEMPORARY 72462 2 0 NONE 1GENERATED 72462 2 0 NONE 1SECONDARY 72462 2 0 NONE 1NAMESPACE 72462 21 .03 NONE 1EDITION_NAME 72462 0 0 NONE 015 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
试读结束[说明：试读内容隐藏了图片]