SQL优化核心思想-读书笔记

SQL优化核心思想 · AI大纲

SQL优化核心思想

罗炳森 黄超 钟侥

微信读书推荐值

80.4%

字数

47.1万字

2018 年 4 月出品

简介

结构化查询语言（Structured Query Language，SQL）是一种功能强大的数据库语言。它基于关系代数运算，功能丰富、语言简洁、使用方便灵活，已成为关系数据库的标准语言。本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。本书共分10章，从SQL基础知识、统计信息、执行计划、访问路径、表连接方式、成本计算、查询变换、调优技巧、经典案例、全自动SQL审核等角度介绍了有关SQL优化的方方面面。本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员，无论是初学者还是有一定基础的读者，都将从中获益。

内容提要

SQL优化简介

SQL是一种功能强大的数据库语言，基于关系代数运算。

本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。

本书内容概述

本书共分10章，从SQL基础知识、统计信息、执行计划、访问路径、表连接方式、成本计算、查询变换、调优技巧、经典案例、全自动SQL审核等角度介绍了有关SQL优化的方方面面。

本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。

适用读者群体

本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员。

无论是初学者还是有一定基础的读者，都将从中获益。

本书约定

本书约定

阅读本书之前请读者安装好Oracle数据库并且配置好示例账户Scott

本书使用的版本是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。

第1章 SQL优化必懂概念

基数(CARDINALITY)的概念和意义

基数是指某个列唯一键的数量

比如性别列，该列只有男女之分，所以这一列基数是2。

基数的高低影响列的数据分布

以测试表test为例，owner列和object_id列的基数分别如下所示。

数据分布对SQL查询的影响

当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描

SYS有30 808条数据，从72 462条数据里面查询30 808条数据，也就是说要返回表中42.5%的数据。

SCOTT有7条数据，从72 462条数据里面查询7条数据，也就是说要返回表中0.009%的数据。

如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描

在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用select列，count(*) from表group by列order by 2 desc来查看列的数据分布。

单表访问的优化方法

如果SQL语句是单表访问，那么可能走索引，可能走全表扫描，也可能走物化视图扫描

在不考虑有物化视图的情况下，单表访问要么走索引，要么走全表扫描。

现在，回忆一下走索引的条件：返回表中5%以内的数据走索引，超过5%的时候走全表扫描。

索引的使用条件

不管object_id传入任何值，都应该走索引。

如果给object_name传入任何值，该查询应该走索引吗？需要查看object_name的数据分布。

1.2 选择性（SELECTIVITY）

选择性的概念和重要性

选择性是基数与总行数的比值再乘以100%

基数必须对比总行数才有实际意义。

选择性大于20%的列数据分布比较均衡。

如何确定必须创建索引的列

当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引

如果表只有几百条数据，那我们就不用创建索引了。

通过V$SQL_PLAN抓取或通过脚本抓取表的哪个列出现在where条件中

先执行下面的存储过程，刷新数据库监控信息。

运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。

查询出选择性大于等于20%的列

把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。

1.3 直方图（HISTOGRAM）

数据分布不均衡导致的问题

数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划

如果没有对基数低的列收集直方图统计信息，基于成本的优化器(CBO)会认为该列数据分布是均衡的。

直方图的作用

直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows

对owner列收集完直方图之后，CBO估算的Rows就基本准确了，一旦Rows估算对了，那么执行计划也就不会出错了。

如何收集直方图

对基数很低、数据分布不均衡的列，使用DBMS_STATS.GATHER_TABLE_STATS过程收集直方图

方法_opt参数设置为'for columns owner size skewonly'。

直方图的应用场景

当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图

注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。

1.4 回表（TABLE ACCESS BY INDEX ROWID）

回表的概念和性能影响

回表是通过索引中记录的rowid访问表中的数据

索引会包含该列的键值以及键值对应行所在的rowid。

回表一般是单块读，回表次数太多会严重影响SQL性能

如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。

回表的次数和物理I/O次数的影响

索引返回多少行数据，回表就要回多少次

该SQL返回了30 808行数据，那么回表一共就需要30 808次。

回表一共需要耗费877个逻辑读，其中796个逻辑读访问存储数据的块，74个逻辑读来自索引扫描，7个逻辑读来自计算

SQL返回的30 808条数据一共存储在796个数据块中，访问这796个数据块就需要消耗796个逻辑读。

加上索引扫描的74个逻辑读，再加上7个逻辑读[其中7=ROUND(30808/5000)]，这样累计起来刚好就是877个逻辑读。

如何避免和优化回表

严禁使用Select *，避免回表

Select * from table where ... 这样的SQL就必须回表。

当要查询的列也包含在索引中，就不需要回表了

我们往往会建立组合索引来消除回表，从而提升查询性能。

当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤，也需要创建组合索引，进而消除回表再过滤，从而提升查询性能

关于如何创建组合索引，这问题太复杂了，我们在本书8.3节、9.1节以及第10章都会反复提及如何创建组合索引。

1.5 集群因子（CLUSTERING FACTOR）

集群因子的定义和算法

集群因子用于判断索引回表需要消耗的物理I/O次数

索引idx_id的叶子块中有序地存储了索引的键值以及键值对应行所在的ROWID。

集群因子的算法是依次比较相邻ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor值加1

首先我们比较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。

像上面步骤一样，一直这样有序地比较下去，直到比较完索引中最后一个键值。

集群因子的影响因素

集群因子介于表的块数和表行数之间

如果集群因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。

如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大。

集群因子对SQL查询性能的影响

集群因子只会影响索引范围扫描(INDEX RANGE SCAN)以及索引全扫描(INDEX FULL SCAN)

因为只有这两种索引扫描方式会有大量数据回表。

集群因子影响的是索引回表的物理I/O次数

我们假设索引范围扫描返回了1 000行数据，如果buffer cache中没有缓存表的数据块，假设这1000行数据都在同一个数据块中，那么回表需要耗费的物理I/O就只需要一个；假设这1000行数据都在不同的数据块中，那么回表就需要耗费1 000个物理I/O。

如何避免集群因子对SQL查询性能产生影响

当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响

在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。

如果无法避免回表，当我们把表中所有的数据块缓存在buffer cache中，这个时候不管集群因子多大，对SQL查询性能也没有多大影响

因为这时不需要物理I/O，数据块全在内存中访问速度是非常快的。

1.6 表与表之间关系

表与表之间的关系类型

表与表之间存在3种关系：1∶1关系，1∶N关系，N∶N关系

1∶1关系，关联之后返回的结果也是属于1的关系，数据不会重复。

1∶N关系，关联之后返回的结果集属于N的关系。

N∶N关系，关联之后返回的结果集会产生局部范围的笛卡儿积。

如何判断表与表之间的关系

对两表关联列进行汇总统计就能知道两表是什么关系

SQL> select deptno, count(*) from emp group by deptno order by 2 desc;

SQL> select deptno, count(*) from dept group by deptno order by 2 desc;

表与表之间的关系对SQL优化的影响

搞懂表与表之间关系对于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表，因为这时关联之后数据量会翻番。

第2章 统计信息

统计信息的概念和重要性

统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL的执行计划就会跑偏，SQL也就会出现性能问题。

收集统计信息是为了让优化器选择最佳执行计划，以最少的代价(成本)查询出表中的数据。

统计信息的分类

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

本书重点讨论表的统计信息、列的统计信息以及索引的统计信息。

表的统计信息

表的统计信息主要包含表的总行数(num_rows)、表的块数(blocks)以及行平均长度(avg_row_len)

我们可以通过查询数据字典DBA_TABLES获取表的统计信息。

列的统计信息

列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况(直方图)

我们可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。

索引的统计信息

索引的统计信息主要包含索引blevel(索引高度-1)、叶子块的个数(leaf_blocks)以及集群因子(clustering_factor)

我们可以通过数据字典DBA_INDEXES查看索引的统计信息。

创建索引的时候会自动收集索引的统计信息。

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

2.2 统计信息重要参数设置

统计信息重要参数设置

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

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

granularity表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO

对于该选项，我们一般采用AUTO方式，也就是数据库默认方式。

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

我们一般对小于1GB的表进行100%采样，因为表很小，即使100%采样速度也比较快。

有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。因此我们建议对小表100%采样。

我们一般对表大小在1GB～5GB的表采样50%，对大于5GB的表采样30%。

如果表特别大，有几十甚至上百GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。

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

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

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

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

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

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

method_opt => 'for columns object_type size skewonly'表示单独对OBJECT_TYPE列收集直方图，对于其余列，如果之前收集过直方图，现在也收集直方图。

no_invalidate表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS. AUTO_INVALIDATE，表示让Oracle自己决定是否立即失效

我们建议将no_invalidate参数设置为FALSE，立即失效。

degree表示收集统计信息的并行度，默认为NULL

如果表没有设置degree，收集统计信息的时候后就不开并行；如果表设置了degree，收集统计信息的时候就按照表的degree来开并行。

我们建议可以根据当时系统的负载、系统中CPU的个数以及表大小来综合判断设置并行度。

cascade表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息

我们一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。

2.3 检查统计信息是否过期

检查统计信息是否过期

表中有大量数据发生变化时，表的统计信息可能过期

如果表中有大量数据发生变化，优化器还是采用的老的(过期的)统计信息来估算返回行数，可能导致执行计划走偏。

使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO刷新数据库监控信息。

通过DBA_TAB_STATISTICS检查表的统计信息是否过期。

通过ALL_TAB_MODIFICATIONS检查表的统计信息过期原因。

重新收集表的统计信息

如果表的统计信息过期了，需要重新收集表的统计信息。

Oracle判断表统计信息过期的规则

当表中有超过10%的数据发生变化(INSERT，UPDATE，DELETE)，就会引起统计信息过期。

如何快速检查SQL语句中所有的表统计信息是否过期

可以先用explain plan for命令，在plan_table中生成SQL的执行计划，然后使用脚本检查SQL语句中所有的表的统计信息是否过期。

2.4 扩展统计信息

扩展统计信息的概念和作用

扩展统计信息用于优化器估算较为准确的行数(Rows)。

当where条件中有多个谓词过滤条件，但是这些谓词过滤条件彼此是有关系的而不是相互独立的，这时我们可能需要收集扩展统计信息。

扩展统计信息的创建和使用

在Oracle11g之前可以使用动态采样(至少Level 4)来获取较为准确的Rows

使用动态采样Level4采样之后，优化器估算返回33 845行数据，实际返回了32 768行数据，这已经比较精确了。

在Oracle11g以后，我们可以使用扩展统计信息将相关的列组合成一个列

收集完扩展统计信息之后，优化器就能估算出较为准确的Rows。

扩展统计信息只能用于等值查询，不能用于非等值查询。

2.5 动态采样

动态采样的概念和目的

动态采样是Oracle优化器在表没有收集过统计信息时，对表进行采样以优化器估算出较为准确的Rows。

动态采样的级别及其用途

动态采样的级别分为11级，从level 0到level 11，采样的块数和精度逐渐增加

level 0：不启用动态采样。

level 1：随机扫描表中32个数据块，然后评估返回的Rows。

level 2：对没有收集过统计信息的表启用动态采样，采样的块数为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自动决定。

动态采样的启用方法

可以通过设置参数optimizer_dynamic_sampling启用动态采样，也可以通过添加HINT启用动态采样

如果表已经收集过统计信息并且优化器能够准确地估算出返回的Rows，即使添加了动态采样的HINT或者是设置了动态采样的参数为level 3，也不会启用动态采样。

动态采样的适用场景

当系统中有全局临时表，就需要使用动态采样

全局临时表无法收集统计信息，我们建议对全局临时表至少启用level 4进行采样。

当执行计划中表的Rows估算有严重偏差的时候，可以考虑使用动态采样

相关列问题，或者两表关联有多个连接列，关联之后Rows算少，或者是where过滤条件中对列使用了substr、instr、like，又或者是where过滤条件中有非等值过滤，或者group by之后导致Rows估算错误，此时我们可以考虑使用动态采样，同样，我们建议动态采样至少设置为level 4。

在数据仓库系统中，对于复杂的报表SQL，可以考虑启用动态采样

报表SQL一般都有几十行甚至几百行，SQL的过滤条件一般也比较复杂，有大量的AND和OR过滤条件，同时也可能有大量的where子查询过滤条件，SQL最终返回的数据量其实并不多。对于此类SQL，如果SQL执行缓慢，有可能是因为SQL的过滤条件太复杂，从而导致优化器不能估算出较为准确的Rows而产生了错误的执行计划。我们可以考虑启用动态采样level 6观察性能是否有所改善。

2.6 定制统计信息收集策略

确保统计信息准确性

优化器在计算执行计划的成本时依赖于统计信息

如果没有收集统计信息，或者是统计信息过期了，那么优化器就会出现严重偏差，从而导致性能问题。

定制统计信息收集策略

数据库自带的JOB无法完成全库统计信息收集，需要根据实际情况自己定制收集统计信息策略

一些资深的DBA会关闭数据库自带的统计信息收集JOB。

收集SCOTT账户下统计信息过期了或者是从没收集过统计信息的表的统计信息

采样率根据表的段大小做出了相应调整

脚本用于收集SCOTT账户下统计信息过期了或者是从没收集过统计信息的表的统计信息。

对全局临时表进行动态采样或人工设置统计信息

全局临时表无法收集统计信息，可以抓出系统中使用到全局临时表的SQL，然后根据实际情况，对全局临时表进行动态采样，或者是人工对全局临时表设置统计信息

下面脚本抓出系统中使用到全局临时表的SQL。

根据实际情况，对全局临时表进行动态采样，或者是人工对全局临时表设置统计信息(DBMS_STATS.SET_TABLE_STATS)。

第3章 执行计划

SQL执行缓慢的原因

数据库本身原因，比如LATCH争用，或者某些参数设置不合理。

SQL写法有问题

有时候是缺乏索引，可能是因为统计信息过期或者没收集直方图，也可能是优化器本身并不完善或者优化器自身BUG而导致的性能问题。

业务原因，比如要访问一年的数据，然而一年累计有数亿条数据，数据量太大导致SQL性能缓慢。

SQL调优的方法

通过分析等待事件，做出相应处理。

分析SQL写法，分析SQL的执行计划

SQL调优就是通过各种手段和方法使优化器选择最佳执行计划，以最小的资源消耗获取到想要的数据。

3.1 获取执行计划常用方法

使用AUTOTRACE查看执行计划

AUTOTRACE用法包括set autot on、set autot trace、set autot trace exp和set autot trace stat

set autot on：运行SQL并且显示运行结果，执行计划和统计信息。

set autot trace：运行SQL，但不显示运行结果，会显示执行计划和统计信息。

set autot trace exp：运行该命令查询语句不执行，DML语句会执行，只显示执行计划。

set autot trace stat：该命令会运行SQL，只显示统计信息。

AUTOTRACE查看执行计划会输出SQL运行结果，如果SQL要返回大量结果，我们可以使用set autot trace查看执行计划

使用set autot on查看执行计划会输出SQL运行结果。

使用set autot trace查看执行计划，不会输出SQL运行结果。

利用AUTOTRACE查看执行计划会带来一个额外的好处，当SQL执行完毕之后，会在执行计划的末尾显示SQL在运行过程中耗费的一些统计信息

recursive calls表示递归调用的次数。

db block gets表示有多少个块发生变化。

consistent gets表示逻辑读，单位是块。

physical reads表示从磁盘读取了多少个数据块。

redo size表示产生了多少字节的重做日志。

bytes sent via SQL*Net to client表示从数据库服务器发送了多少字节到客户端。

bytes received via SQL*Net from client表示从客户端发送了多少字节到服务端。

SQL*Net roundtrips to/from client表示客户端与数据库服务端交互次数。

sorts (memory)和sorts (disk)分别表示内存排序和磁盘排序的次数。

rows processed表示SQL一共返回多少行数据。

使用EXPLAIN PLAN FOR查看执行计划

explain plan for查看执行计划的用法是：explain plan for SQL语句; select * from table(dbms_xplan.display);

高级(ADVANCED)执行计划比普通执行计划多了Query Block Name /Object Alias和Outline Data

Query Block Name表示查询块名称，Object Alias表示对象别名。

Outline Data表示SQL内部的HINT。

查看带有A-TIME的执行计划

查看带有A-TIME的执行计划的用法是：alter session set statistics_level=all; 或者在SQL语句中添加hint:/ + gather_plan_statistics /

运行完SQL语句，然后执行下面的查询语句就可以获取带有A-TIME的执行计划：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Starts表示这个操作执行的次数，E-Rows表示优化器估算的行数，A-Rows表示真实的行数，A-Time表示累加的总时间，Buffers表示累加的逻辑读，Reads表示累加的物理读

普通执行计划中的Time是假的，而A-Time是真实的。

查看正在执行的SQL的执行计划

需要抓取正在运行的SQL的执行计划，这时我们需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR，然后将其代入下面SQL，就能获取正在运行的SQL的执行计划：select * from table(dbms_xplan.display_cursor('sql_id',child_number));

SQL_ID和CHILD_NUMBER代入以下SQL后，可以获取正在运行的SQL的执行计划

SQL> select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));

3.2 定制执行计划

定制执行计划的介绍

执行计划是树形结构，可以利用树形查询来定制

通过explain plan for select语句，可以查看执行计划。

添加表的段大小到定制执行计划中

表的段大小可以帮助我们判断该步骤是否可能是性能瓶颈

通过select case when (filter_predicates is not null or access_predicates is not null) then '*' else ' ' end || id as "Id", lpad(' ', level) || operation || ' ' || options "Operation", object_name "Name", cardinality as "Rows", b.size_mb "Size_Mb", filter_predicates "Filter", access_predicates "Access" from plan_table a, (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb from dba_segments group by owner, segment_name) b where a.object_owner = b.owner(+) and a.object_name = b.segment_name(+) start with id = 0 connect by prior id = parent_id; 脚本，可以查看表的段大小。

添加表的总字段数以及被访问字段数量到定制执行计划中

被访问的列联合在一起，可以建立组合索引

通过select case when access_predicates is not null or filter_predicates is not null then '' || id else ' ' || id end as "Id", lpad(' ', level) || operation || ' ' || options "Operation", object_name "Name", cardinality "Rows", b.size_mb "Mb", case when object_type like '%TABLE%' then REGEXP_COUNT(a.projection, ']') || '/' || c.column_cnt end as "Column", access_predicates "Access", filter_predicates "Filter", case when object_type like '%TABLE%' then projection end as "Projection" from plan_table a, (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb from dba_segments group by owner, segment_name) b, (select owner, table_name, count() column_cnt from dba_tab_cols group by owner, table_name) c where a.object_owner = b.owner(+) and a.object_name = b.segment_name(+) and a.object_owner = c.owner(+) and a.object_name = c.table_name(+) start with id = 0 connect by prior id = parent_id; 脚本，可以查看表的总字段数以及被访问字段数量。

3.3 怎么通过查看执行计划建立索引

执行计划的组成

执行计划分为两部分，Plan hash value和Predicate Information

Plan hash value主要是表的访问路径以及表的连接方式。

Predicate Information是谓词过滤信息，非常重要。

谓词过滤信息的解读

“*”号表示发生了谓词过滤，或者发生了HASH连接，或者是走了索引

Id=1前面有“”号，它是HASH连接的“”号。

Id=3前面有“*”号，这里表示表emp有谓词过滤。

Id=2前面没有“*”号，那么说明dept表没有谓词过滤条件。

根据执行计划建立索引的策略

如果表很小，那么不需理会；如果表很大，可以使用“select count() from表”，查看有多少行数据，然后通过“select count() from表where *”对应的谓词过滤条件，查看返回多少行数据

如果返回的行数在表总行数的5%以内，我们可以在过滤列上建立索引。

如果已经存在索引，但是没走索引，这时我们要检查统计信息，特别是直方图信息。

如果返回的行数超过表总行数的5%，这个时候我们要查看SQL语句中该表访问了多少列，如果访问的列少，同样可以把这些列组合起来，建立组合索引，建立组合索引的时候，谓词过滤列在前面，连接列在中间，select部分的列在最后。如果访问的列多，这个时候就只能走全表扫描了。

如果索引返回的数据本身很少，即使TABLE ACCESS BY INDEX ROWID前面有“*”号，也可以不用理会

因为索引本身返回的数据少，回表也没有多少次，因此可以不用再创建组合索引。

3.4 运用光标移动大法阅读执行计划

执行计划中的关键元素

Id：观察Id前面是否有“*”号

“*”号表示该步骤是关键步骤。

Operation：表示表的访问路径或者连接方式

常见访问路径会在第4章详细介绍。

表连接方式会在第5章详细介绍。

Name：SQL语句中对象的名字

可以是表名、索引名、视图名、物化视图名或者CBO自动生成的名字。

Rows：CBO根据统计信息以及数学公式计算出来的

Rows是假的，不是真实的，被称作执行计划中返回的基数。

在进行SQL优化的时候，我们经常需要手工计算某个访问路径的真实Rows，然后对比执行计划中的Rows。

光标移动大法阅读执行计划

从上往下看，找到执行计划的入口之后，再往上看

阅读执行计划的时候，我们将光标移动到Id=0 SELECT的S前面，然后按住键盘的向下移动的箭头，向下移动，然后向右移动，然后再向下，再向右……

上下相差一个空格(缩进)就是父子关系，上面的是父亲，下面的是儿子，儿子比父亲先执行

Id=0和Id=1相差一个空格(缩进)，Id=1是Id=0的儿子，Id=1先执行。

Id=2是Id=1的儿子，Id=2先执行。

Id=3是Id=2的儿子，Id=3先执行。

兄弟关系，上面的是兄，下面的是弟，兄优先于弟先执行

Id=7与Id=8对齐，表示Id=7与Id=8是兄弟关系，Id=7先于Id=8执行。

Id=7也跟Id=19、Id=24、Id=34对齐，将光标移动到Id=7前面，向下移动光标，Id=19在Id=18的下面，光标移动大法是不能“穿墙”的，从Id=7移动到Id=19会穿过Id=18，同理Id=24、Id=34也“穿墙”了，因此Id=7只是和Id=8对齐。

执行计划的入口是Id=7，整个执行计划中Id=7最先执行

因为Id=7下面没有儿子，所以执行计划的入口是Id=7。

如何利用光标移动大法优化SQL

先利用光标移动大法找到执行计划入口，检查入口Rows返回的真实行数与CBO估算的行数是否存在较大差异

比如，这里执行计划入口为Id=15，优化器估算返回47行(E-Rows=47)，实际上返回了25行(A-Rows=25)，E-Rows与A-Rows差别不大。

找到执行计划入口之后，我们应该从执行计划入口往上检查，Id=15上面的是Id=14，Id=14上面的是Id=13，这样一直检查到Id=11。Id=11估算返回5行(E-Rows=5)，但是实际上返回了11 248行(A-Rows=11 248)，所以执行计划Id=11这步有问题，由于Id=11 Rows估算错误，它会导致后面整个执行计划出错，应该想办法让CBO估算出较为准确的Rows。

利用光标移动大法找出哪个表与哪个表进行关联的，然后进行优化

例如，Id=29的表它与Id=8对齐，这表示Id=29的表是与一个结果集进行关联的，关联方式为嵌套循环(Id=7，NESTED LOOPS)。

从执行计划中我们可以看到Id=29是嵌套循环的被驱动表，但是没走索引，走的是全表扫描。如果Id=29的表是一个大表，会出现严重的性能问题，因为它会被扫描多次，而且每次扫描的时候都是全表扫描，所以，我们需要在Id=29的表中创建一个索引(连接列上创建索引)。

第4章 访问路径（ACCESS PATH）

访问路径的概念和重要性

访问路径指的就是通过哪种扫描方式获取数据，比如全表扫描、索引扫描或者直接通过ROWID获取数据。

想要成为SQL优化高手，我们就必须深入理解各种访问路径。

4.1 常见访问路径

TABLE ACCESS FULL

TABLE ACCESS FULL表示全表扫描，一般情况下是多块读

在Oracle11g中有个新特征，在对一个大表进行全表扫描的时候，会将表直接读入PGA，绕过buffer cache，这个时候全表扫描的等待事件也是direct path read。

全表扫描不能跨区扫描，因为区与区之间的块物理上不一定是连续的

对一个非分区表进行并行扫描，其实就是同时扫描表中多个不同区，因为区与区之间的块物理上不连续，所以我们不需要担心扫描到相同数据块。

对一个分区表进行并行扫描，有两种方式。如果需要扫描多个分区，那么是以分区为粒度进行并行扫描的，这时如果分区数据不均衡，会严重影响并行扫描速度；如果只需要扫描单个分区，这时是以区为粒度进行并行扫描的。

如果表中有clob字段，clob会单独存放在一个段中，当全表扫描需要访问clob字段时，这时性能会严重下降

我们可以考虑将clob字段拆分为多个varchar2(4000)字段，或者将clob存放在nosql数据库中，例如mongodb。

如果表正在发生大事务，在进行全表扫描的时候，还会从undo读取部分数据

从undo读取数据是单块读，这种情况下全表扫描效率非常低下。

TABLE ACCESS BY USER ROWID

TABLE ACCESS BY USER ROWID表示直接用ROWID获取数据，单块读

该访问路径在Oracle所有的访问路径中性能是最好的。

TABLE ACCESS BY ROWID RANGE

TABLE ACCESS BY ROWID RANGE表示ROWID范围扫描，多块读

因为同一个块里面的ROWID是连续的，同一个EXTENT里面的ROWID也是连续的，所以可以多块读。

TABLE ACCESS BY INDEX ROWID

TABLE ACCESS BY INDEX ROWID表示回表，单块读

我们在第1章中提到过回表，在此不再赘述。

INDEX UNIQUE SCAN

INDEX UNIQUE SCAN表示索引唯一扫描，单块读

对唯一索引或者对主键列进行等值查找，就会走INDEX UNIQUE SCAN。

INDEX RANGE SCAN

INDEX RANGE SCAN表示索引范围扫描，单块读，返回的数据是有序的(默认升序)

对唯一索引或者主键进行范围查找，对非唯一索引进行等值查找，范围查找，就会发生INDEX RANGE SCAN。

INDEX SKIP SCAN

INDEX SKIP SCAN表示索引跳跃扫描，单块读

当组合索引的引导列(第一个列)没有在where条件中，并且组合索引的引导列/前几个列的基数很低，where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描。

INDEX FULL SCAN

INDEX FULL SCAN表示索引全扫描，单块读，返回的数据是有序的(默认升序)

索引全扫描会扫描索引中所有的叶子块(从左往右扫描)，如果索引很大，会产生严重性能问题(因为是单块读)。

INDEX FAST FULL SCAN

INDEX FAST FULL SCAN表示索引快速全扫描，多块读

当需要从表中查询出大量数据但是只需要获取表中部分列的数据的，我们可以利用索引快速全扫描代替全表扫描来提升性能。

INDEX FULL SCAN(MIN/MAX)

INDEX FULL SCAN(MIN/MAX)表示索引最小/最大值扫描、单块读

INDEX FULL SCAN(MIN/MAX)只会访问“索引高度”个索引块，其性能与INDEX UNIQUE SCAN一样，仅次于TABLE ACCESS BY USER ROWID。

MAT_VIEW REWRITE ACCESS FULL

MAT_VIEW REWRITE ACCESS FULL表示物化视图全表扫描、多块读

如果我们开启了查询重写功能，而且SQL查询能够直接从物化视图中获得结果，就会走该访问路径。

4.2 单块读与多块读

单块读与多块读的定义

单块读是从磁盘1次读取1个块到buffer cache。

多块读是从磁盘1次读取多个块到buffer cache。

单块读与多块读的影响因素

数据块是否已经缓存在buffer cache中

如果数据块都已经缓存在buffer cache中，那就不需要物理I/O了，没有物理I/O也就不存在单块读与多块读。

单块读与多块读的性能影响

估算每个访问路径的I/O次数，谁的I/O次数少，谁的性能就好

在估算I/O次数的时候，我们只需要算个大概就可以了，没必要很精确。

4.3 为什么有时候索引扫描比全表扫描更慢

索引扫描比全表扫描更慢的原因

索引扫描返回的数据越多，需要耗费的I/O次数也就越多

假设一个索引叶子块能存储100行数据，那么5万行数据需要扫描500个叶子块(单块读)，也就是需要500次物理I/O，然后有5万条数据需要回表，假设索引的集群因子很小(接近表的块数)，假设每个数据块存储50行数据，那么回表需要耗费1 000次物理I/O(单块读)，也就是说从表中查询5万行数据，如果走索引，一共需要耗费大概1 500次物理I/O。

如果索引的集群因子较大(接近表的总行数)，那么回表要耗费更多的物理I/O，可能是3 000次，而不是1 000次。

返回大量数据应该走全表扫描或者是INDEX FAST FULL SCAN，返回少量数据才走索引扫描

我们一般建议返回表中总行数5%以内的数据，走索引扫描，超过5%走全表扫描。

4.4 DML对于索引维护的影响

DML对索引维护的影响

在OLTP高并发INSERT环境中，递增列索引容易引起索引热点块争用

递增列的索引会不断地往索引“最右边”的叶子块插入最新数据，引起索引热点块争用。

对于递增的主键列索引，我们可以对这个索引进行反转，解决主键列索引的热点块问题。

对于递增的时间列索引，我们不能对这个索引进行反转，因为经常会对时间字段进行范围查找，对时间字段的索引反转之后，索引的集群因子会变得很大，严重影响回表性能。

在OLTP高并发INSERT环境中，非递增列索引一般不会引起索引热点块争用

非递增列的数据都是随机的，在高并发INSERT的时候，会随机地插入到索引的各个叶子块中，因此非递增列索引不会引起索引热点块问题。

如果索引太多会严重影响高并发INSERT的性能。

在高并发的INSERT环境中，表中的索引越多，INSERT速度越慢

当只有1个会话进行INSERT时，表中会有1个块发生变化，有多少个索引，就会有多少个索引叶子块发生变化。

如果有10个会话同时进行INSERT，这时表中最多有10个块会发生变化，索引中最多有100个块会发生变化。

在OLAP环境中，递增列上建立索引对批量INSERT影响不会太大，非递增列索引对批量INSERT影响很大

单进程做批量INSERT，可以在递增列上建立索引，因为是单进程，没有并发，不会有索引热点块争用。

单进程做批量INSERT，不能在非递增列建立索引，因为批量INSERT几乎会更新索引中所有的叶子块。

为了提高批量INSERT的效率，我们可以在INSERT之前先禁止索引，等INSERT完成之后再重建索引。

第5章 表连接方式

表连接方式的重要性

表(结果集)与表(结果集)之间的连接方式非常重要

如果CBO选择了错误的连接方式，本来几秒就能出结果的SQL可能执行一天都执行不完。

想要快速定位超大型SQL性能问题，我们就必须深入理解表连接方式。

表连接方式的类型

在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他表/结果集关联

如果执行计划中出现了Filter，这时可以一次性关联多个表。

5.1 嵌套循环（NESTED LOOPS）

嵌套循环的算法和特点

嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。

嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT：FIRST_ROWS，在两表关联的时候，优化器更倾向于嵌套循环。

嵌套循环的注意事项

嵌套循环驱动表应该返回少量数据

如果驱动表返回了100万行，那么被驱动表就会被扫描100万次，这个时候SQL会执行很久，被驱动表会被误认为热点表，被驱动表连接列的索引也会被误认为热点索引。

嵌套循环被驱动表必须走索引

如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大的时候，SQL 就执行不出结果。

嵌套循环被驱动表走索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN

嵌套循环被驱动表不能走TABLE ACCESS FULL，不能走INDEX FULL SCAN，不能走INDEX SKIP SCAN，也不能走INDEX FAST FULL SCAN。

嵌套循环被驱动表的连接列基数应该很高

如果被驱动表连接列的基数很低，那么被驱动表就不应该走索引，这样一来被驱动表就只能进行全表扫描了，但是被驱动表也不能走全表扫描。

两表关联返回少量数据才能走嵌套循环

前面提到，嵌套循环被驱动表必须走索引，如果两表关联，返回100万行数据，那么被驱动表走索引就会产生100万次回表。回表一般是单块读，这个时候SQL性能极差，所以两表关联返回少量数据才能走嵌套循环。

嵌套循环的PLSQL代码实现

游标cur_emp就相当于驱动表EMP，扫描了一次，一共返回了14条记录。该游标循环了14次，每次循环的时候传值给dept，dept被扫描了14次。

嵌套循环与外连接的关系

当两表使用外连接进行关联，如果执行计划是走嵌套循环，那么这时无法更改驱动表，驱动表会被固定为主表

因为嵌套循环需要传值，主表传值给从表之后，如果发现从表没有关联上，直接显示为NULL即可；但是如果是从表传值给主表，没关联上的数据不能传值给主表，不可能传NULL给主表，所以两表关联是外连接的时候，走嵌套循环驱动表只能固定为主表。

如果外连接中从表有过滤条件，那么此时外连接会变为内连接

因为外连接的从表有过滤条件已经排除了从表与主表没有关联上显示为NULL的情况。

5.2 HASH连接（HASH JOIN）

HASH连接的算法和原理

HASH连接的两表等值关联，返回大量数据，将较小的表选为驱动表

驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table。

当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表，对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。

哈希连接只支持等值连接。

HASH连接在SQL中的使用

在测试账号scott中运行SQL，使用HASH连接

SQL> select / + gather_plan_statistics use_hash(e,d) / e.ename, e.job, d.dname from emp e, dept d where e.deptno = d.deptno。

运行命令获取执行计划，查看HASH连接的驱动表和被驱动表

执行计划中离HASH连接关键字最近的表就是驱动表。

驱动表DEPT只扫描了一次，被驱动表EMP也只扫描了一次。

HASH连接的性能优化

尽量避免书写select * from....语句，将需要的列放在select list中

这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间。

如果无法避免驱动表被溢出到临时表空间，我们可以将临时表空间创建在SSD上或者RAID 0上，加快临时数据的交换速度。

如果驱动表比较大，比如驱动表有4GB，可以开启并行查询至少parallel(4)，将表拆分为至少4份

这样每个并行进程中的work area能够容纳1GB数据，从而避免驱动表被溢出到临时表空间。

如果驱动表非常大，比如有几十GB，这时开启并行HASH也无能为力，这时，应该考虑对表进行拆分。

5.3 排序合并连接（SORT MERGE JOIN）

排序合并连接(SORT MERGE JOIN)概述

排序合并连接主要用于处理两表非等值关联

排序合并连接不能用于instr、substr、like、regexp_like关联。

排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表，然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据

驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中(PGA中的work area)匹配数据。

排序合并连接的执行过程

DEPT作为驱动表，EMP作为被驱动表，DEPT只扫描了一次，EMP也只扫描了一次

DEPT走的是INDEX FULL SCAN，INDEX FULL SCAN返回的数据是有序的，所以DEPT表就不需要排序了。

EMP走的是全表扫描，返回的数据是无序的，所以EMP表在PGA中进行了排序。

排序合并连接的优化策略

如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为HASH连接

如果两表关联是非等值关联，比如>，>=，<，<=，<>，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联。

如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小，最后可以考虑开启并行查询加快SQL执行速度。

5.4 笛卡儿连接（CARTESIAN JOIN）

笛卡儿连接的定义和产生条件

笛卡儿连接是两个表关联没有连接条件的时候产生的

笛卡儿连接会返回两个表的乘积。

在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行，这个时候也可能发生笛卡儿连接

全表扫描返回1行并且无过滤条件，这个可能吗？难道表里面真的就只有1行数据？这不符合常识。那么显然是Id=6的表没有收集统计信息，导致优化器默认地把该表算为1行。

笛卡儿连接的优化方法

首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为满足业务需求而故意不写关联条件。

其次应该检查离笛卡儿连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡儿连接是没有问题的，如果返回行数超过1行，那就需要检查为什么Rows会估算错误，同时要纠正错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡儿连接了。

我们可以使用HINT /+ opt_param('_optimizer_mjc_enabled', 'false') / 禁止笛卡儿连接。

5.5 标量子查询（SCALAR SUBQUERY）

标量子查询的定义和特点

标量子查询是介于select与from之间的子查询

标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。

标量子查询中子查询的表连接列也必须包含在索引中

主表EMP通过连接列(DEPTNO)传值给子查询中的表(DEPT)，执行计划中:B1就表示传值，这个传值过程一共进行了3次，因为主表(EMP)的连接列(DEPTNO)基数等于3。

标量子查询的性能问题

如果主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能

我们建议在工作中，尽量避免使用标量子查询。

如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但是记得要给子查询中表的连接列建立索引。

标量子查询的等价改写

将标量子查询等价改写为外连接，从而使它们可以进行HASH连接

标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。

如果主表的连接列是外键，而子查询的连接列是主键，我们就没必要改写为外连接了，因为外键不可能存NULL值，可以直接改写为内连接

例如本书中所用的标量子查询示例就可以改写为内连接，因为DEPT与EMP有主外键关系。

在Oracle12c中，简单的标量子查询会被优化器等价改写为外连接。

5.6 半连接（SEMI JOIN）

半连接等价改写

in和exists一般情况下都可以进行等价改写

半连接in的写法：SQL> select * from dept where deptno in (select deptno from emp);。

半连接exists的写法：SQL> select * from dept where exists (select null from emp where dept.deptno=emp.deptno);。

in和exists有时候也可以等价地改写为内连接

例如，上面查询语句可以改写为如下写法：SQL> select d.* from dept d, (select deptno from emp group by deptno) e where d.deptno = e.deptno;。

控制半连接执行计划

我们先来查看示例SQL的原始执行计划

执行计划中DEPT与EMP是采用排序合并连接进行关联的。

让DEPT与EMP进行嵌套循环连接，同时让DEPT当驱动表

SQL> select / + use_nl(emp@a,dept) leading(dept) / from dept where deptno in (select / + qb_name(a) */ deptno from emp);。

让DEPT与EMP进行HASH连接，同时让EMP作为驱动表

SQL> select / + use_hash(dept,emp@sel$2) leading(emp@sel$2) / * from dept where deptno in (select deptno from emp);。

读者思考

现有如下SQL，假设a有1000万，b有100行，请问如何优化该SQL？

假设a有100行，b有1000万，请问如何优化该SQL？

假设a有100万，b有1000万，请问如何优化该SQL？

5.7 反连接（ANTI JOIN）

反连接(ANTI JOIN)定义及等价改写

反连接一般就是指的not in和not exists

not in的写法是：select * from dept where deptno not in (select deptno from emp)。

not exists的写法是：select * from dept where not exists (select null from emp where dept.deptno = emp.deptno)。

not in与not exists一般情况下也可以进行等价改写

如果反连接采用not in写法，我们需要在where条件中剔除null。

反连接可以改写为“外连接+子表连接条件is null”。

控制反连接执行计划

DEPT与EMP使用嵌套循环进行关联，不指定驱动表，执行计划变成了FILTER

因为子表EMP的连接列DEPTNO没有排除存在null的情况，所以CBO选择了FILTER。

给子查询加上语句where deptno is not null，执行计划恢复正常

执行计划中DEPT是嵌套循环的驱动表，EMP是嵌套循环的被驱动表。

让DEPT与EMP还进行嵌套循环连接，但是让EMP作为驱动表，执行计划中驱动表还是DEPT

当两表关联是外连接，使用嵌套循环进行关联的时候无法更改驱动表，驱动表会被固定为主表。

让DEPT与EMP进行HASH连接，而且让EMP作为驱动表，执行计划中驱动表还是DEPT

因为两表关联如果是外连接，要改变HASH连接的驱动表必须使用swap_join_inputs。

使用swap_join_inputs来更改HASH连接的驱动表，执行计划中驱动表变为EMP

命令执行后，驱动表由DEPT变为EMP。

5.8 FILTER

FILTER的定义和作用

FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样。

FILTER一般在整个SQL的快要执行完毕的时候执行(Filter的Id一般小于等于3)。

FILTER的执行过程

FILTER下面有两个或者两个以上儿子这种FILTER，驱动表都是固定的(固定为主表)，不可更改

主表(EMP)通过连接列(DEPTNO)传值给子表(DEPT)，:B1就表示传值，主表(EMP)的连接列(DEPTNO)基数为3，所以被驱动表(DEPT)被扫描了3次。

FILTER的优化

在做SQL优化的时候，一般只需要关注FILTER下面有两个或者两个以上儿子这种FILTER

关于如何避免执行计划中产生FILTER以及执行计划中产生了FILTER怎么优化，请参阅本书7.1节。

5.9 IN与EXISTS谁快谁慢

IN与EXISTS的性能比较

IN与EXISTS是半连接，半连接也属于表连接，需要关心两表的大小以及两表之间究竟走什么连接方式

如果执行计划中没有产生FILTER，那么我们可以参考以下思路：in与exists是半连接，半连接也属于表连接，那么既然是表连接，我们需要关心两表的大小以及两表之间究竟走什么连接方式，还要控制两表的连接方式，才能随心所欲优化SQL，而不是去记什么时候in跑得快，什么时候exists跑得快。

如果执行计划中产生了FILTER，大家还需阅读7.1节才能彻底知道答案。

5.10 SQL语句的本质

SQL语句的本质

SQL语句其本质就是表连接(内连接与外连接)，以及表与表之间是几比几关系再加上GROUP BY。

第6章 成本计算

SQL优化时不需要看Cost

Cost是根据统计信息、根据一些数学公式计算出来的，可能会出错

一旦统计信息有误差，数学公式有缺陷，Cost就算错了。

一旦Cost计算错误，执行计划也就错了。

当SQL需要优化的时候，Cost往往是错误的。

6.2 全表扫描成本计算

全表扫描成本计算公式及影响因素

全表扫描成本的计算方式如下：Cost = (#SRds sreadtim + #MRds mreadtim + CPUCycles / cpuspeed) / sreadtime

SRds - number of single block reads 表示单块读次数。

MRds - number of multi block reads 表示多块读次数。

CPUCycles - number of CPU cycles CPU时钟周期数。

sreadtim - single block read time 一次单块读耗时，单位毫秒。

mreadtim - multi block read time 一次多块读耗时，单位毫秒。

cpuspeed - CPU cycles per second 每秒CPU时钟周期数。

如果没有收集过系统统计信息(系统的CPU速度，磁盘I/O速度等)，那么Oracle采用非工作量方式来计算成本。如果收集了系统统计信息，那么Oracle采用工作量统计方式来计算成本

一般我们是不会收集系统的统计信息的。所以默认情况下都是采用非工作量(noworkload)方式来计算成本。

全表扫描成本计算实例分析

在全表扫描成本计算公式中，#SRds=0，因为是全表扫描一般都是多块读，#MRds=表的块数/多块读参数=1000/16，sreadtim=ioseektim+db_block_size/iotfrspeed，单块读耗时=I/O寻道寻址耗时+块大小/I/O传输速度，所以单块读耗时为12毫秒。

多块读耗时= I/O寻道寻址耗时+多块读参数*块大小/I/O传输速度，所以多块读耗时需要42毫秒。

CPUCycles 等于PLAN_TABL/V$SQL_PLAN里面的CPU_COST，所以CPU_COST为7271440。

根据以上信息，我们现在来计算全表扫描成本，Cost = (0 12 + 1000 / 16 42 / 12 + 7271440 / (1683.65129 * 1000) / 12) = 219.109904。

全表扫描成本计算公式的本质含义

全表扫描成本公式的本质含义就是多块读的物理I/O次数乘以多块读耗时与单块读耗时的比值

全表扫描没有单块读，所以#SRds=0，CPU耗费的成本基本上可以忽略不计，所以我们将全表扫描公式变换如下：Cost = (#MRds * mreadtim) / sreadtime。

MRds表示多块读I/O次数，那么现在我们得到一个结论：全表扫描成本公式的本质含义就是多块读的物理I/O次数乘以多块读耗时与单块读耗时的比值。

全表扫描成本计算公式是在Oracle9i(2000年左右)开始引入的，当时的I/O设备性能远远落后于现在的I/O设备(磁盘阵列)，随着SSD的出现，寻道寻址时间已经可以忽略不计，磁盘阵列的性能已经有较大提升，因此认为在现代的I/O设备(磁盘阵列)中，单块读与多块读耗时几乎可以认为是一样的，全表扫描成本计算公式本质含义就是多块读物理I/O次数。

6.3 索引范围扫描成本计算

索引范围扫描成本计算公式

索引扫描成本计算公式为：cost = blevel + celiling(leaf_blocks effective index selectivity) + celiling(clustering_factor effective table selectivity)

blevel表示索引的二元高度，blevel等于索引高度−1。

leaf_blocks表示索引的叶子块个数。

clustering_factor表示索引的集群因子。

effective index selectivity表示索引有效选择性。

effective table selectivity表示表的有效选择性。

索引范围扫描成本计算示例

以Oracle11.2.0.1 Scott账户下的t_indexscan_cost表为例，计算索引扫描的成本

表总行数为72645，object_id最大值为76239，最小值为2，null值个数为0。

执行计划中，T_INDEXSCAN_COST表走的是索引范围扫描，Cost为19。

通过查询dba_indexes表，得到blevel=1，leaf_blocks=161，clustering_factor=1113。

有效选择性=(1000−2)/( 76239−2)。

CBO预估的基数=有效选择性*(总行数−NULL数)=951。

手动计算出来的成本为19，正好与执行计划中的Cost吻合。

索引范围扫描与全表扫描的成本比较

如果回表次数太多，就不应该索引扫描，而应该走全表扫描

clustering_factor * effective table selectivity表示回表的Cost，在示例中，回表的Cost为15，回表的Cost占据整个索引扫描Cost的79%。

索引扫描成本计算公式其本质就是单块读物理I/O次数

全表扫描COST=多块读物理I/O次数*多块读耗时/单块读耗时，索引范围扫描COST=单块读物理I/O次数。

全表扫描COST单块读耗时=多块读物理I/O次数多块读耗时=全表扫描总耗时。

索引范围扫描COST单块读耗时=单块读物理I/O次数单块读耗时=索引扫描总耗时。

优化器何时选择全表扫描，何时选择索引扫描，就是比较走全表扫描的总耗时与走索引扫描的总耗时，哪个快就选哪个。

6.4 SQL优化核心思想

SQL优化核心思想

SQL优化的核心思想是想方设法减少SQL的物理I/O次数

全表扫描的成本其本质含义就是多块读的物理I/O次数。

索引范围扫描的成本其本质含义就是单块读的物理I/O次数。

在判断究竟应该走全表扫描还是索引扫描的时候，往往会根据两种不同的扫描方式所耗费的物理I/O次数来做出选择，哪种扫描方式耗费的物理I/O次数少，就选择哪种扫描方式。

在进行SQL优化的时候，我们也是根据哪种执行计划所耗费的物理I/O次数最少而选择哪种执行计划。

第7章 必须掌握的查询变换

子查询非嵌套的概念和目的

子查询非嵌套是当where子查询中有in、not in、exists、not exists等，CBO会尝试将子查询展开，从而消除FILTER。

子查询非嵌套的目的是消除FILTER，保证执行计划是可控的

一旦驱动表被固定，那么执行计划也就被固定了，对于DBA来说这并不是好事，因为一旦固定的执行计划本身是错误的(低效的)，就会引起性能问题，想要提升性能必须改写SQL语句，但是这时SQL已经上线，无法更改，所以，一定要消除FILTER。

子查询非嵌套的示例和效果

通过将SQL改写为in之后，消除了FILTER

SQL> select ename, deptno from emp where deptno in (select deptno from dept where dname = 'CHICAGO' union select deptno from dept where loc = 'CHICAGO');

在子查询中添加/ + no_unnest /可以产生FILTER

SQL> select ename, deptno from emp where deptno in (select / + no_unnest / deptno from dept where dname = 'CHICAGO' union select deptno from dept where loc = 'CHICAGO');

如何产生和消除FILTER

在子查询中添加固化子查询关键词(union/union all/start with connect by/rownum/cube/rollup)容易产生FILTER

exists中有rownum产生FILTER，exists中有树形查询产生FILTER。

执行计划中的FILTER很少能够通过HINT消除，一般需要通过SQL等价改写来消除

对产生FILTER的SQL添加HINT(UNNEST)来尝试消除FILTER，但执行计划中还是有FILTER。

FILTER的优化

虽然我们一直强调要消除执行计划中的FILTER，本意是要保证执行计划是可控的，并不意味着执行计划产生了FILTER就一定性能差，相反有时候我们还可以用FILTER来优化SQL。

7.2 视图合并

视图合并的定义和原理

视图合并是CBO尝试将内联视图/视图拆开，进行等价的改写。

如果没有发生视图合并，执行计划中，我们可以看到VIEW关键字，而且视图/子查询会作为一个整体。

如果发生了视图合并，那么视图/子查询就会被拆开，而且执行计划中视图/子查询部分就没有VIEW关键字。

视图合并的影响

视图合并可能会打乱视图/子查询中表的原本连接顺序

内联视图中EMP表是与DEPT表关联的，但是执行计划中，EMP表是与SALGRADE先关联的。

视图合并会引起性能问题

单独执行子查询a，速度非常快，单独执行子查询b，速度也非常快，但是把上面两个子查询组合在一起，速度反而很慢。

禁止视图合并的方法

添加HINT:no_merge(子查询别名/视图别名)禁止视图合并

执行计划中有VIEW关键字，而且EMP是与DEPT进行关联的，这说明执行计划中没有发生视图合并。

在子查询里面添加HINT:no_merge禁止视图合并

当视图/子查询中有多个表关联，发生视图合并之后一般会将视图/子查询内部表关联顺序打乱。

遇到类似问题，我们可以添加HINT:no_merge禁止视图合并，也可以让子查询a与子查询b进行HASH连接，当子查询a与子查询b进行HASH连接之后，就不会发生视图合并了。

固化子查询关键字对视图合并的影响

固化子查询的关键字包括union、union all、start with connect by、rownum、cube、rollup

添加了union all之后，子查询被固化，没有发生视图合并。

添加了rownum之后，子查询同样被固化，没有发生视图合并。

7.3 谓词推入

谓词推入的定义和目的

谓词推入是将SQL语句中的谓词过滤条件推入视图中，以提升查询性能

谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据。

谓词推入的必要前提

谓词推入必须要有不能被合并的视图，因为一旦视图被合并了，执行计划中根本找不到视图，这个时候谓词往哪里推呢？

谓词推入的实例分析

在视图中使用union all，where过滤条件是针对视图过滤的，但是从执行计划中我们可以看到，where过滤条件跑到视图中的表中进行过滤了，这就是谓词推入

因为视图中第二个表有rownum，rownum会阻止谓词推入，所以第二个表走的是全表扫描，需要到视图上进行过滤。

常量谓词推入和连接列谓词推入的区别

常量的谓词推入就是谓词是正常的过滤条件，而非连接列

常量的谓词推入对性能的提升都是有益的。

连接列谓词推入是把连接列当作谓词推入到视图中，这种谓词推入我们一般叫作连接列谓词推入，此类谓词推入最容易产生性能问题

连接列谓词推入，被推入的视图一般都作为嵌套循环的被驱动表。

连接列谓词推入性能变差一般是CBO将驱动表Rows计算错误(算少)，导致视图作为嵌套循环被驱动表，然后一直反复被扫描。

也有可能是视图太过复杂，视图本身存在性能问题，这时需要单独优化视图。

谓词推入的注意事项

当视图中有rownum会导致无法谓词推入，所以一般情况下，我们不建议在视图中使用rownum

这是因为当谓词推入之后，rownum的值已经发生改变，已经改变了SQL结果集，任何查询变换必须是在不改变SQL结果集的前提下才能进行。

第8章 调优技巧

查看真实的基数(Rows)

执行计划中的Rows是假的，是CBO根据统计信息和数学公式估算出来的

在看执行计划的时候，一定要注意嵌套循环驱动表的Rows是否估算准确，同时也要注意执行计划的入口Rows是否算错。

如果执行计划的入口Rows估算错误，那执行计划也就不用看了，后面全错

现有执行计划中，Id=4是嵌套循环的驱动表，同时也是执行计划的入口，CBO估算它只返回2 967行数据，但手动计算出的Rows返回了946 432行数据，与执行计划中的2 967行相差巨大，所以本示例中，执行计划是错误的。

8.2 使用UNION代替OR

使用UNION代替OR的原因和方法

当SQL语句中同时有or和子查询时，子查询无法展开，只能走FILTER

SQL> select * from t1 where owner = 'SCOTT' or object_id in (select object_id from t2);

Execution Plan中，Id=1为FILTER。

将SQL改写为union，可以消除FILTER

SQL> select from t1 where owner='SCOTT' union select from t1 where object_id in(select object_id from t2);

Execution Plan中，消除了FILTER。

UNION和OR的性能对比

改写为union后，消除了FILTER，性能更优

改写后的SQL执行计划中，Id=3和Id=6为TABLE ACCESS FULL，性能更好。

如果无法改写SQL，那么SQL就只能走FILTER，这时我们需要在子查询表的连接列(t2.object_id)建立索引

如果不建立索引，FILTER会导致性能下降。

8.3 分页语句优化思路

单表分页优化思路

利用索引已经排序特性，将order by的列包含在索引中

如果分页语句中有排序(order by)，要利用索引已经排序特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。

利用rownum的COUNT STOPKEY特性来优化分页SQL。

过滤条件有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。

排序列有多个列，创建索引的时候，我们要将所有的排序列包含在索引中，并且要注意排序列先后顺序(语句中是怎么排序的，创建索引的时候就对应排序)，而且还要注意列是升序还是降序

如果分页语句中排序列只有一个列，但是是降序显示的，创建索引的时候就没必要降序创建了，我们可以使用HINT: index_desc让索引降序扫描就行。

分页语句中如果出现了SORT ORDER BY，这就意味着分页语句没有利用到索引已经排序的特性，执行计划一般是错误的，这时需要创建正确的索引

先看分页语句有没有ORDER BY，再看执行计划有没有SORT ORDER BY，如果执行计划中有SORT ORDER BY，执行计划一般都是错误的。

如果分页语句中排序的表是分区表，这时我们要看分页语句中是否有跨分区扫描，如果有跨分区扫描，创建索引一般都创建为global索引，如果不创建global索引，就无法保证分页的顺序与索引的顺序一致。如果就只扫描一个分区，这时可以创建local索引

如果分页语句中排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是如果将范围分区改成LIST分区或者HASH分区，这时我们就必须创建global索引，因为LIST分区和HASH分区是无序的。

多表关联分页优化思路

多表关联分页语句，要利用索引已经排序特性、ROWNUM的COUNT STOPKEY特性以及嵌套循环传值特性来优化

嵌套循环是驱动表传值给被驱动表，如果驱动表返回的数据是有序的，那么关联之后的结果集也是有序的，这样就可以消除SORT ORDER BY。

如果有排序，只能对其中一个表进行排序，让参与排序的表作为嵌套循环的驱动表，并且要控制驱动表返回的数据顺序与排序的顺序一致，其余表的连接列要创建好索引

如果两表关联是外连接，我们只能选择主表的列作为排序列。

语句中不能有distinct、group by、max、min、avg、union、union all，执行计划中不能出现SORT ORDER BY

当分页语句中有这些关键字，我们需要等表关联完或者数据都跑完之后再来分页，这样性能很差。

8.4 使用分析函数优化自连接

使用分析函数优化自连接

利用分析函数对自连接进行等价改写，使EMP只访问一次

分析函数的写法如下：select ename, deptno, sal from (select a.*, max(sal) over(partition by deptno) max_sal from emp a) where sal = max_sal。

使用分析函数改写之后，减少了表扫描次数，EMP表越大，性能提升越明显。

8.5 超大表与超小表关联优化方法

超大表与超小表关联优化方法

超大表与超小表关联时，小表应作为HASH JOIN的驱动表，大表作为被驱动表

驱动表会被放到PGA中，因为驱动表a只有30MB，PGA能够完全容纳下驱动表。

因为被驱动表b特别大，想要加快SQL查询速度，必须开启并行查询。

在进行并行HASH连接时，可以将小表(驱动表)广播到所有的查询进程，然后对大表进行并行随机扫描

假设对以上SQL启用6个并行进程对a表的并行广播，对b表进行随机并行扫描(每部分记为b1,b2,b3,b4,b5,b6)。

其实就相当于将以上SQL内部等价改写为下面SQL。

如何让a表进行广播

需要添加hint：pq_distribute(驱动表none，broadcast)

如果小表进行了广播，执行计划Operation会出现PX SEND BROADCAST关键字，PQ Distrib会出现BROADCAST关键字。

注意：如果是两个大表关联，千万不能让大表广播。

8.6 超大表与超大表关联优化方法

超大表与超大表关联优化方法

当表a和表b都很大时，使用并行HASH连接可以避免on-disk hash join

开启并行查询加快查询速度。

驱动表a有4GB，需要放入PGA中，因为PGA中work area不能超过2G，所以PGA不能完全容纳下驱动表，这时有部分数据会溢出到磁盘(TEMP)进行on-disk hash join。

假设对上面SQL启用6个并行查询，a表会根据连接列进行HASH运算然后拆分为6份，记为a1，a2，a3，a4，a5，a6，b表也会根据连接列进行HASH运算然后拆分为6份，记为b1，b2，b3，b4，b5，b6。

并行HASH HASH关联的执行计划中，会出现PX SEND HASH关键字，PQ Distrib会出现HASH关键字。

对于超级大表(几十GB)与超级大表(几十GB)关联的性能问题，可以通过人工实现并行HASH HASH来解决

创建新表p1，在表a的结构上添加一个字段HASH_VALUE，同时根据HASH_VALUE进行LIST分区。

创建新表p2，在表b的结构上添加一个字段HASH_VALUE，同时根据HASH_VALUE进行LIST分区。

将a表的数据迁移到新表p1中。

将b表的数据迁移到新表p2中。

下面SQL就是并行 HASH HASH关联的人工实现。

此方法运用了ora_hash函数。Oracle中的HASH分区就是利用的ora_hash函数。

8.7 LIKE语句优化方法

LIKE语句优化方法

因为需要对字符串两边进行模糊匹配，而索引根块和分支块存储的是前缀数据，所以上面SQL查询无法走索引

如果强制走索引，会走INDEX FULL SCAN，性能不如全表扫描。

我们可以创建一个表当索引用，用来代替INDEX FAST FULL SCAN不能回表的情况

创建一个表当索引用，用来代替INDEX FAST FULL SCAN不能回表的情况。

改写SQL之后，需要让index_t与t走嵌套循环，同时让index_t作为嵌套循环驱动表，这样就达到了让index_t充当索引的目的。

采用这个方法还需要对index_t进行数据同步，我们可以将index_t创建为物化视图，刷新方式采用on commit刷新

当t表越大，性能提升就越明显。

8.8 DBLINK优化

DBLINK优化概述

DBLINK优化可以提升查询性能

将本地小表传输到远端关联，再返回结果只需6秒，相比将大表传输到本地，在性能上有巨大提升。

强制a表走索引之后，这时我们只需将索引过滤之后的数据传输到本地，而无需将a表所有数据传到本地，性能得到极大提升，SQL耗时不到1秒。

我们可以设置arraysize减少网络交互次数，从而减少网络开销。

在本地创建一个带有dblink的物化视图，将远端表a的数据刷新到本地，然后再进行关联。

如果SQL语句中有多个dblink源，最好在本地针对每个dblink源建立带有dblink的物化视图。

有时候会使用dblink对数据进行迁移，如果要迁移的数据量很大，我们可以使用批量游标进行迁移。

DBLINK优化实践

将本地小表传输到远端关联

默认情况下，会将远端表a的数据传输到本地，然后再进行关联。

我们可以将本地表b传输到远端，在远端进行关联，然后再将结果集传回本地。

强制a表走索引

因为b表只有100行数据，a表有1 800万行数据，两表关联之后返回2.5万行数据，我们可以让a与b走嵌套循环，b作为驱动表，a作为被驱动表，而且走连接索引。

设置arraysize减少网络交互次数

当需要将本地表传输到远端关联、再将关联结果传输到本地的时候，我们可以设置arraysize优化SQL。

在本地创建一个带有dblink的物化视图

如果远端表a很大，本地表b也很大，两表关联返回数据量多，这时既不能将远端表a传到本地，也不能将本地表b传到远端，因为无论采用哪种方法，SQL都很慢。我们可以在本地创建一个带有dblink的物化视图，将远端表a的数据刷新到本地，然后再进行关联。

使用批量游标进行数据迁移

如果要迁移的数据量很大，我们可以使用批量游标进行迁移。以下是使用批量游标迁移数据的示例(将a@dblink的数据迁移到b)。

8.9 对表进行ROWID切片

对非分区表进行ROWID切片以加快UPDATE、DELETE速度

Oracle提供了一个内置函数DBMS_ROWID.ROWID_CREATE()用于生成ROWID

对于一个非分区表，一个表就是一个段(Segment)，段是由多个区组成，每个区里面的块物理上是连续的。

可以根据数据字典DBA_EXTENTS，DBA_OBJECTS关联，然后再利用生成ROWID的内置函数人工生成ROWID

例如，我们对SCOTT账户下TEST表按照每个Extent进行ROWID切片。

使用存储过程简化ROWID切片操作

存储过程需要访问数据字典，需要单独授权查询数据字典权限

grant select on dba_extents to scott;

grant select on dba_objects to scott;

编写存储过程P_ROWID，用于按指定范围进行ROWID切片

P_ROWID接受两个参数，RANGE表示要切分的份数，ID表示当前处理的份数。

在存储过程中，使用游标CUR_ROWID获取每个Extent的ROWID范围，然后生成SQL语句执行删除操作。

最后，通过循环依次执行P_ROWID存储过程，实现按ROWID切片的目的。

8.10 SQL三段分拆法

SQL三段拆分方法

将SQL拆分为三段，快速判断SQL在写法上是否容易产生性能问题

select与from之间最好不要有标量子查询，也不要有自定义函数。

from与where之间要关注大表，留意子查询和视图，注意表与表之间是内连接还是外连接。

where后面需要特别注意子查询，要能判断各种子查询写法是否可以展开(unnest)，同时也要注意where过滤条件，尽量不要在where过滤列上使用函数。

三段拆分法的应用

在工作中，我们要养成利用SQL三段分拆方法的习惯，这样能大大提升SQL优化的速度。

9.1 组合索引优化案例

组合索引优化案例概述

佛山某沙发厂ERP系统出现大量read by other session等待问题

前台用户卡了一天，数据库版本是Oracle11gR2。

SQL执行计划分析

SQL走的是ILMCU这个列的索引，导致产生大量的单块读

SQL执行缓慢，某些耐不住性子的用户可能会多次点击或刷新前台。

因为做的是SF10的业务，前台操作人员可能多达几十位。

正是因为有很多人在同时运行该SQL，而且该SQL跑得很慢，又是单块读，所以就发生了多个进程需要同时读取同一个块的情况，这就是产生read by other session的原因。

数据分布情况分析

ILMCU列的数据分布极不均衡，其他列的数据分布相对均匀

从2 510 970条数据中查询1 424 246条数据还走索引，这明显大错特错。

组合索引的创建

创建组合索引idx_F4111_docdctilmcufrto，系统中的read other session等待陆续消失

create index idx_F4111_docdctilmcufrto on F4111(ILDOC,ILDCT,ILMCU,ILFRTO) online nologging;

优化器选择索引的原因及建议

优化器选择了走ILMCU列的索引，因为表统计信息有问题，该列很可能没有收集直方图

请注意观察执行计划中的Id=3，Rows=1，优化器认为走ILMCU列的索引只返回一行数据。

优化器未能准确知道ILMCU列的数据分布，导致执行计划走了错误的索引

如果优化器能够准确地知道ILMCU列的数据分布，那么执行计划也不会走该列的索引而会走其他列的索引(如果存在索引)，或者走全表扫描。

建议重视表的统计信息，掌握索引知识，理解透了，就能解决80%左右的关于OLTP的SQL性能问题

如果数据库系统不是OLTP系统，而是ERP系统，或者是OLAP中的报表系统、ETL系统等，只吃透索引没太大帮助，必须精通阅读执行计划、SQL、各种SQL等价改写，熟悉分区，同时熟悉系统业务，这样才能游刃有余地进行SQL优化。

9.2 直方图优化案例

直方图优化案例背景

SQL执行了两个小时还未完成，需要检查

ETL开发人员发来邮件说有个long running job，执行了两小时左右还未完成。

SQL语句简单，但执行计划错误

SQL语句是create table .....as select .....。

执行计划显示走的是嵌套循环，但驱动表返回的真实行数与估算的行数有较大偏差。

执行计划错误的原因分析

CBO估算Rows时，直接以表总行数/列基数进行估算

Id=4过滤条件是RELTV_CURR_QTY = 1，但RELTV_CURR_QTY列基数太低，而且没有收集直方图。

统计信息中表总行数与真实行数接近，但RELTV_CURR_QTY列基数低且无直方图

统计信息中表总行数有2 160 000行数据，与真实的行数(2 137 706)十分接近。

RELTV_CURR_QTY列的基数等于728，没有直方图(HISTOGRAM =NONE)。

直方图优化过程

对RELTV_CURR_QTY列收集直方图

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXX', tabname => 'OPT_REF_UOM_TEMP_SDIM', estimate_percent => 100, method_opt => 'for columns RELTV_CURR_QTY size skewonly', degree => DBMS_STATS.AUTO_DEGREE, cascade=>TRUE); END; / PL/SQL procedure successfully completed.

重新查看执行计划，发现已自动走了HASH连接

执行计划显示走的是HASH连接，这才是正确的执行计划。

优化建议

对于ETL，两表关联处理大量数据应该走HASH连接。

需要分组汇总的SQL一般也是处理大量数据，基于此该SQL也应该走HASH连接。

9.3 NL被驱动表不能走INDEX SKIP SCAN

嵌套循环被驱动表不能走INDEX SKIP SCAN的原因

嵌套循环会传值，从驱动表传值给被驱动表，传值相当于过滤条件

有过滤条件但是走了INDEX SKIP SCAN，很有可能是被驱动表连接列没包含在索引中，或者连接列在索引中放错了位置。

被驱动表连接列未包含在索引中的问题

被驱动表索引RMS_LOCALNET_POS_PUI中竟然没有包含连接列。

解决执行计划错误的方案

将连接列和过滤列组合起来创建组合索引，从而解决该SQL性能问题

我们需要创建一个包含连接列和过滤列的组合索引，以便优化执行计划。

9.4 优化SQL需要注意表与表之间关系

SQL优化思路

首先要看SQL写法，利用SQL三段分拆方法，先观察SQL语句。

其次要检查表与表之间关系，确定关联方式

如果被驱动表与驱动表是几十万比1的关系，这时就不能走嵌套循环了，只能走HASH连接。

SQL执行不出结果的原因分析

被驱动表tb_trans与tab是几十万比1的关系，导致性能问题

因为被驱动表tb_trans与tab是几十万比1的关系，这时就不能走嵌套循环了，只能走HASH连接。

优化SQL的方法

使用HINT：use_hash(aa,bb)优化SQL

最终该SQL可以在1小时左右执行完毕。如果开启并行查询可以更快。

9.5 INDEX FAST FULL SCAN优化案例

SQL执行计划分析

SQL是一个自关联，采用HASH ANTI JOIN进行关联

表SYS_ACTIVATION_SDK_IOS有14G，索引SYS_ACTIVATION_SDK_IOS_IDX1有2.5G。

T1返回11 799行数据，T2返回1 251 009行数据

因为IDFA基数很低，所以不能让T1与T2走嵌套循环，只能走HASH连接。

索引FAST FULL SCAN优化

INDEX RANGE SCAN是单块读，不适合返回大量数据，应该让其走INDEX FAST FULL SCAN

T1走的是INDEX RANGE SCAN，返回了11 799行数据，T2走的也是INDEX RANGE SCAN，返回了1 251 009行数据。

更正了执行计划之后，该SQL最慢可以在1分钟内执行完毕

当buffer cache缓存了索引SYS_ACTIVATION_SDK_IOS_IDX1，SQL就能在几秒至十几秒执行完毕。

如果buffer cache没有缓存SYS_ACTIVATION_SDK_IOS_IDX1，执行计划中Id=5走的是INDEX RANGE SCAN，导致大量单块读，所以会执行40分钟左右。

9.6 分页语句优化案例

分页语句优化案例

检查分页语句是否符合分页语句编写规范

分页语句只能对一个表的列进行排序。

对排序列来自的表创建索引

过滤条件是等值访问，可以把过滤条件放在前面，排序列放在后面。

强制分页语句走嵌套循环，同时让排序列所在的表作为嵌套循环驱动表

嵌套循环被驱动表不能走全表扫描，必须走索引。

创建被驱动表的索引

被驱动表走了全表扫描，需要创建索引。

9.7 ORDER BY取别名列优化案例

ORDER BY取别名列优化案例

SQL类似分页语句，先查看分页语句是否符合分页编码规范

该SQL完全符合分页语句编码规范。

该SQL排序列是crtd_dt，执行计划中走的也是crtd_dt列的索引

表nt_pso_arch_info是LIST分区表，分区列是local_area_id，从执行计划中(Id=4)看到只扫描了一个分区。

SQL语句中order by的列crtd_dt在select中进行了to_char格式化，格式化之后取了别名，但是别名居然与列名一样

正是因为别名与列名一样，才导致无法消除SORT ORDER BY。

更改别名之后，消除了SORT ORDER BY，从而达到了优化目的

如果不更改别名，order by crtd_dt就相当于order by别名，也就是order by to_char　(npai.CRTD_DT, 'yyyy-mm-dd hH24:mi:ss')，而索引中记录的是date类型，现在排序变成了按照char类型排序，如果不更改别名执行计划就无法消除SORT ORDER BY。

9.8 半连接反向驱动主表案例一

SQL优化案例一

SQL执行了30分钟还没出结果，子查询可以秒出结果

子查询返回一个人开房的房号记录，共返回63行。

该SQL就是查与某人相同的房间号的他人的记录。

LY_T_CHREC表有两亿条记录，没有过滤条件，IN子查询过滤之后返回63行数据

LY_T_CHREC应该存放的是开房记录数据，GCODE列基数应该比较高。

优化策略

小表与大表关联，如果大表连接列基数比较高，可以走嵌套循环，让小表驱动大表，大表走连接列的索引

这里小表就是IN子查询，大表就是主表，我们让IN子查询作为NL驱动表。

优化后的SQL

select gcode, name, idcode, address, noroom, etime from zhxx_lgy.LY_T_CHREC t where gcode in (select gcode from zhxx_lgy.LY_T_CHREC t where name = '张三' and bdate = '19941109')

最终该SQL可以秒出。

9.9 半连接反向驱动主表案例二

SQL执行计划问题

执行计划中居然是'PLAN_TABLE' is old version，无法看到谓词信息

这需要重建PLAN_TABLE。

SQL语句分析

SQL语句中，select到from之间没有标量子查询，没有自定义函数，from后面有5个表关联，where条件中只有一个in(子查询)，没有其他过滤条件

SQL语句中用到的表大小如图9-8所示。

SQL语句中有4个表都是大表，只有一个表T_BD_PERIOD是小表，在SQL语句中与T_GL_VOUCHER外关联，是外连接的从表。

子查询优化

in(子查询)与表T_GL_VOUCHER进行关联，T_GL_VOUCHER同时也是外连接的主表，如果in(子查询)能过滤掉T_GL_VOUCHER大量数据，那么可以让T_GL_VOUCHER作为嵌套循环驱动表，一直与后面的表NL下去，这样或许能优化SQL

如果in(子查询)不能过滤掉大量数据，那么SQL就无法优化，最终只能全走HASH。

询问in(子查询)返回多少行，运行多久，得到反馈：in(子查询)返回16 880条数据，耗时23秒。

with as子句优化

将in子查询改写为with as，让with as子句作为嵌套循环驱动表

需要注意的是with as子句中必须要添加HINT:/ + materialize /，同时主表与子查询关联列必须有索引。

with as子句添加了/ + materialize /会生成一个临时表，这时，就将复杂的in子查询简单化了，之后优化器会将with as子句展开(unnesting)，将子查询展开一般是子查询与主表进行HASH连接，或者是子查询作为嵌套循环驱动表与主表进行关联，一般不会是主表作为嵌套循环驱动表，因为主表作为嵌套循环驱动表可以直接走Filter，不用展开。

优化器发现with as子句数据量较小，而主表较大，而且主表连接列有索引，于是自动让with as子句固化的结果作为了嵌套循环驱动表。

9.10 连接列数据分布不均衡导致性能问题

SQL查询性能问题原因排查

通过逐步排查，发现select中同时count(distinct a.user_name)，count(distinct a.invest_id)导致SQL查询缓慢

先执行select count(*)，可以秒出。

再执行select count(a.user_name)，也可以秒出。

继续执行select count(a.user_name), count(a.invest_id)，依然可以秒出。

最后执行select count(distinct a.user_name), count(distinct a.invest_id)，以上SQL还是可以秒出。

SQL查询性能问题解决方案

将SQL进行改写，通过添加HINT：/ + materialize / 生成临时表，再对临时表进行count(distinct...),count(distinct)

改写后的SQL执行计划中，Id=3是HASH JOIN RIGHT SEMI，说明CBO没有将半连接等价改写成内连接。

SQL查询性能问题根本原因分析

CBO内部将半连接改写为内连接导致SQL查询缓慢

缓慢SQL执行计划中Id=2是HASH JOIN，而秒出SQL的执行计划中Id=3是HASH JOIN RIGHT SEMI。

SEMI是半连接特有关键字，缓慢SQL的执行计划中没有SEMI关键字，这说明CBO将半连接等价改写成了内连接。

内连接表关联之后数据量可能会翻番，该SQL查询缓慢是被改成内连接导致，现在我们有充分理由怀疑内连接关联之后返回的数据量太大。

两个表的连接列中有部分数据倾斜非常严重

如果让两表进行内连接，026h2这条数据关联之后返回结果应该是160162*10984，现在我们终于发现该SQL执行缓慢的根本原因，是因为两个表的连接列中有部分数据倾斜非常严重。

SQL查询性能问题优化方法

使用rownum优化SQL，rownum可以让一个查询被当成一个整体

with t1 as (select owner, object_name from a where owner in (select owner from b) and rownum > 0) select count(distinct owner), count(distinct object_name) from t1;

对子查询先去重，将子查询变成1的关系，这样也能优化SQL

select count(distinct owner), count(distinct object_name) from a where owner in (select owner from b group by owner);

9.11 Filter优化经典案例

SQL执行计划问题及优化思路

SQL执行计划中，Id=1是Filter，而且Filter对应的谓词信息有EXISTS(子查询:B1)，这说明该Filter类似嵌套循环

Id=2和Id=8是Id=1的儿子，因为这里的Filter类似嵌套循环，Id=2就相当于NL驱动表，Id=8相当于NL被驱动表，Id=8是全表扫描过滤后的数据，所以Id=8可以看作全表扫描。

由于Id=10并没有走索引，Id=2估算返回905行数据，一般情况下Rows会算少，这里就暂且认为Id=2返回905行数据，那么Id=8会被扫描905次，也就是说DW_BO_ORDER这个200万行大表会被扫描905次，而且每次都是全表扫描，这就是为什么SQL会执行5个多小时。

优化的思路是让大表只被扫描一次，或者减少大表每次被扫描的体积

最优的解决方案是，想办法让Id=2和Id=8走HASH连接消除Filter，这样就只需要扫描1次大表。

如果不能减少扫描的次数，但是能减少扫描的体积，也能优化SQL。我们可以在大表上建立一个组合索引，这样就能避免大表每次全表扫描，从而达到减少扫描体积的目的。

使用with as子句优化SQL

使用with as子句将大表要被访问的字段查询出来，一共4个字段，然后过滤掉不需要的数据，添加HINT:MATERIALIZE将with as子句查询结果固化为临时表

假设200万行的大表DW_BO_ORDER有占用2GB存储空间，表有40个字段，通过with as子句改写之后，只需要存储4个字段数据，这时只需200MB存储空间。

而且with as子句中还有过滤条件，又可以过滤掉一部分数据，这时with as子句可能就只需要几十兆存储空间。

最终SQL可以在12秒左右跑完，一共返回4.9万行数据。

9.12 树形查询优化案例

树形查询优化案例

通过分析执行计划，发现SQL的性能瓶颈在Id=7和Id=8这两步

执行计划中，Id=7的谓词过滤中有绑定变量:B1，但是SQL语句中并没有绑定变量。

执行计划中Id=4也有绑定变量，但是Id=4的绑定变量与Id=8是成对出现，Id=5的绑定变量与Id=7也是成对出现，对于成对出现的绑定变量情况，关注有表对应的Id即可，这里有表对应的Id就是7和8。

创建索引来优化SQL

create index idx_a on CABLE_1(tdl_dn,tdl_operation);

create index idx_b on RESOURCE_FACING_SERVICE1_1(tdl_dn,tdl_operation);

使用with as子句改写，然后将子查询生成临时表来进行优化，但是with as子句改写优化的性能没有创建索引优化的性能高

因为走索引可以进行INDEX RANGE SCAN，而且不需要回表，而with as子句需要对临时表进行全表扫描。

9.13 本地索引优化案例

本地索引优化案例

SQL在RAC环境中执行，时快时慢，最快时1秒，最慢是3秒

SQL语句如下：SELECT /+INDEX(TMS,IDX1_TB_EVT_DLV_W)/ TMS.MAIL_NUM, TMS.DLV_BUREAU_ORG_CODE AS DLVORGCODE, RO.ORG_SNAME AS DLVORGNAME, TMS.DLV_PSEG_CODE AS DLVSECTIONCODE, TMS.DLV_PSEG_NAME AS DLVSECTIONNAME, TO_CHAR(TMS.DLV_DATE, 'YYYY-MM-DD HH24:MI:SS') AS RECTIME, TMS.DLV_STAFF_CODE AS HANDOVERUSERCODE, TU2.REALNAME AS HANDOVERUSERNAME, DECODE(TMS.DLV_STS_CODE, 'I', '妥投', 'H', '未妥投', TMS.DLV_STS_CODE) AS DLV_STS_CODE, CASE WHEN TMS.MAIL_NUM LIKE 'EC%' THEN '代收' WHEN TMS.MAIL_NUM LIKE 'ED%CW' THEN '代收' WHEN TMS.MAIL_NUM LIKE 'FJ%' THEN '代收' WHEN TMS.MAIL_NUM LIKE 'GC%' THEN '代收' ELSE '非代收' END MAIL_NUM_TYPE FROM TB_EVT_DLV_W TMS LEFT JOIN RES_ORG RO ON TMS.DLV_BUREAU_ORG_CODE = RO.ORG_CODE LEFT JOIN TB_USER TU2 ON TU2.DELVORGCODE = TMS.DLV_BUREAU_ORG_CODE AND TU2.USERNAME = TMS.DLV_STAFF_CODE WHERE NOT EXISTS (SELECT /+INDEX(TDW,IDX1_TB_MAIL_SECTION_STORE)/ MAIL_NUM FROM TB_MAIL_SECTION_STORE TDW WHERE TDW.MAIL_NUM = TMS.MAIL_NUM AND TDW.DLVORGCODE = TMS.DLV_BUREAU_ORG_CODE AND TDW.DLVORGCODE = '35000133' AND TDW.RECTIME >= TO_DATE('2012-11-01 00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2012-11-08 15:15', 'YYYY-MM-DD HH24:MI:SS') >= TDW.RECTIME AND rownum = 1) AND TMS.DLV_BUREAU_ORG_CODE = '35000133' AND TMS.DLV_DATE >= TO_DATE('2012-11-01 00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2012-11-08 15:15', 'YYYY-MM-DD HH24:MI:SS') >= TMS.DLV_DATE AND ('' IS NULL OR TMS.DLV_STAFF_CODE = '') AND ('' IS NULL OR TU2.REALNAME LIKE '%%') AND TMS.REC_AVAIL_FLAG = '1';。

执行计划显示，SQL的性能问题出在Id=7和Id=6上

Id=7走的是索引范围扫描，过滤条件是"TMS"."DLV_BUREAU_ORG_CODE"='35000133'，Id=6是Id=7的索引回表操作，注意Id=6，Operation中出现了GLOBAL关键字，这说明TB_EVT_DLV_W是一个分区表，而且Id=7中的索引是全局索引。

Id=6中出现了时间过滤，一般的分区表都是根据时间字段进行分区的。

将Id=7的全局(global)索引改成本地(local)索引后，SQL多次执行都能稳定在1秒内

建立本地索引之后，Id=6就不会再去进行时间过滤了。

相比扫描全局索引，扫描本地索引只需要到对应的索引分区中进行扫描，扫描的叶子块数量也大大减少。

本地索引的创建原则

如果过滤条件中有分区字段，一般都创建本地索引

如果过滤条件中没有分区字段，一般都创建global索引，如果这时创建成local索引，会扫描所有的索引分区，分区数量越多，性能下降越明显。

假设表按月分区，一个月大概几百万行数据，但是只查询几小时的数据，数据也就几千行，这时我们需要将分区列包含在索引中，这样的索引就是有前缀的本地索引。

假设表按月分区，但是查询经常按月查询或者跨月查询，这时我们就不需要将分区列包含在索引中，这样创建的本地索引就是非前缀的本地索引。

9.14 标量子查询优化案例

标量子查询优化案例一

原始SQL有7个标量子查询，通过等价改写为外连接，大大提升SQL性能

原始SQL的逻辑读为103 172，改写后的SQL的逻辑读下降到400。

标量子查询优化案例二

通过将标量子查询中的半连接改写为内连接，消除了Filter，提高了SQL性能

改写后的SQL的逻辑读下降到400。

本次优化通过将标量子查询的半连接等价改写为内连接，使Id=3和Id=4的两个表使用索引，从而提高了SQL性能。

9.15 关联更新优化案例

关联更新优化案例背景

UPDATE语句执行了30分钟还没执行完毕

SQL语句如下：UPDATE OPT_ACCT_FDIM A SET ACCT_SKID = (SELECT ACCT_SKID FROM OPT_ACCT_FDIM_BKP B WHERE A.ACCT_ID = B.ACCT_ID)。

OPT_ACCT_FDIM有226 474行数据，OPT_ACCT_FDIM_BKP有227 817行数据。

UPDATE后面跟子查询类似嵌套循环，它的算法与标量子查询，Filter一模一样。

OPT_ACCT_FDIM_BKP是通过CTAS创建的备份表，用来备份OPT_ACCT_FDIM表的数据。

嵌套循环被驱动表应该走索引，但是OPT_ACCT_FDIM_BKP是通过CTAS创建的，仅仅用于备份，该表上面没有任何索引，这就是说OPT_ACCT_FDIM_BKP要被扫描20多万次，而且每次都是全表扫描。

关联更新优化方案

创建一个索引(ACCT_ID，ACCT_SKID)从而避免OPT_ACCT_FDIM_BKP每次被全表扫描

虽然这种方法能优化该SQL，但是此时索引会被扫描20多万次。

采用存储过程并且利用ROWID对关联更新进行优化

存储过程代码如下。

将关联更新改写成存储过程，利用ROWID进行更新只需要1分22秒就可执行完毕。

关联更新优化原理

对ROWID进行排序是为了保证在更新表的时候，被更新的块尽量不被刷出buffer cache，从而减少物理I/O

假设要被更新的表有20GB，数据库的buffer cache只有10GB，这时buffer cache不能完全容纳要被更新的表，有部分块会被挤压出buffer cache。

这时如果不对ROWID进行排序，被更新的块有可能会被反复读入buffer cache，然后挤压出buffer cache，然后重复读入、挤压，此时会引发大量的I/O读写操作。

假设一个块存储200行数据，最极端的情况就是每个块要被读入/写出到磁盘200次，这样读取的表就不是20GB，而是(200×20)GB。

如果对ROWID进行排序，这样就能保证一个块只需被读入buffer cache一次，这样就避免了大量的I/O读写操作。

关联更新优化实验验证

如果不对ROWID排序，块有可能被反复扫描

我们先创建两个表，分别取名为a，b，为了模拟实际情况，将a，b中数据随机打乱存储。

查看返回结果如下。

从SQL查询结果中我们可以看到，返回的数据是无序的。

关联更新优化方案对比

MERGE INTO可以自由控制走嵌套循环或者走HASH连接，而且MERGE INTO可以开启并行DML、并行查询，而采用PLSQL更新不能开启并行，所以MERGE INTO在速度上有优势

PLSQL更新可以批量提交，对UNDO占用小，而MERGE INTO要等提交的时候才会释放UNDO。

采用PLSQL更新不需要担心进程突然断开连接，MERGE INTO更新如果进程断开连接会导致UNDO很难释放。

所以，如果追求更新速度且被更新的表并发量很小，可以考虑采用MERGE INTO，如果追求安全、平稳，可以采用PLSQL更新。

9.16 外连接有OR关联条件只能走NL

外连接有OR关联条件只能走NL

当两表用外连接进行关联，关联条件中有OR关联条件，那么这时只能走嵌套循环，而且驱动表固定为主表

即使通过HINT：USE_HASH也无法修改执行计划。

如果主表数据量很大，那么这时就会出现严重性能问题。

我们可以将外连接的OR关联/过滤条件放到查询中，用case when进行过滤，从而让SQL可以走HASH连接

利用case when改写外连接OR连接条件有个限制：从表只能是1的关系，不能是n的关系，从表要展示多少个列，就要写多少个case when。

如果主表属于1的关系，从表属于n的关系，我们就不能用case when进行等价改写。

如果两表是n∶n关系，这时就无法对SQL进行改写了，在日常工作中一般也遇不到n∶n关系。

9.17 把你脑袋当CBO

SQL优化前的准备

检查SQL写法，确保SQL没有语法问题。

检查执行计划，找出问题所在

如果执行计划中有明显值得怀疑的地方，可以直接检查值得怀疑之处。

执行计划的入口是Id=8，Id=8是索引范围扫描，通过Id=7回表。

根据执行计划优化SQL

让A4作为驱动表leading(a4)，与A3使用嵌套循环use_nl(a4,a3)方式进行关联

A4过滤后只返回6行数据，A3是小表，所以让A4作为驱动表。

让A5与A2进行HASH连接

A5过滤后有6万行数据，所以我们让A5与A2进行HASH连接。

优化后的执行计划

最终该SQL只需0.188秒就能出结果，逻辑读从最开始的2 539 920下降到14 770

执行计划中，Id=3是HASH JOIN SEMI，Id=12是TABLE ACCESS BY INDEX ROWID，Id=13是INDEX RANGE SCAN。

如何快速优化SQL

不看执行计划，直接根据SQL写法找到SQL语句中返回数据量最小的表作为驱动表

然后看它与谁进行关联，根据关联返回的数据量判断走NL还是HASH，然后一直这样进行下去，直到SQL语句中所有表都关联完毕。

长期采用此方法进行锻炼，久而久之，你自己的脑袋就是CBO

当具备一定优化理论知识之后，我们可以不看执行计划，直接根据SQL写法找到SQL语句中返回数据量最小的表作为驱动表，然后看它与谁进行关联，根据关联返回的数据量判断走NL还是HASH，然后一直这样进行下去，直到SQL语句中所有表都关联完毕。

9.18 扩展统计信息优化案例

SQL优化案例背景

SQL语句由Obiee报表工具自动生成，看起来有些凌乱

SQL语句执行了30分钟还不出结果。

SQL写法没有任何问题，执行计划也比较长

使用自己编写的脚本抓出该SQL要用到的表信息。

定位性能瓶颈

OPT_PRMTN_PROD_FLTR_LKP表走的是全表扫描，有3 000万行数据，1.5GB

表OPT_PRMTN_PROD_FLTR_LKP大小应该不止1.5GB。

OPTPRMTN_PROD_FLTR LKP做了嵌套循环(Id=7)的被驱动表，而且没有走索引

创建索引花了33分钟。

优化策略及效果

根据过滤条件创建索引让NL被驱动表走索引

创建索引之后，Obiee报表能在4分钟内执行完所有数据。

收集扩展统计信息纠正Rows估算的误差

SQL能在1秒左右执行完毕。

优化后的效益

客户可以在掏打火机、烟还没点燃之前就能打开报表了

罗老师优化完报表之后，幽默地说了这句话。

9.19 使用LISGAGG分析函数优化WMSYS.WM_CONCAT

SQL性能问题定位

通过分步执行，定位到SQL性能问题是由wmsys.wm_concat导致

SQL语句中有个with as子句，对其单独执行，发现要执行两分钟左右。

将with as子句中wmsys.wm_concat(distinct(su.user_name)) usernames去掉，只保留wmsys.wm_concat(distinct (sg.gp_name)) groupnames，执行计划中的db block gets来自于Clob。

因为Listagg不支持distinct，所以我们需要先去重，再采用Listagg，Listagg改写的SQL能在1秒执行完毕，而采用wmsys.wm_concat需要58秒。

使用Listagg分析函数优化WMSYS.WM_CONCAT

对with as子句进行等价改写，利用分析函数Listagg代替wmsys.wm_concat

先去重，再使用Listagg，Listagg改写的SQL如下。

最终的with as子句如下。

用改写后的with as子句替换原始SQL中的with as子句，最终SQL能在两秒左右执行完毕。

9.20 INSTR非等值关联优化案例

INSTR非等值关联优化案例

需求是查找事实表中URL字段包含了维度表中URL的记录，然后进行汇总统计

创建事实表T_FACT，包含msisdn和url两个字段。

插入测试数据，直到表中一共有128万条数据。

创建维度表T_DIM，包含code和url两个字段。

创建汇总统计表T_RESULT，包含msisdn，code，url和cnt四个字段。

SQL语句中关联条件是instr，只能走嵌套循环，不能走HASH连接，也不能走排序合并连接

执行计划中事实表是驱动表，维度表是被驱动表，被驱动表反而不能走索引。

INSTR，LIKE，REGEXP_LIKE会匹配所有数据，走索引的访问路径只能是INDEX FULL SCAN，而INDEX FULL SCAN是单块读，全表扫描是多块读。

如果从执行计划方向入手，我们无法优化SQL，我们再来看一下原始SQL语句

SQL语句中有GROUP BY(汇总)，事实表与维度表一般都是N:1关系，因为SQL语句中有汇总，我们可以先对事实表进行汇总，去掉重复数据，然后再与维度表关联。

事实表原始数据为128万行，我们对事实表提前汇总，数据从128万行减少到1万行。

对数据进行提前汇总之后，被驱动表T_DIM只需要循环1万次，而之前需要循环128万次，性能得到极大提升

改写后的SQL，对数据进行提前汇总。

提前汇总之后，被驱动表T_DIM只需要循环1万次，而之前需要循环128万次，性能得到极大提升。

如果想要最大程度优化INSTR，LIKE，REGEXP_LIKE等非等值关联，我们只能从业务角度入手，设法从业务本身、数据本身着手，使其进行等值连接，从而可以走HASH连接

如果业务手段无法优化，除了上面讲到的提前汇总数据，我们还可以开启并行查询(并行广播)，从而优化SQL。

如果不想开启并行查询，我们可以对表进行拆分(类似并行广播)，人工模拟并行查询，从而优化SQL。

9.21 REGEXP_LIKE非等值关联优化案例

存储过程执行缓慢的原因及优化方法

存储过程执行缓慢的原因是使用了嵌套循环和REGEXP_LIKE进行关联，导致全表扫描

嵌套循环就是一个LOOP循环，LOOP套LOOP相当于笛卡儿积。

使用regexp_like(c_no_data.no, c_data.expression)进行关联，无法走HASH连接，也无法走排序合并连接，两表只能走嵌套循环并且被驱动表无法走索引。

优化方法是采用ROWID批量更新和ROWID切片实现并行

创建一个临时表用于存储43记录。

创建另外一个临时表，用于存储要被更新的表的ROWID以及过滤条件字段。

利用ROWID切片变相实现并行。

优化后的PLSQL代码及效果

优化后的PLSQL代码能在4小时左右执行完，开启8个并行进程后能在29分钟左右执行完

单个进程能在4小时左右执行完，如果开启8个并行进程，那应该能在30分钟左右执行完。

在8个窗口中同时运行以上PLSQL代码，最终我们能在29分钟左右执行完所有存储过程。

优化后的PLSQL代码经典之处在于ROWID切片实现并行，同时考虑到了数据分布对并行的影响，其次还使用了ROWID关联更新技巧。

9.22 ROW LEVEL SECURITY优化案例

Row Level Security(RLS)引发的SQL性能问题

权限较低的账户打开报表非常缓慢，报表运行了15分钟还没响应。

使用权限最高的账户，报表可以在16秒内执行完毕

缓慢的SQL在where条件中多了实现Row Level Security功能的代码。

RLS代码分析

RLS代码中有一个in子查询，in子查询中有union关键字

in子查询可以展开(unnest)，exists子查询中有union是不可以展开的。

如果where条件中的子查询不能展开(no_unnest)，执行计划中会出现Filter

Filter一般是在SQL的最后阶段执行。

如果where条件中的子查询展开了，子查询会与主表提前关联。

优化RLS引发的SQL性能问题

在in子查询中添加HINT：NO_UNNEST，让子查询不展开

子查询不展开，执行计划中就会出现Filter，但是Filter是在最后进行过滤。

子查询不展开就不会干扰原始的(跑得快的)执行计划，只是在跑得快的执行计划的最后一步添加Filter过滤而已。

将in改写为exists，这时优化器会自动走Filter

千万不要因为我们将in改写为exists、exists执行快就说exists性能比in高。

9.23 子查询非嵌套优化案例一

SQL执行计划分析

SQL最终只返回少量数据，应该走嵌套循环，而不是走HASH连接

SQL中有13个GROUP BY字段，一般而言，GROUP BY字段越少，去重能力越强；GROUP BY字段越多，去重能力越弱。

SQL中有两处外链接，d.company_id = e.company_id(+)，d.applicant_id = f.customer_id(+)，如果走嵌套循环，外连接无法更改驱动表。如果走HASH连接，外连接可以更改驱动表。

两个大表t_policy_fee tpf与t_contract_master tcm都是走的全表扫描，这显然不对

它们应该走索引，或者作为嵌套循环的被驱动表。

优化SQL的策略

添加HINT：NO_UNNEST，让子查询不展开，从而不去干扰执行计划

因为SQL最终只返回少量数据，我们判断执行计划应该走嵌套循环。

走嵌套循环首先要确定好谁做驱动表。根据上面的分析e,f首先被排除掉做驱动表的可能性，因为它们是外连接的从表，tpf，tcm也被排除掉作为驱动表的可能性，因为它们是大表。现在只剩下tpc，c和g可以作为驱动表候选，tpc，c,g都是与tpf关联的，只需要看谁最小，谁就作为驱动表。

将in改成exists，因为子查询中有固化子查询关键字，这时SQL不能展开，会自动走Filter

in可以控制走Filter或者不走，in执行计划可控，而exists执行计划不可控。

对于in子查询，我们一定要搞清楚in子查询返回多少数据，究竟能起到多大过滤作用。如果in子查询能过滤掉主表大量数据，这时我们一定要让in子查询展开并且作为NL驱动表反向驱动主表，主表作为NL被驱动表，走连接列索引。如果in子查询不能过滤掉主表大量数据，这时要检查in子查询返回数据量多少，如果返回数据量很少，in子查询即使不展开，走Filter也不大会影响SQL性能。如果in子查询返回数据量很多，但是并不能过滤掉主表大量数据，这时一定要让in子查询展开并且与主表走HASH连接。

9.24 子查询非嵌套优化案例二

SQL执行不出结果的原因

SQL走了Filter，Id=3返回大量数据，导致Id=18、Id=21、Id=29被多次扫描

因为SQL写成了exists，才导致走了Filter。

使用in代替exists优化SQL

用in代替exists之后，两个in子查询因为进行了Subquery Unnesting，消除了Filter

两个子查询都走的是HASH连接，这样两个in子查询都只会被扫描一次。

in和exists的性能对比

如果where子查询中没有固化子查询关键字，不管写成in还是写成exists，效率都是一样的

CBO始终能将子查询展开(unnest)。

如果where子查询中有固化子查询关键字，这时我们最好用in而不是exists

因为in可以控制子查询是否展开，而exists无法展开。

至于where子查询是展开性能好还是不展开性能好，我们要具体情况具体分析。

9.25 烂用外连接导致无法谓词推入

SQL执行计划问题

SQL最终返回一行数据，应该全走嵌套循环，但是关联到视图view_xj_ct_fukuan的时候居然走的是HASH连接

视图view_xj_ct_fukuan的别名是ctv，ctv分别与cth和ctb进行了关联。

如果能让cth与ctb关联之后得到的结果集通过ctv的连接列传值给ctv，通过连接列将数据将数据推入到视图中，这样就可以让视图走嵌套循环了，这种方式就是连接列谓词推入，但是执行计划并没有这样做。

视图中的外连接问题

视图ctv.pk_ct_manage字段来自于ctb，而ctb与a是外连接，而且ctb是从表，并不是主表

正是因为ctb是视图中外连接的从表，而且视图ctv也是外连接的从表，所以导致cth不能通过连接列pk_ct_manage将谓词推入到ctv.pk_ct_manage中，从而导致走了HASH连接。

优化SQL的方法

将视图中的外连接改成内连接，就可以将谓词推入到ctv中，从而走嵌套循环

通过反复分析SQL写法，我们确认可以将视图中的外连接改写为内连接。

将视图里面的外连接改成内连接之后，我们就可以将谓词推入到视图中了。

如果不改视图中的外连接，将SQL语句中的外连接改成内连接也可以将谓词推入视图。

SQL优化的反思

在系统上线之前，SQL审核是多么重要

我们甚至怀疑是不是开发人员只会left join，或者不管写什么SQL，一直left join，这太可怕了。

9.26 谓词推入优化案例

谓词推入优化案例

原始SQL执行时间长，需要优化

原始SQL要执行1个多小时。

视图AP_UNAPPLY_PREPAYS_V被当作了嵌套循环的被驱动表

执行计划中Id=5看到，该SQL发生了连接列谓词推入。

使用HINT：USE_HASH(A,B)，让两表走HASH连接，从而避免视图被多次反复扫描

添加HINT之后，SQL能在1秒返回结果。

也可以调整隐含参数，关闭连接列谓词推入

ALTER SESSION SET "_push_join_predicate" = FALSE;

如果发生了连接列谓词推入，一定要检查执行计划中是否走了谓词被推入的表的连接列索引。

9.27 使用CARDINALITY优化SQL

SQL执行不出结果的原因分析

SQL访问的是同一个表TB_INDEXS，表在SQL语句中被访问了4次

SQL语句中的in子查询，优化器评估Id=8返回1行数据，但是实际上Id=8要返回2万行数据。

UPDATE_TIME字段被设计为了NUMBER类型，而实际上UPDATE_TIME应该是DATE类型

UPDATE_TIME因为字段类型设计错误，本来应该估算返回21天的数据，但是因为UPDATE_TIME设计为了NUMBER类型，导致优化器在估算返回行数的时候不是利用DATE类型估算返回行数，而是利用NUMBER类型估算返回行数。

使用CARDINALITY优化SQL

添加HINT：CARDINALITY，指定执行计划入口(子查询)返回2万行数据

/ + cardinality(@a 20000) cardinality(@b 20000) /((v.yvalue 300)/(u.xvalue 50)), u.xtime。

通过指定执行计划入口(子查询)返回2万行数据，纠正了之前错误的执行计划

SQL最终执行了15秒就返回了所有的结果。

优化SQL的其他方法

启用动态采样Level 4及以上(最好别超过6)，让优化器能较为准确地评估出子查询返回的Rows

如果不知道动态采样怎么优化SQL，我们可以直接使用HINT，比如USE_HASH等。

从业务上入手，从表设计上入手，从SQL写法上入手

当然了，最佳的优化方法应该是直接从业务上入手，从表设计上入手，从SQL写法上入手，而不是退而求其次从执行计划入手。

9.28 利用等待事件优化SQL

SQL执行计划问题

SQL执行计划中存在大量的嵌套循环，导致执行速度慢

执行计划中Id=16和Id=27优化器评估只返回1行数据，怀疑OPT_ACCT_DIM和OPT_ACTVY_BUOM_GTIN_COST_TFADS这两个表统计信息有问题。

对OPT_ACCT_DIM和OPT_ACTVY_BUOM_GTIN_COST_TFADS这两个表收集完统计信息之后，执行计划变为正确

纠正完执行计划之后，笔者将SQL放在后台运行了大概两小时，发现SQL还没执行完毕。

SQL等待事件分析

SQL的等待事件为direct path write temp，表示当前SQL正在进行排序或者正在进行HASH连接，但是因为PGA不够大，不能完全容纳需要排序或者需要HASH的数据，导致有部分数据被写入temp表空间

为了追查究竟是因为排序还是因为HASH而引发的direct path write temp等待，使用以下脚本查看临时段数据类型。

从SQL查询中我们看到，临时段数据类型为HASH，耗费了3 304MB的temp表空间，这表示SQL是因为HASH连接引发的direct path write temp等待。

每次I/O只写了7个块，怀疑是PGA中work area不够导致出现了该问题

PGA在自动管理的情况下，单个PGA进程的work area不能超过1GB。

将PGA的work area设置为接近2GB之后，重新运行了SQL并且监控等待事件，发现P3可以达到64，相比之前一次只能写入7个块速度提升了9倍。

优化SQL性能

将PGA的work area设置为接近2GB之后，整个SQL的性能应该提升了18倍

最后，经过对比测试，手动设置work area的SQL只需要56分钟左右就能执行完毕。

而自动work area管理的SQL还在一直等待direct path write temp，估计该SQL如果不手动设置work area可能跑一天一夜都跑不完。

该问题是11.1.0.7版本在HPUX平台下的一个bug，Oracle开发补丁需要一定的时间，在此期间，使用本书给出的方法临时解决了项目中遇到的问题，确保项目不会因此延期。

第10章 全自动SQL审核

全自动SQL审核脚本分享

分享一些常用的全自动SQL审核脚本

在实际工作中，我们可以对脚本进行适当修改，以便适应自己的数据库环境，从而提升工作效率。

10.1 抓出外键没创建索引的表

抓出外键没创建索引的表

通过dba_constraints和dba_ind_columns视图获取外键列和索引列信息

cons视图包含外键列的信息，idx视图包含索引列的信息。

通过dba_cons_columns视图筛选出外键列没有创建索引的表

在dba_cons_columns视图中，筛选出(owner,table_name, constraint_name)在cons视图中的记录，且(owner,table_name, column_name)不在idx视图中的记录。

10.2 抓出需要收集直方图的列

抓出需要收集直方图的列的脚本

脚本依赖统计信息，用于抓出表总行数大于5万行、列选择性低于5%并且列出现在where条件中的表以及列信息

通过dba_tab_col_statistics和dba_tables表关联，筛选出满足条件的表和列。

再通过sys.col_usage$、sys.obj$、sys.col$和sys.user$表关联，进一步筛选出属于Scott账户的表和列。

脚本的应用实例

在Scott账户中，test表总行数大于5万行，owner列选择性小于5%，而且出现在where条件中，通过脚本可以将其抓出

通过脚本查询，可以得到表名、列名、总行数、基数和选择性等信息。

10.3 抓出必须创建索引的列

抓出必须创建索引的列的脚本

脚本依赖统计信息，用于抓出Scott账户下表总行数大于5万行、列选择性超过20%、列出现在where条件中并且没有创建索引的列

通过dba_tab_col_statistics和dba_tables表关联，获取表的总行数、列的选择性和基数。

通过sys.col_usage$、sys.obj$、sys.col$和sys.user$表关联，获取列的使用情况。

过滤出选择性大于20%、总行数大于5万行、没有创建索引的列。

脚本运行结果示例

在Scott账户中，test表总行数大于5万行，有两个列出现在where条件中，选择性大于20%，而且没有创建索引，通过脚本将其抓出

test表总行数为73020，object_id列的选择性为100%，object_name列的选择性为56.15%。

10.4 抓出SELECT * 的SQL

SELECT * SQL的问题

SELECT * SQL走索引无法避免回表，走HASH连接的时候会将驱动表所有的列放入PGA中，浪费PGA内存。

抓出SELECT * 的SQL的脚本

通过查询v$sql, v$sql_plan, dba_segments和dba_tab_cols表，结合正则表达式，抓出SELECT * 的SQL。

运行SELECT * SQL的示例

在Scott账户中运行select * from t where object_id<1000;的SQL，并使用脚本将其抓出。

脚本运行结果

输出了SQL_ID, SQL_TEXT, OWNER, TABLE_NAME, COLUMN_CNT, SIZE_MB等信息，其中COLUMN_CNT等于表的字段总个数，SIZE_MB表示表的大小。

10.5 抓出有标量子查询的SQL

抓出有标量子查询的SQL

通过分析执行计划，抓出标量子查询语句

同一个SQL语句，执行计划中如果有两个或者两个以上的depth=1的执行计划就表示SQL中出现了标量子查询。

利用脚本抓出Scott账户下在SQL*Plus中运行过的标量子查询语句

select sql_id, sql_text, module from v$sql where parsing_schema_name = 'SCOTT' and module = 'SQLPlus' AND sql_id in (select sql_id from (select sql_id, count() over(partition by sql_id, child_number, depth) cnt from V$SQL_PLAN where depth = 1 and (object_owner = 'SCOTT' or object_owner is null)) where cnt >= 2);。

10.6 抓出带有自定义函数的SQL

抓出带有自定义函数的SQL

通过V$SQL和DBA_OBJECTS表关联，找出调用自定义函数的SQL

在V$SQL表中查找SQL_ID、SQL_TEXT和MODULE。

在DBA_OBJECTS表中查找调用自定义函数的对象名。

过滤出符合条件的SQL语句

过滤出调用自定义函数的SQL语句。

过滤出执行时间大于0的SQL语句。

过滤出仅包含SELECT语句的SQL语句。

过滤出解析模式为Scott的SQL语句。

10.7 抓出表被多次反复调用SQL

抓出表被多次反复调用SQL的方法

通过v$sql和v$sql_plan表联查，找出对同一个表进行多次扫描的SQL

在v$sql_plan表中，筛选出object_owner为'SCOTT'的SQL，并按sql_id和child_number分组，统计每个SQL语句对每个表的访问次数。

在v$sql表中，筛选出与上述统计结果中SQL_ID和child_number相同的记录，获取SQL文本和对应的schema。

将上述结果组合，得到对同一个表进行多次扫描的SQL。

10.8 抓出走了FILTER的SQL

抓出走了FILTER的SQL

通过脚本抓出where子查询没能unnest的SQL

查询v$sql表，筛选出parsing_schema_name为'SCOTT'的SQL。

再查询v$sql_plan表，找出operation为'FILTER'且filter_predicates包含'IS NOT NULL%'的SQL。

最后将结果减去object_owner为'SYS'的SQL，得到最终结果。

10.9 抓出返回行数较多的嵌套循环SQL

抓出返回行数较多的嵌套循环SQL

通过v$sql和v$sql_plan表抓出执行计划中嵌套循环的SQL

在v$sql表中筛选出parsing_schema_name为'SCOTT'，executions大于0，rows_processed / executions大于10000的SQL。

在v$sql_plan表中筛选出operation像'%NESTED LOOPS%'，且id小于等于5的SQL。

脚本中的SQL语句返回行数较多，执行计划在最后几步走了嵌套循环，判定该执行计划中的嵌套循环是有问题的，应该走HASH连接。

10.10 抓出NL被驱动表走了全表扫描的SQL

抓出NL被驱动表走了全表扫描的SQL

使用脚本抓出嵌套循环被驱动表走了全表扫描的SQL，并根据表大小降序显示

脚本不依赖统计信息。

嵌套循环的被驱动表应该走索引。

脚本通过v$sql_plan表获取SQL信息，通过dba_segments表获取表大小信息。

在Scott账户中运行强制两表走嵌套循环，强制两表走全表扫描的SQL，并通过脚本抓出

SQL语句为：select /+ use_nl(a,b) full(a) full(b) / * from a, b where a.object_id = b.object_id。

通过脚本，我们抓出了这条SQL，并显示了其SQL_TEXT、SQL_ID、OBJECT_NAME和MB。

10.11 抓出走了TABLE ACCESS FULL的SQL

抓出走了TABLE ACCESS FULL的SQL

通过v$sql、v$sql_plan、dba_segments和dba_tab_cols视图获取相关信息

v$sql视图包含SQL的详细信息。

v$sql_plan视图包含SQL的执行计划信息。

dba_segments视图包含表的大小信息。

dba_tab_cols视图包含表的字段数信息。

过滤出走了TABLE ACCESS FULL的SQL，并显示访问了表多少个字段，表一共有多少个字段以及表段大小

过滤条件包括：a.sql_id = b.sql_id，a.child_number = b.child_number，b.object_owner = c.owner，b.object_name = c.segment_name，b.object_owner = d.owner，b.object_name = d.table_name，c.owner = 'SCOTT'，b.operation = 'TABLE ACCESS'，b.options = 'FULL'。

显示字段包括：sql_id, sql_text, table_name, column_cnt, size_mb, filter。

脚本的实际应用

过滤出大于1GB的表、过滤出表总字段数大于20的表、过滤出访问了超过10个字段的表等

在实际工作中，我们可以对脚本适当修改，以满足不同的需求。

10.12 抓出走了INDEX FULL SCAN的SQL

抓出走了INDEX FULL SCAN的SQL

使用脚本抓出走了INDEX FULL SCAN的SQL，并根据索引段大小降序显示

select c.sql_text, c.sql_id, b.object_name, d.mb。

from v$sql_plan b, v$sql c, (select owner, segment_name, sum(bytes / 1024 / 1024) mb from dba_segments group by owner, segment_name) d。

where b.sql_id = c.sql_id and b.child_number = c.child_number and b.object_owner = 'SCOTT' and b.operation = 'INDEX' and b.options = 'FULL SCAN' and b.object_owner = d.owner and b.object_name = d.segment_name。

order by 4 desc。

在Scott账户中运行特定SQL，使执行计划自动走INDEX FULL SCAN

select * from t where object_id is not null order by object_id。

对脚本进行适当修改，例如过滤出大于10GB的索引。

10.13 抓出走了INDEX SKIP SCAN的SQL

抓出走了INDEX SKIP SCAN的SQL

通过v$sql_plan、v$sql和dba_segments表抓出走了INDEX SKIP SCAN的SQL

在Scott账户中创建测试表t_skip。

在owner字段上创建一个索引idx_owner_id。

对表t_skip收集统计信息。

执行SQL select * from t_skip where object_id < 100，查看执行计划。

通过脚本抓出走了INDEX SKIP SCAN的SQL。

10.14 抓出索引被哪些SQL引用

抓出索引被哪些SQL引用

通过v$sql和v$sql_plan表关联查询，找出使用指定索引的SQL

select a.sql_text, a.sql_id, b.object_owner, b.object_name, b.object_type。

from v$sql a, v$sql_plan b。

where a.sql_id = b.sql_id and a.child_number = b.child_number and object_owner = 'SCOTT' and object_type like '%INDEX%';。

order by 3,4,5;。

10.15 抓出走了笛卡儿积的SQL

抓出走了笛卡儿积的SQL

通过v$sql_plan表和dba_segments表抓出走了笛卡儿积的SQL

在Scott账户中运行如下SQL：select * from a,b。

利用脚本将其抓出：select c.sql_text, a.sql_id, b.object_name, a.filter_predicates filter, a.access_predicates predicate, d.mb from v$sql_plan a, (select * from (select sql_id, child_number, object_owner, object_name, parent_id, operation, options, row_number() over(partition by sql_id, child_number, parent_id order by id) rn from v$sql_plan) where rn = 1) b, v$sql c, (select owner, segment_name, sum(bytes / 1024 / 1024) mb from dba_segments group by owner, segment_name) d where b.sql_id = c.sql_id and b.child_number = c.child_number and b.object_owner = 'SCOTT' and a.sql_id = b.sql_id and a.child_number = b.child_number and a.operation = 'MERGE JOIN' and a.id = b.parent_id and a.options = 'CARTESIAN' and b.object_owner = d.owner and b.object_name = d.segment_name order by 4 desc;。

10.16 抓出走了错误的排序合并连接的SQL

排序合并连接的使用场景和优化建议

排序合并连接一般用于非等值关联，如果两表是等值关联，建议使用HASH连接代替

HASH连接只需要将驱动表放入PGA中，而排序合并连接要么是将两个表放入PGA中，要么是将一个表放入PGA中、另外一个表走INDEX FULL SCAN，然后回表。

如果两表是等值关联并且两表比较大，这时应该走HASH连接而不是排序合并连接。

抓出走了错误的排序合并连接的SQL的脚本

通过v$sql_plan表找到两表等值关联但是走了排序合并连接的SQL，同时显示离MERGE JOIN关键字较远的表的段大小

脚本中，a.sql_id = b.sql_id and a.child_number = b.child_number确保我们比较的是同一个SQL的不同执行计划。

b.operation = 'SORT' and b.options = 'JOIN'筛选出排序合并连接的操作。

b.access_predicates like '%"="%'确保我们找到的是等值关联的SQL。

a.parent_id = b.id and a.object_owner = 'SCOTT'确保我们只查看Scott账户中的SQL。

a.object_owner = d.owner and a.object_name = d.segment_name将SQL和对应的表关联起来。

order by 4 desc按照离MERGE JOIN关键字较远的表的段大小降序排列。

示例SQL的执行计划和优化建议

SQL: select / + use_merge(e,d) / * from emp e, dept d where e.deptno = d.deptno

执行计划中，使用了排序合并连接。

优化建议：使用HASH连接代替排序合并连接。

10.17 抓出LOOP套LOOP的PSQL

避免使用LOOP套LOOP的原因

双层循环，最内层循环类似笛卡儿积，执行次数过多

假设外层循环返回1 000行数据，内层循环返回1 000行数据，那么内层循环里面的代码就会执行1000*1000次。

抓出LOOP套LOOP的PLSQL脚本

通过DBA_SOURCE视图获取包含LOOP的SQL语句

使用`upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'`过滤出包含LOOP的SQL语句。

通过两个SELECT语句查找嵌套的LOOP

使用`a.rn+1=b.rn`查找嵌套的LOOP。

使用`a.owner=b.owner and a.name=b.name and a.type=b.type`确保查找的是同一个对象的LOOP。

示例：创建并抓出LOOP套LOOP的存储过程

创建一个包含LOOP套LOOP的存储过程

存储过程名为p_99，包含两个FOR循环。

使用脚本抓出包含LOOP套LOOP的存储过程

脚本运行结果中，OWNER、NAME和TYPE列显示了存储过程的详细信息。

10.18 抓出走了低选择性索引的SQL

抓出走了低选择性索引的SQL

依赖统计信息，检查SQL语句中是否有其他过滤条件

如果一个索引选择性很低，说明列数据分布不均衡。

当SQL走了数据分布不均衡列的索引，很容易走错执行计划。

如果有其他过滤条件，可以考虑建立组合索引，将选择性高的列作为引导列。

如果没有其他过滤条件，应该检查列是否有收集直方图。

使用脚本抓出走了低选择性索引的SQL

脚本从v$sql_plan、dba_ind_columns、v$sql、dba_tab_col_statistics和dba_tables表中获取数据。

脚本筛选出索引选择性低于10%的SQL。

10.19 抓出可以创建组合索引的SQL（回表再过滤选择性高的列）

抓出可以创建组合索引的SQL(回表再过滤选择性高的列)

通过分析执行计划，找出回表再过滤且过滤字段选择性较高的SQL

在执行计划中，如果发生了回表再过滤并且过滤字段的选择性比较高，我们可以将过滤字段包含在索引中避免回表再过滤，从而减少回表次数，提升查询性能。

利用脚本抓出回表再过滤选择性较高的列

脚本查询条件包括：a.sql_id = b.sql_id，a.child_number = b.child_number，b.object_owner = c.owner，b.object_name = c.segment_name，e.owner = f.owner，e.table_name = f.table_name，b.object_owner = f.owner，b.object_name = f.table_name，instr(b.filter_predicates, e.column_name) > 0，(e.num_distinct / f.num_rows) > 0.1，c.owner = 'SCOTT'，b.operation = 'TABLE ACCESS'，b.options = 'BY INDEX ROWID'，e.owner = 'SCOTT'。

脚本排序条件为：按size_mb降序排列。

10.20 抓出可以创建组合索引的SQL（回表只访问少数字段）

抓出可以创建组合索引的SQL(回表只访问少数字段)

通过分析v$sql、v$sql_plan、dba_segments和dba_tab_cols视图，找出回表只访问少数字段的SQL

筛选条件包括：a.sql_id = b.sql_id，a.child_number = b.child_number，b.object_owner = c.owner，b.object_name = c.segment_name，b.object_owner = d.owner，b.object_name = d.table_name，c.owner = 'SCOTT'，b.operation = 'TABLE ACCESS'，b.options = 'BY INDEX ROWID'，REGEXP_COUNT(b.projection, ']')/d.column_cnt<0.25。

排序条件包括：按size_mb降序排列。

示例SQL：select object_name from t2 where object_id<1000，只访问了1个字段，可以利用脚本将其抓出

SQL_ID为bzyprvnc41ak8，SQL_TEXT为select object_name from t2 where object_id<1000，TABLE_NAME为T2，COLUMN_CNT为1/15，SIZE_MB为9，FILTER为access("OBJECT_ID"<1000)。