第三章 Oracle里的Cursor和绑定变量

Library Cache

Oracle数据库中库缓存（Library Cache）的作用和其组成结构。

库缓存实际上是SGA的一块内存区域（更确切地说，库缓存是Shared Pool中的一块内存区域），它的主要作用是缓存刚刚执行过的SQL语句和PL/SQL语句（如存储过程、函数、包、触发器）所对应的执行计划、解析树（Parse Tree）、Pcode、Mcode等对象，当同样的SQL语句和PL/SQL语句再次被执行时，就可以利用已经缓存在Library Cache中的那些对象而无须再次从头开始解析，这样就提高了这些SQL语句PL/SQL语句在重复执行时的执行效率。

 

 

 

硬解析：

硬解析（Hard Parse）是指Oracle在执行目标SQL时，在库缓存中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标SQL并生成相应的Parent Cursor和Child Cursor的过程。（会产生一系列资源争用，如果硬解析过多，会有危害）。理想情况下，OLTP类型系统每秒硬解析的数量应该控制在20以下。

软解析：

软解析是指Oracle在执行目标SQL时，在Libary Cache中找到了匹配的Parent Cusor和Child Cursor，并将存储在Child Cursor中的解析树和执行计划直接拿过来重用而无须从头开始解析的过程。

软软解析：

大致意思是一个SQL执行后在PGA中对应的Session Cursor不会关闭，会进行缓存，下次再执行此SQL的时候省去了打开Session Cursor和关闭的动作。不过剩下的Parse、Bind、Execute、Fetch还是需要做的。

3.2  Oracle里的绑定变量

绑定变量是一种特殊类型的变量，它又被称为占位符。它可以有效降低系统硬解析的数量。

绑定变量的典型用法

var x number;
exec :x :=7369;
select * from scott.emp where empno=:x;

 

绑定变量窥探

随着输入值的不同，目标SQL的where条件的可选择率和结果集的行数可能会随之发生变化，那么SQL的执行计划可能就会发生变化。

对于不使用绑定变量的目标SQL，具体输入值一旦发生了变化，目标SQL的SQL文件就会随之发生变化，这样Oracle就能很容易计算出可选择率和行数，进而据此选择执行计划。但对于使用绑定变量的目标SQL而言，无论对应绑定变量的具体输入值是什么，目标SQL的SQL文本都是一摸一样的，这种情况下Oracle应该如何来决定执行计划呢？

（1）使用绑定变量窥探（就是硬解析时窥探具体的输入值）

（2）如果不使用绑定变量窥探，则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率（例如5%）。

什么是绑定变量窥探？是否启用绑定变量窥探受隐含参数_OPTIM_PEEK_USER_BINDS的控制，默认开启。

当绑定变量窥探开启后，每当Oracle以硬解析的方式解析使用了绑定变量的SQL时，Oracle都会实际窥探一下对应绑定变量的具体输入值，并据此选择执行计划。注意：这里的这个“窥探”　动作只有在硬解析的时候才会执行，当使用了绑定变量的SQL再次执行时（此时对应的是软解析/软软解析），即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle也会沿用之前硬解析时所产生的解析树和执行计划，而不再重复执行上述"窥探”的动作。　

如何让SQL重新进行硬解析：

1.对一个表进行DDL操作

　　通常会选择添加注释的comment语句，因为comment也是DDL操作。一旦对某个表执行DDL操作，再次执行与这个表的所有SQL都会全部进行硬解析。

2.针对执行SQL进行重新硬解析使用DBMS_SHARED_POOL.PURGE。它可以用来删除指定的缓存在库缓存中的Shared Cursor。

select /*cs2*/count(*) from TEST_BIND where OBJECT_ID between :x and :y;
select SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS,ADDRESS,HASH_VALUE from v$sqlarea where SQL_TEXT like 'select /*cs2*/count(*)%'; 
select SQL_TEXT,SQL_ID,HASH_VALUE,CHILD_NUMBER from v$sql where SQL_ID='cp8ctg5b1xjnt';
select * from table(dbms_xplan.display_cursor('1444857497',0,'advanced'));
--exec sys.dbms_shared_pool.purge('ADDRESS+HASH_VALUE','c');
exec sys.dbms_shared_pool.purge('0000000353F7B228,1444857497','c');

3.改变文本型绑定变量定义长度也可能会重新进行硬解析

绑定变量分级

绑定变量分级是指在PLSQL代码中会根据文本型绑定变量的定义长度而将这些文本型绑定变量分为四个等级。

1.32字节以内      　　会分配32字节空间

2.33~128字节 　　    会分配128字节空间

3.129~2000字节　　 会分配2000字节

4.2000字节以上　　   取决于实际传入的实际值大小，如果小于等于2000字节，分配2000字节，如果实际传入大于2000字节，分配4000字节。

一旦Oracle为这些绑定变量所分配的内存空间的大小发生了变化，那么该SQL之前存储在Child  Cursor中的解析树和执行计划就不能被重用了。其原因是Child Cursor中除了会存储目标SQL的解析树和执行计划之外，还会存储该SQL所使用的绑定变量的类型和长度，这意味着即使该SQL的SQL文本没有发生任何改变，只要其SQL文本中文本型绑定变量的定义长度发生了变化，那么该SQL再次执行时就可能还是做硬解析。

 

如何得到已执行的目标SQL中绑定变量的值

1.查询视图V$SQL_BIND_CAPTURE如果找不到有可能对应的Shared Cursor已经被age out出Shared Pool了，这时候可以尝试去AWR Respository相关的数据字典表DBA_HIST_SQLSTAT或DBA_HIST_SQLBIND中查询。

当oracle解析和执行含有绑定变量的目标SQL时，如果满足如下两个条件之一，则该SQL中的绑定变量的具体值就会被Oracle捕获，并可通过视图V$SQL_BIND_CAPTURE查询。

（1）当含有绑定变量的目标SQL以硬解析的方式被执行时。

（2）当含有绑定变量的目标SQL以软解析/软软解析的方式重复执行时，该SQL中的绑定变量的具体输入值可能会被Oracle捕获，只不过默认情况下这种捕获操作Oracle至少得间隔15分钟才会做一次。

注意：Oracle只会捕获那些位于目标SQL的where条件中的绑定变量的具体输入值，而对于那些使用了绑定变量的INSERT语句，不管该语句是否是以硬解析的方式执行，Oracle始终不会捕获其values子句中对应绑定变量的具体输入值。