*sql优化基于oracle11gR2读书笔记*
三、Oracle里的Cursor
Oracle中的Cursor是Oracle数据库中SQL解析和执行的载体，是c语言的一种数据结构（oracle是用c写的）。
Oracle数据库中的Cursor分为两种：一种是Shared Cursor，另外一种是Session Cursor。
1、Shared Cursor
先来了解一下什么是库缓存、库缓存对象。
我们知道Oracle中有一个全局内存区域SGA，而SGA又可以分为java池、大池、共享池、空池等等。而库缓存是共享池中的一块内存区域，它的主要作用是缓存刚刚执行过的SQL语句和PL/SQL语句，比如存储过程、函数、包、触发器等所对应的执行计划、解析树、Pcode、Mcode等，当同样的SQL语句和PL/SQL语句再次被执行时，就可以利用已经缓存在库缓存中的那些相关对象而无需再次开始从头解析，这样就提高了这些SQL语句和PL/SQL语句在重复执行时的执行效率。
缓存在库缓存中的对象就是库缓存对象。所有的库缓存对象又是以一种名为库缓存对象句柄的c结构存储在库缓存中，oracle通过访问相关的库缓存对象句柄来访问对应的库缓存对象。库缓存对象句柄是以哈希表的方式存储在库缓存中。哈希值相同的库缓存对象句柄以链式的结构存储在同一个哈希槽中，形成了一个句柄链。
言归正传。
Oracle数据库中的Shared Cursor就是指缓存在库缓存里的一种库缓存对象，说白了就是指缓存在库缓存里的SQL语句和匿名PL/SQL语句所对应的库缓存对象。Shared Cursor里会存储目标SQL的SQL文本、解析树、该SQL所涉及的对象定义、该SQL使用的绑定变量类型和长度，以及该SQL的执行计划等信息。
Shared Cursor又细分为Parent Cursor和Child Cursor，可以通过分别查询视图V$SQLAREA和V$SQL来查看当前缓存在库缓存中的Parent Cursor和Child Cursor信息。
Parent Cursor和Child Cursor的结构是一样的，都是库缓存对象句柄，他们的区别在于Parent Cursor会把目标SQL的SQL文本存储在Name属性中，而Child Cursor的Name属性值为空，而该目标SQL的解析树和执行计划则会存储在其Child Cursor（一种执行计划对应一个Child Cursor）中。Parent Cursor会存储所有该Parent Cursor的Child Cursor的句柄地址，即通过Parent Cursor可以找到其所有的Child Cursor. 这种结构决定了在oracle数据库中，任意一个目标SQL一定会同时对应两个Shared Cursor，一个Parent Cursor和一个Child Cursor，Parent Cursor会存储目标SQL的文本，而目标SQL真正能被重用的解析树和执行计划则被存储在Child Cursor里面。
上面介绍过库缓存对象句柄是存储在一个哈希map的结构中，那么库缓存对象句柄是以什么来生成哈希值的呢？库缓存对象句柄是以其Name属性和NameSpace属性来生成哈希值的，对于Parent Cursor就是目标SQL的SQL文本和NameSpace属性（值为CRSR）。
了解了Shared Cursor中存储的信息及其在库缓存中的存储方式，下面介绍下oracle是如何做到执行计划的重用的。
Oracle在解析目标SQL时，首先会去库缓存中查找匹配的库缓存对象，其查找顺序如下：
1) 根据目标SQL的SQL文本（库缓存对象句柄的Name属性）做哈希运算，用得到的哈希值去库缓存中查找对应的库缓存对象。因为对于Shared Cursor来说其缓存对象句柄的NameSpace属性值为固定的CRSR，所以这里忽略。
2) 然后在根据哈希值查找到哈希槽中的库缓存对象链表中查找匹配的Parent Cursor。在查找匹配的Parent Cursor过程中会比对目标SQL的SQL文本，因为不同的SQL文本计算出来的哈希值有可能相同。
3) 步骤2中查找成功，接下来遍历该Parent Cursor以查找匹配的Child Cursor。
4) 步骤2中查找失败，意味着此时没有可以共享的解析树和执行计划，oracle会从头开始解析，生成Parent Cursor和Child Cursor并将其放在对应的哈希槽中。
5) 步骤3如果找到了对应的Child Cursor，则Oracle会把存储在该Child Cursor的解析树和执行计划拿出来重用，而不用再从头开始解析。
6) 步骤3如果找不到对应的Child Cursor，则意味着没有可以共享的解析树和执行计划，oracle会从头开始解析，新生成一个Child Cursor，并把这个Child Cursor挂在相应的Parent Cursor下面。既然能找到Parent Cursor说明该SQL的执行计划已被缓存，那么怎么会出现找不到Child Cursor的情况呢？首先，Shared Cursor对象是一个全局共享的对象；其次oracle中存在多个表空间，而不同的表空间下面的表可以同名。如果两个用户在不同的表空间对同名的表做了同样的操作，此时根据SQL的哈希值及SQL文本的比较会得到同一个Parent Cursor，显然此时的执行计划肯定不能是一样的（也许连表的定义都不一样），所以会出现找不到Child Cursor的情况。
硬解析
硬解析是指在执行目标SQL时，在库缓存中找不到可以重用的解析树和执行计划，而必须从头开始解析目标SQL，并生成相应的Parent Cursor和Child Cursor.
从前面对查找库缓存对象的顺序的介绍中，我们很容易得到发生硬解析时的两种场景:一是找不到Parent Cursor必须进行硬解析；另外一种是找到了Parent Cursor但找不到Child Cursor时也必须进行硬解析。
硬解析的危害：
1) 硬解析可能导致Shared Pool Latch的争用。发生硬解析时至少会生成一个Child Cursor，也就是必须在库缓存中分配一块内存来存储，而库缓存又是共享池（Shared Pool）中的一块内存，所以实际上是需要在共享池中分配一块内存，而Shared Pool Latch的作用之一就是在并发时保护共享池内存的分配，需要持有Shared Pool Latch才能对共享池内存进行分配。
2) 硬解析可能会导致库缓存相关Latch和Mutex的争用。在查找库缓存对象句柄（或者说在查找Parent Cursor）时需要扫描库缓存对象句柄链，这个动作需要持有Library Cache Latch（11gr1版本及以后用的是Mutex）。
软解析
软解析是指在执行目标SQL时，在Library Cache中找到了匹配的Parent Cursor和Child Cursor，并重用解析树和执行计划而无需从头开始解析的过程。
首先软解析不存在Shared Pool Latch争用，因为不需要分配内存。软解析也需要扫描库缓存对象句柄链以查找可重用的解析树和执行计划，所以不可避免的存在Library Cache Latch的争用。但相比硬解析而言，一、软解析的Library Cache Latch的争用要少得多，对Library Cache Latch的持有时间也会比较短。
总结
如果OLTP类型的系统中在执行目标SQL时能够广泛的使用软解析，则系统的性能和可扩展性就会比全部使用硬解析时有明显提升，执行目标SQL所消耗的系统资源（主要体现在CPU上）也会显著降低。
Session Cursor
Session Cursor是当前session解析和执行SQL的载体。和Shared Cursor一样，Session Cursor也是一张c语言的复杂结构，它也是以哈希表的方式缓存起来的，只不过时缓存在PGA中，而不是缓存在共享池的库缓存里。
关于Session Cursor的几个要点：
1) Session Cursor和session是一一对应的,不同的session的Session Cursor不能共享。
2) Session Cursor是有生命周期的。每个Session Cursor在使用的过程中都至少会经历一次Open，Parse，Bind，Execute，Fetch和Close中的一个或多个阶段，用过的Session缓存不一定会缓存在对应Session的PGA中，这取决于SESSION_CACHED_CURSORS的值是否大于0。
3) Oracle在解析和执行目标SQL时，会先去当前Session的PGA中去查找是否存在匹配的Session Cursor。当Oracle第一次解析和执行目标SQL的时候，会新生成一个Session Cursor和一对Shared Cursor（Parent和Child），Shared Cursor会存储能被其他Session共享、重用的内容（比如解析树和执行计划），而Session Cursor则会经历一次Open、Parse、Bind、Execute、Fetch和Close中的一个或多个阶段。在11g之前，Session Cursor中会保存Parent Cursor的库缓存对象句柄地址，从而通过Session Cursor可以直接定位到Parent Cursor。
当上述目标SQL已硬解析的方式解析和执行完毕后，这个目标SQL锁对应的Shared Cursor已经被缓存在库缓存当中，它所对应的Session Cursor也已经使用完毕，这时候会存在以下两种情况：
第一种：如果参数SESSION_CACHED_CURSORS的值为0，那么Session Cursor就会正常执行close操作，这样当上述SQL再次执行时，在当前session中找不到对应的Session Cursor，但可以在库缓存中找到Parent Cursor和Child Cursor，此时oracle还必须为该SQL新生成一个Session Cursor，并且该Session Cursor还会再经历一次Open,Parse,Bind,Execute,Fetch和Close中的一个或多个，这就是软解析。
第二种：如果参数SESSION_CACHED_CURSORS的值大于0，那么当满足额外条件时（这个条件后面会提到），oracle就不会对Session Cursor执行Close操作，而是将其标记为Soft Closed，当SQL重复执行时就可以通过Session Cursor直接定位到Parent Cursor，对其解析树，执行计划等进行重用，这叫做软软解析。和软解析相比，软软解析省掉了创建一个新的Session Cursor的开销，当然剩下的Open,Parse,Bind,Execute,Fetch还是需要做的。
Session Cursor的相关参数解析
OPEN_CURSORS
用于设定单个Session中同时能够以Open状态并存的Session Cursor的总数。视图V$OPEN_CURSOR可以用来查询数据库中状态为OPEN或者已经被缓存在PGA中的Session Cursor的数量和具体信息（如SQLID和SQL文本）。
SESSION_CACHED_CURSORS
用于设定单个session中能够以Soft Closed状态并存的Session Cursor总数，即用于单个session能够缓存在PGA中的Session Cursor总数。oracle用LRU算法来管理这些Session Cursor。Session Cursor被缓存在PGA中是有额外条件的，在11gr2中，一个Session Cursor能被缓存在PGA中的条件是该SQL的解析和执行次数要超过3次。
Session Cursor的种类和用法
Oracle中的Session Cursor又细分为三种类型：隐式游标、显式游标和参考游标（Ref Cursor）。这三种类型经常用在SQL，PL/SQL代码中。
隐式游标
最常见的Session Cursor，它无处不在。当执行SQL的时候，oracle自动帮我们创建了隐式游标来作为该SQL执行的载体，它的生命周期完全由SQL引擎或者PL/SQL引擎负责。即便如此我们还是可以通过如下四个属性来探测隐式游标的一些信息（最近一条SQL的执行相关信息）：
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN 对于隐式游标来说这个属性永远是false
SQL%ROWCOUNT
显示游标
通常用于PL/SQL代码中，它的生命周期完全由我们在PL/SQL代码中来显式控制。它也有四个属性：
CURSORNAME%FOUND
CURSORNAME%NOTFOUND
CURSORNAME%ISOPEN
CURSORNAME%ROWCOUNT
参考游标
又名动态游标。
绑定变量
使用绑定变量可以有效降低OLTP类型应用系统的硬解析数量。因为那些除SQL文本中对应的输入值不同外其他部分一模一样的同一类型的SQL如果使用了绑定变量，则SQL文本就变得完全相同了，据此计算出来的哈希值也完全相同，这就意味着此时就具备了可以重用解析树和执行计划的基础条件。
绑定变量的使用语法是“:variable_name”,即用冒号和自定义变量名称的组合来替换目标SQL文本中的具体输入值。这里的变量名可以是字母、数字或者字母与数字的组合。
绑定变量的典型用法
在SQL语句中使用绑定变量
var x number;//首先定义变量
exec :x := 1000;//给变量赋值
--在SQL中使用绑定变量
selec × from emp where empno = :x;
PL/SQL中select语句的绑定变量用法
execute immediate [带绑定变量的目标SQL语句] using [对应绑定变量的具体输入值]，举例如下：
declare
v_name varchar2(10);
begin
execute immediate 'select name from person where name = :1' into v_name using 'sara';
dbms_output.put_line(v_name);
end;
PL/SQL中DML语句的绑定变量用法
declare
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
begin
v_sql1 := 'insert into person (name,dob,home_addr,work_addr) values (:1,:2,:3,:4)';
execute immediate v_sql1 using 'link',to_date('1987-01-28','YYYY-MM-DD'),'SZ','HN';
dbms_output.put_line(sql%rowcount);
v_sql2 := 'insert into person (name,dob,home_addr,work_addr) values (:1,:1,:1,:1)';
execute immediate v_sql1 using 'dd',to_date('1987-01-28','YYYY-MM-DD'),'SZ','HN';
dbms_output.put_line(sql%rowcount);
end;
关键字using后面传入的绑定变量具体输入值只与对应绑定变量在SQL语句中的位置有关，而与名称无关。从上述范例可以看出同一条SQL语句中的绑定变量的名称是可以相同的。
declare
v_sql1 varchar2(4000);
v_name varchar2(10);
begin
v_sql1 := 'delete from person where name = :1 returning name into :2';
execute immediate v_sql1 using 'dd' returning into v_name;
dbms_output.put_line(v_name);
end;
关键字returning可以和带绑定变量的SQL连用，其目的是把受该SQL影响的目标行的对应列的值给取出来。
PL/SQL中批量绑定的典型用法
批量绑定是一种优化后的使用绑定变量的方式，它的核心在于以下两点：
1、批量绑定还是会以之前介绍过的方式来使用绑定变量
2、批量绑定的优势在于它是一次性处理一批数据，而不是像常规方式那样一次只处理一条数据，所以它能有效减少PL/SQL引擎和SQL引擎上下文的切换次数，从而提高执行效率。理论上PL/SQL代码里只要执行SQL语句就会发生PL/SQL引擎和SQL引擎的交互，但对PL/SQL代码性能有影响的交互主要发生在PL/SQL代码中的如下两处。
a) 显示游标或者参考游标需要循环执行Fetch操作时。这里的循环操作需要PL/SQL引擎来处理，而Fetch一条记录对应要执行的SQL语句则需要SQL引擎来处理，所以如果不做优化，那么这里每Fetch一条记录，引擎就需要切换一次。
b) 循环内部需要执行SQL操作，和第一种情况一样，如果不做优化则每循环一条记录，引擎就要切换一次。
批量Fetch对应的语法如下：
fetch CURSOR_NAME bulk collect into [自定义数组] <limit batch_size>
一次执行一批SQL语句的语法如下：
forall i in 1..[数组长度]
execute immediate [带绑定变量的目标SQL] using [对应绑定变量的具体输入值]
关键字forall表示一次执行一批SQL语句，可以与INSERT、UPDATE、DELETE语句联合使用。
绑定变量窥探
我们知道，随着具体输入值的不同，目标SQL的where条件的可选择率和结果集的行数可能会随之发生变化，而可选择率和结果集的行数会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对于目标SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL的执行计划可能会发生变化。对于使用了绑定变量的目标SQL而言，情况就完全不一样了，因为现在无论对应绑定变量的具体输入值是什么，目标SQL的SQL文本都是一模一样，这种情况下Oracle应该如何来决定目标SQL的执行计划呢？
对于使用了绑定变量的目标SQL而言，Oracle可以选择如下两种方法来决定其执行计划：
1、使用绑定变量窥探
2、如果不使用绑定变量窥探，则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的选择率。
绑定变量窥探是在9i中引入的，是否启用绑定变量受隐含参数_OPTIM_PEEK_USER_BINDS的控制，默认为true。启用绑定变量窥探后，每当Oracle以硬解析的方式解析使用了绑定变量的目标SQL时，Oracle都会实际窥探一下对应绑定变量的具体输入值，并以这些具体输入值为标准，来决定这些使用了绑定变量的目标SQL的where条件的可选择率和结果集行数，并据此来选择该SQL的执行计划。但是这个“窥探”动作只有在硬解析的时候才会执行，当使用了绑定变量的目标SQL再次执行时（此时是软解析或者软软解析），即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，oracle也会沿用之前硬解析时所产生的解析树和执行计划，而不会重复执行上述的窥探的动作。
绑定变量窥探的优点是可以避免使用默认的可选择率，就有更大的可能性得到准确的执行计划；同样绑定变量窥探的坏处是对于那些执行计划会随着输入值的不同而不同的SQL而言，一旦启用的绑定变量窥探，其执行计划就被固定下来了，至于接下来的执行计划到底是什么，则完全依赖于该SQL在硬解析时传入的对应绑定变量的具体值。
在Oracle 10g中出现了直方图统计信息后，绑定变量窥探的缺点被进一步放大，为了克服绑定变量窥探的缺点，Oracle 11g又引入了自适应游标共享。自适应游标共享可以让使用了绑定变量的目标SQL在启用了绑定变量窥探的前提条件下，不再只沿用之前硬解析时所产生的解析树和执行计划，也就是说自适应游标共享可以在启用了绑定变量窥探的前提条件下，让目标SQL在其可能的多个执行计划之间“自适应“地做出选择，而不再像之前那样必须得刻板地沿用该SQL硬解析时所产生的解析树和执行计划。
如何手动使之前硬解析的执行计划失效呢？可以对目标SQL中所涉及的表执行DDL操作，库缓存中所有在SQL文本中包含了这个表的Shared Cursor都会被标记为失效。另外一种方法是使用DBMS_SHARED_POOL.PURGE。它是从Oracle 10.2.0.4开始引用的一种方法，它可以用来删除指定的缓存在库缓存中的Shared Cursor。
绑定变量分级
绑定变量分级是指Oracle在PL/SQL代码中会根据文本型绑定变量（对于数字型的不适用）的定义长度而将这些文本型的绑定变量分为四个等级。
定义在32字节以内的文本型绑定变量被分在第一个等级；33~128字节之间为第二个等级；129~2000字节之间为第三个等级，2000字节以上为第四个等级。那么为什么要分等级呢？因为在执行目标SQL时。对目标SQL中的每一个绑定变量都要用实际的值来替换，所以该Session Cursor必须为这个绑定变量在PGA中预留一定长度的内存。最理想的情况是实际的长度为多少就分配多少，但这样处理起来很麻烦，为了简化，oracle就分了上述四个等级。对长度在第一个等级内的绑定变量，固定分配32字节的内存；长度在第二个等级内的绑定变量分配128字节的内存；长度在第三个等级内的绑定变量分配2000字节的内存；而长度在第四个等级内的绑定变量，如果变量的实际长度小于等于2000，则分配2000，否则分配4000字节的内存。例如：
----定义变量
v varchar2(28);
v_sql varchar2(2000);
----赋值
v:='abcd';
----由于v的定义长度为最大28，小于32，则分配32字节的内存
v_sql := 'select * from table_name where col1 = :1';
execute immediately v_sql using v;
这里提到绑定变量等级的目的是为了引入一个知识点：
对于PL/SQL代码中那些使用了文本型绑定变量的目标SQL而言，只要其SQL文本中文本型绑定变量的定义长度发生了变化，则oracle为这些绑定变量分配的内存空间的大小也会随着发生变化，这时候之前缓存在Child Cursor中的解析树和执行计划就不能被重用了。因为在Child Cursor中除了会存储目标SQL的解析树和执行计划之外还要存储该SQL使用的绑定变量的类型和长度，即使SQL的文本没有发生任何变化，只要其SQL文本中文本型变量的定义长度发生变化，那么该SQL再次执行时就得做硬解析。
---清理共享池
alter system flush shared_pool;
declare
v_name_1 varchar2(100);
v_sql varchar2(2000);
v_name_2 varchar2(133);
begin
v_name_1 := 'link';
v_name_2 := 'link';
v_sql := 'select * from person where name = :1';
execute immediate v_sql using v_name_1;
execute immediate v_sql using v_name_2;
end;
--执行了两次，执行计划对应两个版本
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from person where name = :1%'
select plan_hash_value,child_number from v$sql where sql_text like 'select * from person where name = :1%'
查看已执行的目标SQL中绑定变量的值
查询视图v$sql_bind_capture如果v$sql_bind_capture中查不到，那么有可能shared cursor已经被age out出了库缓存，这时候可以尝试去AWR Repository相关的数据字典表DBA_HIST_SQLSTAT或DBA_HIST_SQLBIND中查找。
对于解析和执行含有绑定变量的SQL语句时，如果满足如下两个条件之一，则该SQL中的绑定变量的具体输入值就会被捕获，并可通过视图V$SQL_BIND_CAPTURE查询:
1、当含有绑定变量的SQL以硬解析的方式执行时
2、当含有绑定变量的SQL以软解析或者软软解析执行时，该SQL中的绑定变量的具体输入值也可能被捕获，只不过这种捕获动作每15分钟才会发生一次。
Oracle只会捕获那些位于where语句中的绑定变量的具体输入值。