ORACLE优化之SQL篇

一、SQL调优

SQL优化主要从以下四个方面进行优化。

1、访问路劲

2、表链接

3、优化器

4、执行路劲

二、访问路劲

1、全表扫描

全表扫描又可简称为全扫，或全扫描，这个术语隐含很多意思。Oracle 将读取指定段中用于某一点或另一点上的每个块。全扫描就是批读取所有的块，准确说是读取段高水标记之下所有的块。在这里，Oracle 将从段的开始读到结尾。处理每一个块。全扫描是读取 Oracle的大量数据的行之有效的方法。因为数据库将使用多块读取。由于 Oracle 知道它打算读取读段中的每一块，因此它将一次性读取多个块，而非一次一个块。多块读的数量由初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT 确定。

2、ROWID访问

每一个表都有一个 ROWID 伪列，此伪列中记录了每一行的物理地址。它包含文件号、块号和行在该块中的行号。ROWID 对于获取一个特定的行来说可能是最快的方法。但是，使用ROWID 获取成千上万的行，并不是最好的方法。因为每使用 ROWID 访问一行，都是一次逻辑读。在不使用 ROWID 访问行时，Oracle 将在一次逻辑读中访问尽量多的行。

3、索引扫描

(1) 索引唯一扫描

索引唯一扫描和使用 ROWID 访问行是差不多的。只不过是把每一行的 ROWID 记录到另一处地方，它被叫做索引，每次访问行时，先访问索引，从索引中取出行的 ROWID，根据 ROWID再真正的访问行。

(2) 索引范围扫描

如果索引不是唯一型索引，那么，通过此索引的访问将是索引范围扫描。从根本上说，索引范围扫描和索引唯一扫描是一样的。

(3) 索引跳跃扫描

在复合索引中，比如索引包含两个列：A 列、B 列，如果以 B 列为条件进行查询，将使用索引跳跃扫描。

(4) 索引全扫描

索引全扫描（Index Full Scans）不读取索引结构上的每个块，这与其名字及我们关于全扫描的知识相背。可以这样说，它根据部分枝块，找到第一个叶块，然后按叶块双向链表的顺序，处理所有的叶块。

(5) 索引快速全扫描

索引快速全扫描是把索引当作表一样进行全扫描操作。

三、表链接

1、嵌套循环

在嵌套循环连接中,Oracle 从第一个行源中读取第一行,然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中,然后 Oracle 将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。第一个记录源通常称为外部表,或者驱动表,相应的第二个行源称为内部表。使用嵌套循环连接是一种从连接结果中提取第一批记录的最快速的方法。在驱动行源表(就是您正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势地方是,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。

2、排序合并链接

在排列合并连接中,Oracle 分别将第一个源表、第二个源表按它们各自要连接的列排序,然后将两个已经排序的源表合并。如果找到匹配的数据,就放到结果集中。在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的 5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERED.deptno=E.dejptno,而不是 WHERE D.deptno>=E.deptno)。排列合并连接需要临时的内存块,以用于排序(如果 SORT_AREA_SIZE 设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘 I/O。

3、HASH连接

当内存能够提供足够的空间时,哈希(HASH)连接是 Oracle 优化器通常的选择。在哈希连接中,Oracle 访问一张表(通常是较大的表),并在内存中建立一张基于连接键的哈希表。然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。只有在数据库初始化参数 HASH_JOIN_ENABLED 设 为 True, 并且为参数PGA_AGGREGATE_TARGET 设置了一个足够大的值的时候,Oracle 才会使用哈希边连接(HASH_AREA_SIZE 是向下兼容的参数 。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索 B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的 I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接。

四、优化器

用来生成执行计划的工具就是优化器。Oracle 中优化器有两种 CBO（基于成本）和 RBO（基于规则）。现在基本都是是同CBO优化器。

1、优化器生成执行计划的步骤

(1) 优化器基于可以的 Access Paths（访问路径）和 Hints（提示），生成一组潜在的执行计划。

(2) 优化器基于声明所访问表、索引和分区存放在数据字典中相关的数据分布、存储特性的资料，评估每一个执行计划。此步骤中，优化器基于 I/O, CPU, 和 memory，计算出执行计划的 Cost（成本）。

(3) 优化器比较所有执行计划的成本，选择成本最低的作为最终的执行计划。

2、CBO组成部分

CBO主要由以下三部分组成：Query Transformer(查询转换器)、Estimator(评估器)、Plan Generator(计划生成器）。因此，SQL 声明的解析也分为三个步骤，先由查询转换器对 SQL 声明做转换，简单的话，查询转换就是在开始计算成本前，将声明转为更合理的形式。然后由评估器从选择性、集的势和成本三个方面评估表、索引和各种连接，最后由计划生成器生成执行计划。

(1)Query Transformer

oracle里的查询转换(Query Transformation)，又称查询改写(Query Rewrite)，它是oracle在解析目标sql的过程中的重要一步，是指oracle在解析目标sql时可能会对其做等价改写，目的是为了能更高效的执行目标sql，即oracle可能会将目标sql改写成语义上完全等下但执行效率更高的形式。

(2)Estimator

estimator的目的是评估plan的整体cost，如果统计信息是可用的，estimator将使用统计信息来计算评估量。estimator会产生三种类型的评估量：Selectivity选择性、Cardinality集势、Cost开销

A、Selectivity

选择性是指从行集中返回的行的比例，行集可以是基本表，视图，或者是由join或者group by等操作产生的结果集。Selectivity取决于查询谓词(predicate)或者查询谓词的组合。谓词的选择性表明了限定谓词后返回多少行。选择性的取值范围是0到1，选择性为0意味着没有从行集中选择行，为1则意味着选择了所有行。当统计信息可用的时候estimator使用它来评估选择性，比如一个相等谓词(equality predicate)ename=’Smith’，选择性就为1/distinct(ename)，如果ename上有可用的直方图，那么评估器使用直方图来计算选择性，而不是用distinct value。直方图记录了列上不同值的分布，所以将较好的评价选择性，这个很好理解。

B、Cardinality

集势表示行集的行数，同样这里的行集可以是基本表，视图，或者join，group by等操作的结果集。Base cardinality是指基本表的行数，可以通过analyze table来获得，如果表统计信息不可用，estimator将使用table的extents数来评估base cardinality。Effective cardinality是指从表中选择的行，如果基础表上没有谓词，那么它就等于表的Base cardinality

C、Cost开销

计算公式：cost=io_cost+cup_cost

io_cost=(总块数/mbrc)*(mreadtim/sreadtim)

mbrc=db_file_optimizer_read_count的值sreadtim=ioseektim + db_block_size/iotrfrspeedmreadtim=ioseektim+(db_file_multiblock_read_count* db_block_size)/iotrfrspeed

cpu_cost=cpucycles/(cpuspeed*sreadtim)

(3)Plan Generator

计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个。由于不同的访问路径、连接方式和连接顺序可以任意组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果，因此一个SQL可能存在大量不同的执行计划。但实际上计划生成器很少会试验所有的可能存在的执行计划，如果它发现当前执行计划的成本已经很低了，它将停止试验，相反当前计划的成本如果很高，它将继续试验其他执行计划，因此如果能使计划生成器一开始就找到成本很低的执行计划，则会大量减少所消耗的时间，这也正是我们为什么用HINTS来优化SQL的原因之一。

五、执行路劲

使用 Hints，可以人为的更改生成器生成的执行路径，常用的 Hints 有：

1. /+ALL_ROWS/

表明对语句块选择基于成本的优化方法,并获得最佳吞吐量,使资源消耗最小化.

SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='liuxaiobin';

1. /+FIRST_ROWS/

表明对语句块选择基于成本的优化方法,并获得最佳响应时间,使资源消耗最小化.

SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='liuxaiobin';

1. /+CHOOSE/

表明如果数据字典中有访问表的统计信息,将使用基于成本的优化方法,并获得最佳的吞吐量;如果数据字典中没有访问表的统计信息,将基于规则的优化方法;

SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='liuxaiobin';

1. /+RULE/

表明对语句块选择基于规则的优化方法.

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='liuxaiobin';

1. /+FULL(TABLE)/

表明对表选择全局扫描的方法.

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='liuxaiobin';

1. /+ROWID(TABLE)/

提示明确表明对指定表根据 ROWID 进行访问.

SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'AND EMP_NO='liuxaiobin';

1. /+CLUSTER(TABLE)/

提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMSWHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

1. /+INDEX(TABLE INDEX_NAME)/

表明对表选择索引的扫描方法.

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

1. /+INDEX_ASC(TABLE INDEX_NAME)/

表明对表选择索引升序的扫描方法.

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='liuxaiobin';

1. /+INDEX_DESC(TABLE INDEX_NAME)/

表明对表选择索引降序的扫描方法.

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

1. /+INDEX_FFS(TABLE INDEX_NAME)/

对指定的表执行快速全索引扫描,而不是全表扫描的办法.

select /*+INDEX_FFS(TABLE INDEX_NAME)*/ from emp;

1. /+NOWRITE/

禁止对查询块的查询重写操作

select /*+NOWRITE*/  from emp;

1. /+REWRITE/

可以将视图作为参数

1. /+ORDERED/

根据表出现在 FROM 中的顺序,ORDERED 使 ORACLE 依此顺序对其连接.

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

1. /+USE_NL(TABLE)/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

SELECT /*+ORDERED USE_NL(BSEMPMS)*/ SDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

1. /+USE_MERGE(TABLE)/

将指定的表与其他行源通过合并排序连接方式连接起来.

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMSWHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

1. /+USE_HASH(TABLE)/

将指定的表与其他行源通过哈希连接方式连接起来.

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

1. /+CACHE(TABLE)/

当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表 LRU 的最近使用端

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

1. /+NOCACHE(TABLE)/

当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表 LRU 的最近使用端

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

1. /+APPEND/

直接插入到表的最后,可以提高速度.

insert /*+append*/ into test1 select * from test4;

1. /+NOAPPEND/

通过在插入语句生存期内停止并行模式来启动常规插入

insert /*+noappend*/ into test1 select * from test4 ;