好记性不如烂笔头之Oracle SQL优化（1）

*sql优化基于oracle11gR2读书笔记*

一、访问数据的方法

1.1、访问表的方法

访问表中数据的方法有两种：1.全表扫描；2.ROWID扫描

1.1.1、全表扫描

全表扫描是指在访问目标表里的数据数时，会从该表所占用的第一个分区的第一块开始扫描，一直扫描到该表的高水位线，这个范围内所有的数据块都必须读到。

在做全表扫描操作时会使用多块读，这在目标表数据量不大的时候执行效率还是非常高的。但问题是走全表扫描的目标SQL的执行时间会不稳定，不可控，这个时间一定会随着目标表数据量的增加而递增。另外如果用delete语句删除表中的一些数据（哪怕是全部数据），高水位线也不会随之下降，这时候全表扫描就会扫很多的空块，造成资源的浪费。

1.1..2、ROWID扫描

ROWID扫描是指在访问目标表里的数据时，直接通过数据所在的ROWID去定位并访问数据。ROWID表示的是数据行记录所在的物理存储地址。

从严格意义上来讲ROWID扫描有两层含义：1.根据用户在SQL语句中输入的ROWID的值直接去访问对应的数据行记录；2.先去访问相关的索引，然后根据访问索引后得到的ROWID再回表去访问对应的数据行记录。

每个表都有一个ROWID的伪列，我们可以通过DBMS_ROWID包中的相关方法（dbms_rowid.rowid_relative_fno,dbms_rowid.rowid_block_number,dbms_rowid.rowid_row_number），其中dbms_rowid.rowid_relative_fno的结果表示文件编号。

1.2、访问B树索引的方法

注意：B树索引，当单列索引列的值为null时，这一行是不会被添加到索引当中的；当多列复合的索引的列全部为null值时，这一行是不会被添加到索引当中的。如果结果集中可能存在谓词条件为null的情况，就不会走相应索引访问数据了。

1.2.1、索引唯一性扫描（INDEX UNIQUE SCAN）

针对唯一性索引的扫描，仅适用于where条件子句中是等值查询的目标SQL。因为扫描的对象是唯一性索引，且是等值查询，所以最多只会返回一条记录。

1.2.2、索引范围扫描（INDEX RANGE SCAN）

适用于所有类型的B树索引，即不管索引是唯一性索引还是非唯一性索引，where条件里边是等值还是between，>，<等条件。

即使是针对同等条件下的相同SQL（SQL一样，执行环境也一样），当目标索引中的索引行大于1时，索引范围扫描所耗费的逻辑读（consistent gets，可以理解为在块中读的行数）会比索引唯一性扫描多。因为索引唯一性扫描只要扫描到一条记录（oracle认定了最多只会返回一条记录）就可以返回了，而索引范围扫描因为其扫描结果可能会返回多条记录（oracle认为会返回多条记录），所以必须扫描下一条记录直到能够确定没有记录满足查询条件了才能返回。

1.2.3、索引全扫描（INDEX FULL SCAN）

适用于所有类型的B树索引。

索引全扫描就是要扫描目标索引的所有叶子块的所有记录。但这并不意味着需要扫描该索引的所有分支块，因为B树索引的叶子块是左右互联的，在默认情况下只要通过必要的分支块定位到该索引的最左边的叶子块的第一行，就可以利用该索引叶子块之间的双向指针定位到下一个叶子块了。

索引全扫描的执行结果是有序的，而且是按照该索引的索引键值来排序（因为B树索引的逻辑存储顺序本来就是有序的），避免了对该索引的索引键值列的排序操作。默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的（并行可能会导致无序），通常情况下索引全扫描是单块读。

1.2.4、索引快速全扫描（INDEX FAST FULL SCAN）

适用于所有类型的B树索引。

索引快速全扫描和索引全扫描极其类似，主要有以下三点区别：

1.只适用于CBO

2.可以使用多块读和并行执行

3.扫描结果不一定有序。这是因为索引快速全扫描是根据索引行在磁盘中的物理存储顺序来扫描的，而且可以并行执行。对于B树索引逻辑存储有序，物理存储可能是无序的（聚簇索引的逻辑和物理都是有序的）。这就不能通过叶子块的左右指针来定位下一个要扫描的叶子块了。

问题：是怎么样来定位下一个要扫描的叶子块的呢？

1.2.5、索引跳跃扫描（INDEX SKIP SCAN）

适用于所有类型的复合B树索引。

索引跳跃扫描使得那些在where条件中没有对目标索引的前导列（索引定义中的第一列）指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引。

为什么在where条件中没有对目标索引的前导列指定查询条件但oracle依然可以用上该索引呢？这是因为oracle帮你对该索引的前导列的所有distinct值做了遍历。所谓对目标索引的所有distinct值做遍历，其实就是相当于对原目标SQL做等价改写（即把所有要用的目标所有的所有前导列的distinct值都加进来），把目标SQL改写成多条SQL执行结果的union all合集，前导列有多少个distinct值就有多少条SQL。索引跳跃扫描适用的场景是那些目标索引前导列的distinct值数量较少，后续非前导列的可选择性又非常好的情形。

当where条件中没有前导列做为查询条件且distinct值比较多的情况下oracle会选择走全表扫描，但我们也可以使用hint强制走复合索引，这时的执行计划可能是最优的。

二、表连接

1、表连接顺序

在oracle中，不管sql中有多少个表做表连接，在实际执行该sql时都只能先两两做表连接，再依次执行这样的两两表连接过程，直到目标sql中所有的表都已经连接完毕。这样，oracle优化器需要解决两个问题：一是在众多表中选择哪两个表做连接，然后这个结果集再跟哪个表做连接，直到所有的表连接完毕；二是在两个表当中（结果集），哪个表做为驱动表。

2、表连接方法

在oracle方法中，两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接和笛卡尔积连接。优化器在解析含表连接的目标sql时，都需要从上述四种方法中选择一种，作为每一对两两做表连接时需要采用的方法。

表T1,T2排序合并连接

步骤：

a、首先以目标SQL中指定的谓词条件（如果有的话）去访问表T1，然后对访问结果按照表T1中连接列来排序，记为结果集1；

b、接着以目标SQL中指定的谓词条件（如果有的话）去访问表T2，然后对访问结果按照表T2中连接列来排序，记为结果集2；

c、最后对结果集1和结果集2执行合并操作，从中取出匹配记录来作为排序合并连接的最终执行结果。

优缺点：

a、通常情况下，排序合并连接的执行效率会远不如哈希连接，但前者的适用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并连接还能用于其它连接条件。

b、通常情况下，排序合并连接并不适合（但可以用于）OLTP类型的系统，因为对于OLTP系统而言，排序是比较昂贵的操作。

c、没有驱动表的概念。

表T1，T2的嵌套循环连接

顾名思义，就是两层循环。驱动表作为外层循环，被驱动表作为内层循环。

步骤：

a、确定驱动表T1

b、接着以目标SQL中指定的谓词条件（如果有）去访问驱动表T1，访问驱动表T1后得到的结果集我们记为驱动结果集1.

c、对于结果集1中的每条记录，遍历T2表去找匹配的记录。

优缺点：

a、很明显，如果驱动表所对应的结果集记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者选择性较好的非唯一性索引），那效率会比较高；

b、大表也可以作为嵌套循环连接的驱动表，关键看目标SQL中指定的谓词条件（如果有）能否将驱动结果集的数据量降下来。

c、嵌套循环连接有其他连接方法所没有的一个优点：嵌套循环连接可以实现快速响应，即它可以第一时间先返回已经连接过且满足连接条件的记录，而不必等到所有的连接操作全部做完后才返回连接结果。虽然排序合并连接和哈希连接也可以先返回已连接过且满足连接记录的条件的记录，而不必等待所有的连接操作都做完，但他们不是第一时间返回，因为排序合并连接要等到排完序后做合并操作时才能开始返回数据，而哈希连接要等到驱动结果集所对应的哈希表创建完成后在能开始返回数据。

表T1，T2的哈希连接

在10g及其以后的版本中，CBO（哈希连接仅适用于CBO）优化器在解析目标SQL时是否考虑使用哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认为true.

步骤：

主要步骤：

a、确定驱动表T1，应用谓词条件（如果有）得到结果集1；

b、对结果集1中的每一条记录，在连接列上应用哈希函数hash_func_1计算出一个哈希值，根据这个值将结果集1里面的记录分配到哈希表中的哈希槽（hash bucket）里面。

c、在被驱动表T2上应用谓词条件（如果有），得到结果集2；

d、对结果集2中的每条记录，在连接列上应用hash_func_1计算出一个哈希值，根据这个哈希值定位到哈希槽，再与槽中的记录一一比对。

优缺点：

a、哈希连接不保证排序。

b、哈希连接驱动表的连接列的选择性应该尽可能的好，这个可选择性会影响哈希槽中的记录数，进而影响查找匹配记录的效率。

c、只适用于CBO，且只能用于等值条件。

d、哈希连接适用于小表和大表之间做表连接且连接结果集的记录数较多的情形。

表T1和T2的笛卡尔连接

不用排序，没有连接条件

步骤：

a、对于T1表，应用谓词条件（如果有），得到结果集1；

b、对于T2表，应用谓词条件（如果有），得到结果集2；

c、合并结果集1和结果集2；

表连接的类型

内连接

表连接的连接结果只包含那些完全满足连接条件的记录。

标准SQL写法：

目标表1 （inner） join 目标表2 on 连接条件1 [and 连接条件2或者谓词条件1 and ....] where ....

对于内连接而言谓词条件写到where子句之前和where子句之后对最终的结果集没有影响。

Oracle写法：

select * from t1,t2 where t1.col1 = t2.col1 and ...

外连接

外连接又分为左连接、右连接和全连接

左连接的语法：

1、目标表1 left (outer) join 目标表2 on 连接条件 where 谓词条件

左边的目标表1作为驱动表。

谓词条件既可以放到on子句也可以放到where子句，放到on子句的话先执行谓词条件，再执行连接条件；放到where子句的话是先执行连接条件再执行谓词条件。

2、oracle里面独有的语法

select * from t1,t2 where t1.col1 = t2.col1(+)

select * from t1,t2 where t1.col1 = t2.col1(+) and t2.col2 = 'XXX';---先执行连接条件后执行谓词条件t2.col2='XXX'

select * from t1,t2 where t1.col1 = t2.col1(+) and t2.col2(+) = 'XXX';---先在t2表上执行谓词条件t2.col2='XXX',然后在结果集上与t1执行连接条件。

右连接的语法：
右连接使用的关键字是 right (outer) join,驱动表为关键字右边的表。

其他与左连接相同。

全连接的语法：

全连接使用的关键字是full (outer) join.

反连接

反连接(Anti Join)是一种特殊的连接类型，与内连接和外连接不同，oracle里面没有相关的关键字可以在SQL文本里面专门表示反连接。

为了方便说明反连接的含义，约定用t1.x anti = t2.y来表示t1和t2做反连接，且t1是驱动表，t2是被驱动表，反连接的条件为t1.x=t2.y。那么反连接t1.x anti = t2.y的结果集是只要t2中有满足条件t1.x=t2.y的记录存在，则表t1中满足条件t1.x=t2.y的记录就会被丢弃，最后返回的记录就是标t1中不满足条件t1.x=t2.y的记录。

当做子查询的展开时，oracle经常把那些外部where条件为NOT EXISTS、NOT IN和<> ALL的子查询转换成对应的反连接。

说到NOT EXISTS、NOT IN和<> ALL,以下是一些需要注意的地方：

1、NOT IN和<> ALL对NULL值敏感，也就是说如果子查询返回的结果集中包含NULL值，则整个SQL的结果集就是NULL，也就是没有记录。

2、NOT EXISTS对NULL值不敏感，也就是子查询的结果集中是否包含NULL值，对整个SQL的结果集没有影响。

3、t.col NOT IN (子查询)，如果t.col列中存在NULL值，那么对应的记录将被丢弃，对于<> ALL子句也是一样，但NOT EXISTS 子句则相反，这些NULL值所在的记录不会被抛弃。

半连接

半连接和反连接类似。

为了方便说明半连接的含义，约定t1.x semi = t2.y来表示表t1和表t2做半连接，且t1为驱动表，t2为被驱动表，半连接条件为t1.x=t2.y。半连接t1.x semi = t2.y的含义是只要在表t2中找到一条记录满足t1.x=t2.y，则马上停止搜索表t2，并直接返回表t1中满足条件t1.x=t2.y的记录。也就是说，表t2中满足半连接条件t1.x=t2.y的记录即使有多条，表t1中也只会返回第一条满足条件的记录。

二、执行计划

1、如何查看执行计划

在oracle数据库里通常有以下几种查看执行计划的方法：

a、explain plan命令;

explain plan命令的语法是依次执行如下两条命令：

explain plan for + 目标sql;

select * from table(dbms_xplan.display);

在oracle10g及其以上的版本中，如果对目标sql执行explain plan命令，则oracle就将解析目标sql所产生的执行计划具体步骤写入PLAN_TABLES$,随后执行的“select * from table(dbms_xplan.display);”只是把它格式化显示出来。PLAN_TABLES$是一张ON COMMIT PRESERVE ROWS的全局临时表（基于会话的临时表）。 

b、DBMS_XPLAN包

使用DBMS_XPLAN包中的方法从oracle数据库中得到目标SQL的执行计划，针对不同的应用场景，可以选择如下四种方法中的一种：

1). select * from table(dbms_xplan.display);--必须与explain plan命令配合使用。

2). select * from table(dbms_xplan.display_cursor(null,null,'advanced'));---查看最近执行过的sql的执行计划，advanced参数也可以换成all，但前者的结果会详细一点。

3). select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,'advanced'));---查看指定sql的执行计划，前提是目标sql所在的执行计划所在的Child Cursor还没有被age out出Shared Pool。

那么怎么查看sql的相关信息呢？只要目标sql所对应的Child Cursor还在Library Cache中，我们就可以从V$SQL中查到目标SQL的Child Cursor的详细信息，包括SQL ID,SQL HASH VALUE,Child Cursor Number等

4). select * from table(dbms_xplan.display_awr(‘sql_id’));

方法2、3能够显示目标SQL执行计划的前提条件是该SQL的执行计划还在Shared Pool中，而如果该SQL的执行计划已经被age out出Shared Pool，那么只要该SQL的执行计划被oracle采集到AWR Repository中，就可以用方法四来查看该SQL的所有历史执行计划。

该方法显示出来的执行计划看不到执行步骤中使用的谓词条件。

c、SQLPLUS中的AUTOTRACE;

在SQLPLUS中将AUTOTRACE开关打开也能看到目标SQL的执行计划，而且，除此之外还可以得到目标SQL在执行时的资源消耗量。

设置AUTOTRACE开关的语法如下：

SET AUTOTRACE ON|OFF

d、10046事件

使用10046事件得到的目标执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。

用10046时间获取执行计划需要依次执行以下三个步骤：

1).首先在当前的session中激活10046事件；

2).接着在此session中执行目标sql；

3).最后在此session中关闭10046事件；

当执行完以上步骤后，oracle会将目标sql的执行计划写入此session所对应的trace文件中，oracle会在USER_DUMP_DEST所代表的目录下生成这个trace文件，其命名格式为“实例名_ora_当前session的spid.trc”。

激活10046的语句：

alter session set events '10046 trace name context forever,level 12';

或

oradebug event 10046 trace name context forever,level 12;----此方法激活的10046事件可以通过命令oradebug tracefile_name来获取所生成文件的路径及文件名称。

关闭10046事件：

alter session set events '10046 trace name context off'

或

oradebug event 10046 trace name context off

一般使用tkprof命令格式化生成的trace文件，这样trace文件会容易读些。

e、10053事件

f、AWR报告或Statspack报告

2、如何得到准确的执行计划

在1中详细介绍了四种获取oracle执行计划的方法，有些方法获得的执行计划不一定是准确的。

在oracle数据库中，判断得到的执行计划是否准确，就是看目标SQL是否被真正执行过，真正执行过的SQL所对应的执行计划就是准确的，否则是不准确的。

explain plan命令得到的sql执行计划是不准确的，目标sql并没有被执行。

对于DBMS_XPLAN包的四种方法，除了第一种（基于explain plan命令），其余三种得到的执行计划是准确的。

SET AUTOTRACE ON;得到的执行计划可能是不准确的，因为执行计划的来源是基于explain plan命令的。

注：

查看sql执行过多少次的语句：
select sql_text ,executions from v$sqlarea where sql_text like '%test.test01%'

3、如何看懂执行计划

SQL执行计划的执行顺序

先从开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行。

这里说的执行计划是格式化后输出的，而连续的意思是说下一行的缩进大于上一行的缩进，否则就是不连续的。

逻辑读和物理读

我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffercache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不是整个块,而是块中的行.从buffercache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的buffercache 链.找到之后,需要在这个链上加一个cachebuffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache bufferchains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffercache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cachebuffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.