Oracle中SQL语句运行过程的深度解析

最近项目做完了（也许并不能说做完了），但是出现了一个致命的障碍，就是性能。现在想想TOP的作者是讲的是多么的精辟，性能是贯穿在项目需求，项目分析设计，项目编码和单元测试、集成等各个阶段。而不是等项目开发完毕，再来考虑性能问题。但是，事实已是如此。所以，最近先从SQL语句开始优化， 为了更好地优化SQL，那么首先自然需要知道Oracle中的SQL引擎对一条给定的SQL语句，他都做了哪些鲜为人知的事情。本文为个人学习总结，仅作学习记录，其中不妥或者错误之处，敬请指出，本人感激不尽！

为了延续TOP中从Cursor开始说明，这里的Cursor并非PL/SQL中的Cursor，至于这个Cursor是什么，怎么用中文解释，暂时还不清楚。但是我想他其实是一个概念性的东西，代表了SQL在不同环境中的称谓。顽皮点地说，当一条SQL语句进入了SQL引擎，其就被Cursor了。下面看一张TOP上的Cursor的生命周期：

1:Open Cursor：在本次会话的服务器进程的私有内存UGA（User Global Area）中会开辟一个内存区给这个Cursor。此时，SQL语句还没有和该Cursor相关联。

2:Parse Cursor：这个过程就会让SQL语句和Cursor相关联了。这个步骤就是通常意义的SQL解析，最后将执行计划存入共享区。然后UGA中的Cursor会用一个指针指向共享区的一个可共享的Cursor。

3:Define output variables：定义输出变量主要是对于查询和使用了returning语法的insert，update，和delete语句。

4:Bind input variables：如果SQL使用了绑定变量，则这里需要获取绑定变量的值。

5:Execute cursor：执行，但是SQL语句真正的执行往往会延迟到下个步骤。

6:Fetch cursor：如果sql返回数据，则这里获取返回的数据。

7:Close cursor：关闭cursor，释放UGA中占用的资源。但是共享区中的cursor并不会释放。

在上面这个过程中，对于我们关心的SQL语句的解析，一笔带过。下面继续分析第二个步骤Parse Cursor。对于SQL语句的解析，早就听说是软解析和硬解析，下面我们分析下整个解析的过程：

1、整个解析的过程从包含VPD的谓词开始。如果该SQL中含有应用了VPD(Virtual Private Database)的表，则将会首先在SQL语句的where中附加上VPD安全策略的谓词。

2、语法、语义检查和权限控制。语法检查，对sql语句关键字的正确性进行检查，语义检查主要判断该SQL中是否引用了不存在的对象或者是否违反了相关约束条件；权限控制

主要是判断当前操作是否具备相应的操作权限。

3、从共享区中获取一个Parent Cursor，如果未获取到，则在共享区中分配内存，定义一个新的Parent Cursor。这个Parent Cursor结构的关键信息就是这个SQL语句本身。

4、逻辑优化，这个过程应用各种不同的算法和策略对SQL语句进行转换，生成很多等价的SQL。

5、物理优化，首先将上个步骤中各SQL语句生成各自的执行计划，然后应用系统数据字典中的分析统计信息或者通过动态采样获取的统计信息为每个执行计划计算出一个Cost，然后选择最小Cost的执行计划。

6、在共享区中存储一个Child Cursor，这个Child Cursor和上面Parent Cursor是关联的，Child Cursor中主要存储执行计划和运行环境信息。

通过上面，可以看到，在共享区中有一个Parent Cursor和Child Cursor来保存一个SQL语句的信息，通过查询v$sqlarea,v$sql,v$sqltext等视图，我们可以获取sql和cursor的相关信息。

对于上面的各个过程，我们看到，Parent Cursor和Child Cursor是在共享区的，也就是说其可以被共享，嘿嘿，这就是为什么有软解析和硬解析了。

软解析：如果Parent Cursor和Child Cursor在共享区中已经存在，则只需要前两个步骤就行了。这个就是软解析。

硬解析：如果Parent Cursor和Child Cursor不能共享，则需要完整的过程，这个就是硬解析了。

下面通过一个例子来说明关于共享的机制和需要注意的问题。

首先，对于如下几个查询语句

select * from t where n = 1024;

Select * from t where n = 1024;

select  *  from t where n = 1024;

select * from t where n = 1024;

执行上面四条查询语句，然后，我们查看v$sqlarea视图发现，第一条和第四条是相同的，只需要硬解析一次，当第二次执行的时候就不需要硬解析了，所以第一条语句的执行次数是2。这里就说明SQL语句必须完全相同才能共享Parent Cursor。

select sql_id,sql_text,executions from v$sqlarea where  sql_text like '%n=1024%';结果就不贴了。

绑定变量：对于绑定变量，其对于开发来说，或许会增加其代码量。从性能的角度来说，其对OLTP系统的影响巨大，很多系统的瘫痪，归根于此。比如，对于如下的查询语句，select * from t where n = 1024;如果在程序中，我们构造SQL语句的时候，使用如下的代码：

private String buildSQL(int n){

StringBuilder sb = new StringBuilder("select * from t where n = ");

sb.append(n);

return sb.toString();

}

那么，如果应用程序中这个n假如有10万个不同的值，如果这些都被执行了，则在共享区就会存储10万条共享记录。因为对于每个不同的值，其SQL就是两条完全不同的SQL。

所以，建议在OLTP系统当中尽可能使用绑定变量的方式构造SQL语句。但是绑定变量，也会造成执行计划可能并非最优的。比如：

select * from t where c < 1000;

如果这个查询的结果记录数为总数据的90%，则执行计划将会选择全表扫描，而不会走索引。

select * from t where c < 10;

对于这个查询，优化器肯定会走索引，而不会走全表扫描。

但是当使用绑定变量的时候，查询语句就是select * from t where c < :c ;这样优化器并不能知道当前查询的记录数的情况，所以，在生成执行计划的时候，可能最后选择的执行计划并不是最优的。

关于这个，在9i中好像得到了改善，增加了bind variable peeking，在硬解析的过程中会将绑定变量的值加上，再生成执行计划。