SQL优化总结

SQL 的优化主要涉及几个方面：

（1）相关的统计信息缺失或者不准确

（2）索引问题

（3） SQL 的本身的效率问题，比方使用绑定变量，批量DML 採用bulk等。这个就考验写SQL的基本功了，这一点也是最基本的一点。

一.SQL 编写注意事项

1.1 查看SQL

对于生产环境上的SQL,能够从AWR 或者 Statspack 报告中获取相关的SQL 信息。

这部分參考：

Oracle AWR 介绍

http://blog.csdn.net/tianlesoftware/article/details/4682300

statspack安装使用和report 分析

http://blog.csdn.net/tianlesoftware/article/details/4682329

查看SQL 的性能怎么样，最直接的工具就是通过运行计划。通过运行计划能够看到SQL 的运行路径，逻辑读，物理读等信息。能够这些信息，能够帮助我们推断SQL 是否还有优化的余地。

1.2 SQL 编写的详细注意事项

这部分工作是基本功。

在SQL 编写过程中，避免一些低效的写法。能将SQL的效率提高几倍。如：

to_char(created,'yyyy') = '2011'

trunc(created,'y') = to_date('01-jan-2011','dd-mon-yyyy')

与使用TRUNC 相比。使用TO_CHAR 所用的CPU 时间与前者相差一个数量级（即相差12倍）。由于TO_CHAR 必须把日期转换为一个串。这要使用一个更大的代码路径。并利用当前的全部NLS来完毕这个工作。然后必须运行一个串与串的比較。还有一方面。TRUNC 仅仅需把后5 个字节设置为1.然后将两个7 字节的二进制数进行比較。因此。假设仅仅是要截断一个DATE 列。你将应该避免使用TO_CHAR。

之前从网上转载了一篇文章。链接例如以下：

Oracle SQL的优化

http://blog.csdn.net/tianlesoftware/article/details/4672023

这是几年前转载的文章，当中内容有些也有误。这里就不更正了。

1.3 多表关联方式

表之间的关联有例如以下三种方式：

（1） Nested Loop

Inner table 循环与outer table匹配。这样的是表有索引，选择性较好。表之间的差距不大。 ===》两层for 循环，小表匹配大表。

（2） Hash John

小表做hash ，放内存，然后拿大表的每条记录做hash，然后与之前小表的Hash 值匹配。

==》大表匹配小表。

（3） Sorted Merge Into

表有序。而且没有索引。

详细參考：

多表连接的三种方式具体解释 HASH JOIN MERGE JOINNESTED LOOP

http://blog.csdn.net/tianlesoftware/article/details/5826546

二. 相关理论说明

2.1 Oracle 优化器：CBO 和 RBO

Oracle 的优化器有两种：

RBO(Rule-BasedOptimization): 基于规则的优化器

CBO(Cost-BasedOptimization): 基于代价的优化器

CBO（Cost Based Optimizer）的思路是让Oracle 获取全部运行计划相关的信息。通过对这些信息做计算分析，最后得出一个代价最小的运行计划作为终于的运行计划。

从10g開始，Oracle 已经彻底丢弃了RBO。即使在表，索引没有被分析的时候。Oracle依旧会使用CBO。此时，Oracle 会使用一种叫做动态採样的技术，在分析SQL的时候。动态的收集表，索引上的一些数据块，使用这些数据块的信息及字典表中关于这些对象的信息来计算出运行计划的代价，从而挑出最优的运行计划。

当表没有做分析的时候。Oracle 会使用动态採样来收集统计信息，这个动作仅仅有在SQL运行的第一次，即硬分析阶段使用。兴许的软分析将不在使用动态採样，直接使用第一次SQL硬分析时生成的运行计划。

2.2 软解析和硬解析

Oracle对此SQL将进行几个步骤的处理过程：

1、语法检查(syntax check): 检查此sql的拼写是否语法。

2、语义检查(semantic check): 诸如检查sql语句中的訪问对象是否存在及该用户是否具备对应的权限。

3、对sql语句进行解析(prase): 利用内部算法对sql进行解析，生成解析树(parse tree)及运行计划(execution plan)。

4、运行sql，返回结果(execute and return)

当中解析分为：

Hard Parse：就是上面提到的对提交的Sql全然又一次从头进行解析(当在Shared Pool中找不到时候将会进行此操作)，总共同拥有一下5个运行步骤：

1：语法分析

2：权限与对象检查

3：在共享池中检查是否有全然同样的之前全然解析好的. 假设存在，直接跳过4和5，执行Sql, 此时算soft parse.

4：选择运行计划

5：产生运行计划

注：创建解析树、生成运行计划对于sql的运行来说是开销昂贵的动作，所以，应当极力避免硬解析。尽量使用软解析。

这就是在非常多项目中，倡导开发设计人员对功能同样的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。

Soft Parse：就假设是在Shared Pool中找到了与之全然同样的Sql解析好的结果后会跳过Hard Parse中的后面的两个步骤。

Oracle SQL的硬解析和软解析

http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

Oracle 高 Version counts 问题说明

http://blog.csdn.net/tianlesoftware/article/details/6628232

Oracle SQL Parsing FlowDiagram（SQL 解析流程图）

http://blog.csdn.net/tianlesoftware/article/details/6625683

那么运行计划放在内存的什么位置。在一下的Blog 有说明：

Oracle Library cache 内部机制说明

http://blog.csdn.net/tianlesoftware/article/details/6629869

与解析相关的一个重要參数：cursor_sharing，它决定什么情况下使用同样的cursor，从某种意义上讲，决定是否须要进行解析，该參数有3个值：

（1）FORCE

Allowsthe creation of a new cursor if sharing an existing cursor, or if the cursorplan is not optimal.

（2）SIMILAR

Causesstatements that may differ in some literals, but are otherwise identical, toshare a cursor, unless the literals affect either the meaning of the statementor the degree to which the plan is optimized.

（3）EXACT

Onlyallows statements with identical text to share the same cursor.

--仅仅有SQL 语句全然同样的情况下。才会使用同样的cursor，即运行计划。

Oracle cursor_sharing 參数具体解释

http://blog.csdn.net/tianlesoftware/article/details/6551723

2.3 运行计划

生成SQL的运行计划是Oracle在对SQL做硬解析时的一个很重要的步骤，它制定出一个方案告诉Oracle在运行这条SQL时以什么样的方式訪问数据：索引还是全表扫描，是Hash Join还是Nested loops Join等。

Oracle 运行计划（Explain Plan）说明

http://blog.csdn.net/tianlesoftware/article/details/5827245

Oracle 从缓存里面查找真实的运行计划

http://blog.csdn.net/tianlesoftware/article/details/6556850

Oracle Recursive Calls 说明

http://blog.csdn.net/tianlesoftware/article/details/6561620

我们也能够使用OracleHint 来强制的改变SQL的运行计划。当然Oracle 不建议这么做，由于仅仅要统计信息正确的情况下，CBO 的分析就过一般都是正确的。

Oracle Hint

http://blog.csdn.net/tianlesoftware/article/details/5833020

2.4 10053 和 10046 事件

2.4.1 10053事件

我们在查看一条SQL的运行计划的时候，仅仅能看到CBO 终于告诉我们的运行计划结果，可是不知道CBO 是依据什么来做的。

假设遇到了运行计划失真。如：一个SQL语句，非常明显oracle应该使用索引，可是运行计划却没有使用索引。无法进行分析推断。

10053事件就提供了这种功能。它产生的trace文件提供了Oracle怎样选择运行计划，为什么会得到这种运行计划信息。

对于10053事件的trace文件，我们仅仅能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具仅仅能用来处理sql_trace 和 10046事件产生的trace文件。

10053事件有两个级别：

Level2：2级是1级的一个子集。它包括下面内容：

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Join Methods Considered (NL/MS/HA)

Level1： 1级比2级更具体，它包括2级的全部内容，在加例如以下内容：

Parameters used by the optimizer

Index statistics

启用10053事件：

ALTER SESSION SET EVENTS='10053 trace namecontext forever, level 1';

ALTER SESSION SET EVENTS='10053 trace namecontext forever, level 2';

关闭10053事件：

ALTER SESSION SET EVENTS '10053 trace namecontext off';

说明：

（1）sqlplus中打开autotrace看到的运行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。

应该通过v$sql_plan查看SQL的真实的运行计划。

（2）10053仅仅对CBO有效。并且假设一个sql语句已经解析过，就不会产生新的trace信息。

2.4.2 10046 事件：

10046 事件主要用来跟踪SQL语句。它并非ORACLE 官方提供给用户的命令。在官方文档上也找不到事件的说明信息。可是用的却比較多，由于10046事件获取SQL的信息比SQL_TRACE 很多其它。更有利于我们对SQL的推断。

10046 事件依照收集信息内容，能够分成4个级别：

Level 1：等同于SQL_TRACE 的功能

Level 4：在Level 1的基础上添加收集绑定变量的信息

Level 8：在Level 1 的基础上添加等待事件的信息

Level 12：等同于Level 4+Level 8, 即同一时候收集绑定变量信息和等待事件信息。

--启动10046事件

SQL>alter session set events‘10046 tracename context forever, level 12’;

-- 关闭10046事件

SQL>alter session set events ‘10046 trace name context off’;

也能够使用oradebug 命令来运行10046：

SYS@anqing1(rac1)> oradebug setmypid

SYS@anqing1(rac1)> oradebug event 10046trace name context forever,level 8;

SYS@anqing1(rac1)> oradebug event 10046trace name context off;

SYS@anqing1(rac1)> oradebugtracefile_name

/u01/app/oracle/admin/anqing/udump/anqing1_ora_17800.trc

详细的内容參考：

Oracle oradebug 命令使用说明

http://blog.csdn.net/tianlesoftware/article/details/6525628

Oracle 跟踪事件 set event

http://blog.csdn.net/tianlesoftware/article/details/4977827

Oracle 10053 事件

http://blog.csdn.net/tianlesoftware/article/details/5859027

Event 10053 运行计划绑定变量 Bind peeking

http://blog.csdn.net/tianlesoftware/article/details/5544307

Oracle SQL Trace 和 10046 事件

http://blog.csdn.net/tianlesoftware/article/details/5857023

使用 Tkprof 分析 ORACLE 跟踪文件

http://blog.csdn.net/tianlesoftware/article/details/5632003

2.5 统计信息

优化器收集的统计信息包含例如以下内容：

1）Table statistics

Number of rows

Number of blocks

Average row length

2）Column statistics

Number of distinctvalues (NDV) in column

Number of nulls incolumn

Data distribution(histogram)

3）Index statistics

Number of leaf blocks

Levels

Clustering factor

4）System statistics

I/O performance and utilization

CPU performance andutilization

统计信息收集例如以下数据：

（1）表自身的分析：包含表中的行数。数据块数，行长等信息。

（2）列的分析：包含列值的反复数，列上的空值，数据在列上的分布情况。

（3）索引的分析：包含索引叶块的数量。索引的深度，索引的聚合因子等。

这些统计信息存放在数据字典里，如：

（1）. DBA_TABLES

（2）. DBA_OBJECT_TABLES

（3）. DBA_TAB_STATISTICS

（4）. DBA_TAB_COL_STATISTICS

（5）. DBA_TAB_HISTOGRAMS

（6）. DBA_INDEXES

（7）. DBA_IND_STATISTICS

（8）. DBA_CLUSTERS

（9）. DBA_TAB_PARTITIONS

（10）. DBA_TAB_SUBPARTITIONS

（11）. DBA_IND_PARTITIONS

（12）. DBA_IND_SUBPARTITIONS

（13）. DBA_PART_COL_STATISTICS

（14）. DBA_PART_HISTOGRAMS

（15）. DBA_SUBPART_COL_STATISTICS

（16）. DBA_SUBPART_HISTOGRAMS

统计信息的准确程度，直接决定SQL的效率。

所以须要定期的收集相关对象的统计信息。Oracle 的Statistic 信息的收集分两种：自己主动收集和手工收集。

Oracle 的Automatic StatisticsGathering 是通过Scheduler 来实现收集和维护的。 Job 名称是GATHER_STATS_JOB, 该Job收集数据库全部对象的2种统计信息：

（1）Missing statistics（统计信息缺失）

（2）Stale statistics（统计信息陈旧）

该Job 是在数据库创建的时候自己主动创建，并由Scheduler来管理。Scheduler 在maintenance windows open时执行gather job。默认情况下。job 会在每天晚上10到早上6点和周末全天开启。

该过程首先检測统计信息缺失和陈旧的对象。然后确定优先级。再開始进行统计信息。

Scheduler Job的 stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 假设该值设置为False，那么GATHER_STATS_JOB 会中断，而没有收集完的对象将在下次启动时继续收集。

Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。该过程收集对象statistics的条件例如以下：

（1）对象的统计信息之前没有收集过。

（2）当对象有超过10%的rows 被改动，此时对象的统计信息也称为stale statistics。

Oracle Statistic 统计信息小结

http://blog.csdn.net/tianlesoftware/article/details/4668723

Oracle 推断并手动收集统计信息脚本

http://blog.csdn.net/tianlesoftware/article/details/6445868

三.索引

3.1 索引分类

索引对DB的性能中起着关键的数据。

Oracle 有例如以下类型的索引：

B树索引(默认类型)
位图索引
HASH索引
索引组织表索引
反转键(reverse key)索引
基于函数的索引
分区索引(本地和全局索引)
位图连接索引

3.2 索引限制

这部分内容应该放到SQL 编写部分。只是为了强调。还是放到这块。

即使相关字段上有索引，在例如以下4种情况，也不会走作引：

(1) 使用不等于操作符（<>、!=）

(2) 使用IS NULL 或IS NOT NULL

(3) 使用函数

(4) 比較不匹配的数据类型

所以在SQL 编写过程中，尽量避免以上4种情况。

详细參考：

Oracle 索引具体解释

http://blog.csdn.net/tianlesoftware/article/details/5347098

3.3 索引维护

这部分内容包含：

（1）索引的选择性： distinct/rows, 接近与1，选择性越高。直方图--》数据倾斜。

（2）索引的扩展：index segment 是由extents组成。假设extents大于10，能够考虑重建索引。

（3）索引碎片：查询index_stats表以确定索引中删除的、未填满的叶子（Leaf）行的百分比和 height 字段。

假设索引的叶子行的碎片超过10%，或者 index_stats中height > =4, 能够考虑对索引进行重建。

select name,height, del_lf_rows, lf_rows,round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent" from index_stats

详细參考：

索引维护：

http://blog.csdn.net/tianlesoftware/article/details/5680706

3.4 索引的 Clustering Factor 參数

这个參数是个奇妙的參数，先看一种情况。有的人应该遇到过。就是表的字段上有索引，但依据这个字段做查询时，却发现Oracle并没有使用索引？而且查询条件没有限制索引。

那么Oracle 为什么不走索引？非常可能就是和这个參数值有关。

能够从dba_indexes 表里查看到每一个详细对象的Clustering Factor值：

select owner,index_name, clustering_factor, num_rows from dba_indexes whereowner='SYS' and index_name='IDX_T_ID';

该參数反应的是数据在物理block的上的连续性，假设该值接近于对象的block数，那么数据在block上的存储就是有序的。假设接近与表的行数，能够反应出数据的存储无序。

当数据有序时。我们查询一个数据时，能够从一个block里一次性读出，假设无序。那么我们可能就须要读取多个block。这样I/O次数添加，CBO 就会觉得这样的代价更大，从而选择全表扫描来取代索引。从而导致即使有索引，也不会走。

可是随着系统使用时间的越长，Clustering Factor值是会越来越大的。

解决问题的唯一方法就是对表进行move。

详细參考：

Oracle Index Clustering Factor 说明

http://blog.csdn.net/tianlesoftware/article/details/6585453

3.5 索引扫描5种类型：

（1）index unique scan：查询结果返回一行记录

（2）index range scan：查询结果返回多行记录。

（3）index full scan：可能进行全Oracle索引扫描而不是范围扫描，须要注意的是全Oracle索引扫描仅仅在CBO模式下才有效。 CBO依据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，并且此时查询出的数据都必须从索引中能够直接得到。

（4）index fast full scan：与 index full scan非常类似，可是一个显著的差别就是它不正确查询出的数据进行排序，即数据不是以排序顺序被返回。在这样的存取方法中。能够使用多块读功能，也能够使用并行读入，以便获得最大吞吐量与缩短运行时间。

（5）index skip scan： INDEX SKIP SCAN，发生在多个列建立的复合索引上。假设SQL中谓词条件仅仅包括索引中的部分列，而且这些列不是建立索引时的第一列时。就可能发生INDEX SKIP SCAN。这里SKIP的意思是由于查询条件没有第一列或前面几列，被忽略了。

详细參考：

Oracle 索引扫描的五种类型

http://blog.csdn.net/tianlesoftware/article/details/5852106

四.绑定变量

这个也是SQL 编写的基本功. 简单的说。使用绑定变量能够避免进行硬解析，降低对资源的消耗。

Oracle里的全部SQL 语句都是implicitly sharable的。 SQL 在运行之前，要通过一个hash 运算。生成相关的cursor。

假设通过hash 运算之后，发现已经了有相应的cursor，那就能够直接使用之前的cursor 和plan。

假设不存在，就须要进行硬解析，而硬解析是一个很耗资源的操作。须要尽量降低硬解析。

怎样保证每次HASH 运算之后的hash 值都一样，那么这就须要通过绑定变来来实现。

在第一次运行运行SQL 之后，如果使用了绑定变量。那么Oracle 在硬解析的时候。会进行一个叫Peek的操作。也能够称为偷窥。就是把实际值带进去，帮助产生更加准确的运行计划。比方相应的Peek列上有严重的数据倾斜，如果我们已经对表进行了统计信息收集，oracle 会产生该列的直方图（histogram），在peek的时候。就会依据直方图来决定，是走索引还是走全表扫描更划算。

因此第一运行产生的运行计划肯定是最优的。如今如果数据倾斜有2个值，一个占90%。一个占10%。我们第一使用10%的值，所以第一次走索引。

那么如果我们以后在绑定时，使用了90%的值，那么这时候。Oracle 还是会使用之前的cursor，继续走索引，此时走索引就不是最优的了。

这个就是Oracle 10g里绑定变量的一个问题。在第一次硬解析之后，以后全部的操作都会使用之前产生的cursor 和plan。

所以在10g里。对于列上有严重数据倾斜的，最好是不採用绑定变量。

在Oracle 11g里，对这个问题，进行了优化，使用了Adaptive Cursor Sharing，它能够产生多个共享cursor。

假设是90%的值，就使用cursor 1，假设是10%的cursor，就使用corsor 2. 在这个转换的过程中还是有可能再次产生硬解析。

Oracle 11g的绑定变量处理步骤例如以下：

当我们第一去运行一个带有绑定变量的SQL时。Oracle 会进行硬解析，可是硬解析不能确定最优的运行计划。所以这时候有了Peek。也能够说是偷窥。即把实际值带入。来生成一个selectivity estimate。然后来选择最优的一个运行计划来运行。

这是第一次运行SQL语句。以后运行时就会使用已经存在的plan和cursor。 Oracle 通过Adaptive Cursor Sharing特性同意同一个SQL 能够使用多个运行计划。

在每次运行时，Oracle会依据Peek 的selectivity estimate 值和直方图（假设存在）来推断已经存在的cursor 是否是最优的，假设不是，就又一次创建一个child cursor，并讲Bind-Sensitive 标记为Y。

并且Oracle在SQL 每次运行时。都会收集相关的统计信息，然后依据统计信息进行推断。假设比上次的更好，就在创建一个child cursor，并将Bind-Aware 标记为Y。

当标记为bind-aware cursor 的cursor在下次运行时。Oracle依据新的bind value 来生成新的plan和cursor，并将原来的cursor标记为非共享，即V$SQL.IS_SHAREABLE 设置为 N，当这样的cursor 长期不被使用时，就会被移出shared SQL area.

在bind-aware cursor创建新的cursor 之后。假设这个cursor 和之前某个存在的cursor一样，那么Oracle 会对他们进行合并。

假设在cache里不能找到bind-aware相应的plan，那么就会又一次进行一次硬解析，来生成plan 和cursor，假设这个plan 以后被新的cursor 使用，那么Oracle 会将这2个cursor 进行合并。

Oracle 绑定变量具体解释

http://blog.csdn.net/tianlesoftware/article/details/5856430

Oracle 绑定变量演示样例

http://blog.csdn.net/tianlesoftware/article/details/6324243

Oracle 10g 与 11g 绑定变量（Bind Variable）差别说明

http://blog.csdn.net/tianlesoftware/article/details/6591222

对于绑定变量的cursor 是否能重用，与Cursor_sharing 參数有非常大关系。详细參考：

Oracle cursor_sharing 參数具体解释

http://blog.csdn.net/tianlesoftware/article/details/6551723

Oracle Library cache 内部机制说明