ORACLE解析流程详解

ORACLE解析流程(SQL Parsing Flow Diagram)

当用户向oracle数据库发送一条sql或者运行匿名或者运行部署在数据库的过程时候，数据库会按照一定的工作机理进行工作，了解此工作机理，可以帮助开发人员编写有效率的代码，或者了解某些运行症状。

Oracle 在它的官方网站metalink给出的这个示意图，我们可以对SQL的处理过程作如下的描述

ORACLE SQL解析流程图

========================

SQL的处理过程作如下的描述：
1、检查是否有打开的游标，如果有，则直接通过游标link到位于PGA的private SQL AREA（ private SQL area），转步骤11。否则，执行步骤2。
2、检查初始化参数SESSION_CACHED_CURSORS是否被设置，如果被设置，则同样可以通过游标指向到位于PGA的私有SQL AREA，转步骤11。否则执行步骤3。
3、检查HOLD_CURSOR以及RELEASE_CURSOR的设置。如果RELEASE_CURSOR=no（默认 no），HOLD_CURSOR=yes（默认为no），当ORACLE执行完SQL语句，为private SQL AREA分配的内存空间被保留，cursor和private SQL AREA之间的link也被保留，预编译程序不再使用它，同样可以通过这个指针直接在private SQL AREA获得语句，转步骤11。
这上面的三种情况，实际上都没有作任何parse，都是直接从位于PGA中的private SQL AREA获得语句并直接执行。此为fast parse。
这三种情况都不存在的情况下，oracle转到步骤4执行。
4、创建一个游标。
5、语法检查Syntax Check：检查语法书写是否正确，是否符合SQL Reference Manual中给出的SQL语法。
6、语义分析Semantic Analysis：查找数据字典，检查表、列是否正确，在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义，验证为存取所涉及的模式对象所需的权限是否满足。
7、将语句转化成ASCII等效数字码，再通过散列算法得到散列值。
8、检查库缓存中是否存在同样hash值的语句。如果存在，转步骤11。否则，执行步骤9。 这就是soft parse。
9、选择执行计划。从可用的执行计划中选择一个最优的执行计划，其中包括存储大纲（srored outline）或物化视图（materialized view）相关的决定。
10、生成该语句的一个编译代码（p-code）。
11、执行语句。

当某个session执行一条语句之后，该语句的parse结果会在library cache中保存，同时也会在PGA的private sql area有一个拷贝的副本。cursor 总是通过一个link是直接链到 private sql area的。如果在private中没有找到这个副本，就需要对SQL进行parse，然后再在library cache中进行hash值的匹配。所以总的来说，使用cursor能不需要任何parse，就是因为直接从当前的private sql area中得到了语句相关信息，包括执行计划。而一旦需要到library cache中进行匹配，就必须需要parse。
soft parse不是不作parse，只是parse的量比较小，只需要作语法检查和语义分析，以及散列语句。

可以从跟踪文件察看执行一条SQL的时是否发生了硬解析：

从跟踪文件的原文件内容判断是否发生了硬解析

Step 1: ALTER SESSION SET sql_trace=true;
Step 2: select * from dual where 600=600

PARSING IN CURSOR #1 len=32 dep=0 uid=109 oct=3 lid=109 tim=328242274096 hv=2021

551448 ad='21cc5d4c'

select * from dual where 600=600

END OF STMT

PARSE #1:c=0,e=2160,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=328242274073

EXEC #1:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328242274741

FETCH #1:c=0,e=208,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328242275207

FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328242276279

..

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

From the raw trace above, the 'PARSE #1' line indicates that there has been a parse call for this statement. The 'mis=1' entry on this line indicates that this is a hard parse.

Step 3: select /* Dummy */ * from dual where 600=600;

PARSING IN CURSOR #1 len=44 dep=0 uid=109 oct=3 lid=109 tim=328245863439 hv=4155

56111 ad='21cc07fc'

select /* Dummy */ * from dual where 600=600

END OF STMT

PARSE #1:c=10000,e=1828,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=328245863418

EXEC #1:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328245863867

FETCH #1:c=0,e=167,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328245864284

FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328245865330

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

Another 'hard' parse (mis=1) as expected since this SQL has not been parsed before.

Step 4: select * from dual where 600=600;

PARSING IN CURSOR #1 len=32 dep=0 uid=109 oct=3 lid=109 tim=328250975145 hv=2021

551448 ad='21cc5d4c'

select * from dual where 600=600

END OF STMT

PARSE #1:c=0,e=354,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328250975125

EXEC #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328250975561

FETCH #1:c=0,e=184,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328250976009

FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328250977032

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

Since this select is a repeat of Step 2 it has already been parsed and, in this case, the parse information is still in the library cache, the query is found in the shared pool and a soft parse occurs. The parse line shows this as mis=0.

Step 5: ALTER system FLUSH shared_pool;
This step 'removes' all the SQL in the shared pool

Step 6: select * from dual where 600=600;

PARSING IN CURSOR #1 len=33 dep=0 uid=109 oct=3 lid=109 tim=328261680757 hv=2409

74811 ad='21fff274'

select * from dual where 600=600

END OF STMT

PARSE #1:c=60000,e=63411,p=0,cr=57,cu=0,mis=1,r=0,dep=0,og=4,tim=328261680736

EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328261681114

FETCH #1:c=0,e=138,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=328261681504

FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=328261682605

XCTEND rlbk=0, rd_only=1

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL OBJ#(222) '

(The trace output above follows large amounts of recursive SQL that has also had to be reparsed due to flushing ALL SQL from the shared pool).
The query in this step is the same as the query in Step 2 and Step 4 Step 2 was a hard parse whereas Step 4 was soft parsed. This time a hard parse is required since the SQL has been flushed from the shared pool and so the parse line shows 'mis=1'.

从TKPROF的格式化输出结果判断是否发生了硬解析：

Steps 2, 4 and 6:

select *

from

dual where 600=600

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.03 0.32 0 4 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 6 0.00 0.00 0 9 0 3

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 12 0.03 0.32 0 13 0 3

Misses in library cache during parse: 2

Optimizer goal: CHOOSE

Parsing user id: 109

Rows Row Source Operation

------- ---------------------------------------------------

 1 TABLE ACCESS FULL DUAL

******************************************************************************

Note: The Parse count above shows 3 parse calls. This consists of two Hard Parses (Step 2 and Step 6) and a single soft parse (Step 4).
'Misses in library cache during parse: 2' records where the SQL was not found (i.e. a library cache miss) in the library cache and indicates that 2 of the parses were hard parses. Step 4 found an existing entry in the library cache for this statement and therefore, only a soft parse was necessary.

Step 3:

select /* Dummy */ * from dual where 600=600

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 109

Rows Row Source Operation

------- ---------------------------------------------------

 1 TABLE ACCESS FULL DUAL

******************************************************************************

In this case the Query shows a hard parse as the parse count is 1 and the 'Misses in library cache during parse: 1'.

在系统表中找出使用直接变量的sql

drop table t_db purge;

create table t_db as select sql_text from v$sqlarea;

select * from t_db;

alter table t_db add sql_text_wo_constants varchar2(1000);

create or replace function

remove_constants( p_query in varchar2 ) return varchar2

as

l_query long;

l_char varchar2(1);

l_in_quotes boolean default FALSE;

begin

for i in 1 .. length( p_query )

loop

l_char := substr(p_query,i,1);

if ( l_char = '''' and l_in_quotes )

then

l_in_quotes := FALSE;

elsif ( l_char = '''' and NOT l_in_quotes )

then

l_in_quotes := TRUE;

l_query := l_query || '''#';

end if;

if ( NOT l_in_quotes ) then

l_query := l_query || l_char;

end if;

end loop;

l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );

for i in 0 .. 8 loop

l_query := replace( l_query, lpad('@',10-i,'@'), '@' );

l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );

end loop;

return upper(l_query);

end;

/

update t_db

set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)

from t_db

group by sql_text_wo_constants

having count(*) > 100

order by 2

The output of that last query will show you statements that are identical in the shared

pool after all numbers and character string constants have been removed. These

statements -- and more importantly their counts -- are the potential bottlenecks. In

addition to causing the contention, they will be HUGE cpu consumers.

If you discover your applications do not use bind variables -- you must have this

corrected. You'll never have a good hit ratio if everyone submits "unique" sql. Your

shared pool will never be used right and you'll be using excessive CPU (90% of the time

it takes to process "insert into t values ( 1 )" is parsing. If you use "insert into t

values ( :x )", and bind the value of 1 -- then the next person that runs that insert

will benefit from your work and run that much faster.

测试

create table table_no_bind_test

(

a int

)

declare

-- Local variables here

i integer;

str1 varchar2(100);

begin

-- Test statements here

for i in 1..120 loop

str1:='insert into table_no_bind_test values('||i||')';

execute immediate str1;

end loop

commit;

end;

select sql_text_wo_constants, count(*) from t_db

group by sql_text_wo_constants

having count(*) > 100

SQL_TEXT_WO_CONSTANTS	COUNT(*)
INSERT INTO TABLE_NO_BIND_TEST VALUES(@)	120

什么情况会发生解析？

1、发布一条新的SQL(什么是新的SQL，或者说什么是不同的SQL?)

2、由于宝贵的SHARE POOL空间有限，只能混存有限的SQL(PL/SQL)，当SHARE POOL已经饱和，如果这时候有新的SQL(PL/SQL)，会将最旧的SQL(PL/SQL)清楚出去。这时候，这条语句虽然以前执行过，但是已经没有在SHARE POOL中被缓存，所以需要重新解析。

使用直接变量是产生情况1 情况2的一个重要原因。

3、触发器

4、在SQL中使用用户定义函数

等等

使用直接变量不仅使得单SESSION情况下，应用效率低下，还会在多SESSION（基本上所有应用是多SESSION）情况下,应用的扩展性大大受到影响。

由于在使用直接变量的情况下，产生了大量的全新SQL,因此在执行这些全新的时候，不可避免地经历完全分析这些SQL,优化执行计划，进行合法性，语句的规范性等过程，需要大量的串行锁—拴，不仅消耗大量的CPU负荷（分析所需要的CPU负荷远比执行SQL的CPU负荷大）,需要更长的时间锁定所涉及到的库缓存，同时运行的SESSON越多，则这种情况就越明显，最终系统停止工作。

这里编写一系列模拟程序，近似模拟多个SESSSION进行硬解析、软解析、快解析运行所需要的挂钟时间

drop table i_binds purge;

create table t_binds

(

a int,

b varchar2(10)

);

drop table run_time_record purge;

create table run_time_record

(

run_type varchar2(30),

sid varchar2(10),

session_num int,

run_time number(10,2)

)

create or replace procedure do_insert_hp_(p1 in integer)

as

p integer;

sql_string varchar2(50);

v_sid int;

begin

delete from run_time_record where run_type='hard parse' and session_num=p1;

execute immediate 'truncate table t_binds';

p:=dbms_utility.get_time;

for i in 1..10000 loop

sql_string:= 'insert into t_binds values('||i||','''||i||'abc'')';

--dbms_output.put_line(sql_string);

execute immediate sql_string;

end loop;

select sid into v_sid from v$mystat where rownum<2;

insert into run_time_record values ('hard parse',v_sid,p1,(dbms_utility.get_time-p)/100);

commit;

end;

create or replace procedure do_insert_sp_(p1 in integer)

as

p integer;

sql_string varchar2(50);

v_sid int;

begin

delete from run_time_record where run_type='soft parse' and session_num=p1;

execute immediate 'truncate table t_binds';

p:=dbms_utility.get_time;

for i in 1..10000 loop

sql_string:= 'insert into t_binds values(:1,:2)';

execute immediate sql_string using i,i||'abc';

end loop;

select sid into v_sid from v$mystat where rownum<2;

insert into run_time_record values(' soft parse ',v_sid,p1,(dbms_utility.get_time-p)/100);

commit;

end;

create or replace procedure do_insert_fp_(p1 in integer)

as

p integer;

v_sid int;

begin

delete from run_time_record where run_type='fast parse' and session_num=p1;

execute immediate 'truncate table t_binds';

p:=dbms_utility.get_time;

for i in 1..10000 loop

insert into t_binds values(i,i||'abc');

end loop;

select sid into v_sid from v$mystat where rownum<2;

insert into run_time_record values(' fast parse ',v_sid,p1,(dbms_utility.get_time-p)/100);

commit;

end;

create or replace procedure run_mutil_task(p_task_name in varchar2, pt in integer )

as

l_job number;

task_name varchar2(100);

begin

for i in 1..pt loop

task_name:=p_task_name||'('||to_char(i)||');';

dbms_job.submit(l_job,task_name);

dbms_output.put_line(l_job);

end loop;

end;

目前本机数据库存在一些问题

可以通过以上测试得出以下图所显示的结论：

1、 使用直接变量引起的硬解析的应用系统中，随着SESSION数增加，响应时间会逐渐缓慢（如果我们作CPU的负荷—SESSION数的关系，会发现在多SESSION情况下，这这样系统CPU的工作负荷很高）；

2、 软解析也会表现出相同的特征，只是影响的程度要小得多

3、PL/SQL 封装的动态SQL 引起软解析， 而使用变量静态SQL 的变量是绑定变量，可以最大可能避免软分析。

JDBC的数据绑定写法

import java.sql.*;

import oracle.jdbc.pool.*;

class prog2 {

public static void main (String args []) throws SQLException

{

String url = "jdbc:oracle:oci8:@//localhost/gx";

OracleDataSource ods = new OracleDataSource();

ods.setURL(url);

ods.setUser("hr");

ods.setPassword("hr");

Connection conn = ods.getConnection();

PreparedStatement cmd = conn.prepareStatement

("SELECT first_name, last_name FROM employees WHERE employee_id = ?");

cmd.setString(1, "101");

ResultSet rs = cmd.executeQuery();

rs.next();

System.out.println ("Last Name: " +

rs.getString(1) +

", First Name: " +

rs.getString(2));

conn.close();

}

}

CURSOR_SHAREING= FORCE可以解决部分只用直接变量的写法的应用，但是CURSOR_SHAREING= FORCE并不能解决soft parse的问题。

CURSOR_SHAREING= FORCE还能引起其它问题：

1、 将语句中所有的字符与数字都转化为绑定变量，这可能导致不同的执行计划；

2、 查询列的长度可能发生改变；

3、 查询优化更困难

因此，最好的方法，还是使用绑定变量。

使用绑定变量的例外规则

1、在WHERE的语句中，总有 FIELDS=CONST,这时候使用直接变量；

绑定变量窥视（bind peeking）

查询优化器在用户第一次运行SQL时候，会窥视用户定义的绑定变量。这个特征不仅决定了语句的过滤条件，也决定了用直接变量代替绑定变量，在后来再执行这条语句的时候，不再会去窥视变量，即使以后不同的直接变量，还是会共享以前的游标。

如何解决绑定变量窥视影响正确的执行计划的执行？

11G新特性：自适应游标共享Adaptive Cursor Sharing

当我们在对高变异数据（skewed data）列使用绑定变量的时候，由于在硬解析法发生了绑定变量窥视的缘故，可能导致使用差的执行计划。11g提供了一个新的特性Adaptive Cursor Sharingy用来解决这个问题，可以为不同的绑定变量真实的值，提供不同的执行计划。

要使用这个新的特性，不需要需要额外的构建或者参数设定。以下的代码给出一个adaptive cursor sharing的例子

Test Case

Firstwecreateandpopulateatesttablewithskeweddataforrecord_type2

CREATETABLEadaptive_test(
idNUMBER,
record_typeNUMBER,
descriptionVARCHAR2(50),
CONSTRAINTadaptive_test_pkPRIMARYKEY(id)
);

CREATEINDEXadaptive_test_record_type_iONadaptive_test(record_type);


DECLARE
TYPEt_adaptive_testISTABLEOFadaptive_test%ROWTYPE;
l_tabt_adaptive_test:=t_adaptive_test();
BEGIN
FORiIN1..100000LOOP
l_tab.extend;
IFMOD(i,2)=0THEN
l_tab(l_tab.last).record_type:=2;
ELSE
l_tab(l_tab.last).record_type:=i;
ENDIF;
l_tab(l_tab.last).id:=i;
l_tab(l_tab.last).description:='Descriptionfor'||i;
ENDLOOP;
FORALLiINl_tab.first..l_tab.last
INSERTINTOadaptive_testVALUESl_tab(i);

COMMIT;
END;
/

Gatherstatisticsforthetable

EXECDBMS_STATS.gather_table_stats(USER,'adaptive_test',method_opt=>'forallindexedcolumnssizeskewonly',cascade=>TRUE);


ThedataintheRECORD_TYPEcolumnisskewedwecancheckusing:

selectrecord_type,count(*)fromadaptive_test
groupbyrecord_typehavingcount(*)>1;

Herewecancanseerecord_typeof2isrepeated50000sofulltablescanisbetterthanindexinthiscase


RECORD_TYPECOUNT(*)
---------------------
250000

显示adaptive cursor如何工作的

This will be shown by executing the query below and checkinghow the execution plan is changed to adapt to the new bind value.

SELECTMAX(id)FROMadaptive_testWHERErecord_type=1;
SELECT*FROMTABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
1

1rowselected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||2(100)||
|1|SORTAGGREGATE||1|9|||
|2|TABLEACCESSBYINDEXROWID|ADAPTIVE_TEST|1|9|2(0)|00:00:01|
|*3|INDEXRANGESCAN|ADAPTIVE_TEST_RECORD_TYPE_I|1||1(0)|00:00:01|
-----------------------------------------------------------------------------------------------------------


Thisqueryhasusedtheindexaswewouldexpect.Nowwerepeatthequery,butthistimeuseabindvariable.

VARIABLEbindNUMBER;
EXEC:bind:=1;

SELECTMAX(id)FROMadaptive_testWHERErecord_type=:bind;
SELECT*FROMTABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
1

1rowselected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT|&n sp;|||2(100)||
|1|SORTAGGREGATE||1|9|||
|2|TABLEACCESSBYINDEXROWID|ADAPTIVE_TEST|1|9|2(0)|00:00:01|
|*3|INDEXRANGESCAN|ADAPTIVE_TEST_RECORD_TYPE_I|1||1(0)|00:00:01|
-----------------------------------------------------------------------------------------------------------

Soweranwhatamountedtothesamequery,andgotthesameresultandexecutionplan.Theoptimizerpickedanexecutionplan
thatitthinksisoptimiumforquerybypeekingatthevalueofthebindvariable.Theonlyproblemis,thiswouldbetotally
thewrongthingtodoforotherbindvalues.


VARIABLEbindNUMBER;
EXEC:bind:=2;

SELECTMAX(id)FROMadaptive_testWHERErecord_type=:bind;
SELECT*FROMTABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
100000

1rowselected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||2(100)||
|1|SORTAGGREGATE||1|9|||
|2|TABLEACCESSBYINDEXROWID|ADAPTIVE_TEST|1|9|2(0)|00:00:01|
|*3|INDEXRANGESCAN|ADAPTIVE_TEST_RECORD_TYPE_I|1||1(0)|00:00:01|
-----------------------------------------------------------------------------------------------------------


IfwelookattheV$SQLviewentryforthisquery,wecanseetheIS_BIND_SENSITIVEcolumnismarkedas'Y',
soOracleisawarethisquerymayrequiredifferingexecutionplansdependingonthebindvariablevalues,butcurrentlytheIS_BIND_AWAREcolumnismarkedas'N',soOracleasnotactedonthisyet.



SELECTsql_id,child_number,is_bind_sensitive,is_bind_aware
FROMv$sql
WHEREsql_text='SELECTMAX(id)FROMadaptive_testWHERErecord_type=:bind';

SQL_IDCHILD_NUMBERII
---------------------------
9bmm6cmwa8saf0YN


Ifwerunthestatementusingthesecondbindvariableagain,wecanseethatOraclehasdecidedtouseanalternate, moreefficientplanforthisstatement.

VARIABLEbindNUMBER;
EXEC:bind:=2;

SELECTMAX(id)FROMadaptive_testWHERErecord_type=:bind;
SELECT*FROMTABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
100000

1rowselected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||138(100)||
|1|SORTAGGREGATE||1|9|||
|*2|TABLEACCESSFULL|ADAPTIVE_TEST|48031|422K|138(2)|00:00:02|
-----------------------------------------------------------------------------------


ThischangeinbehaviorisalsoreflectedintheV$SQLview,whichnowhastheIS_BIND_AWAREcolumnmakedas"Y".


SELECTsql_id,child_number,is_bind_sensitive,is_bind_aware
FROMv$sql
WHEREsql_text='SELECTMAX(id)FROMadaptive_testWHERErecord_type=:bind';

SQL_IDCHILD_NUMBERII
---------------------------
9bmm6cmwa8saf0YN
9bmm6cmwa8saf1YY

is_bind_sensitive(Y), means that Oracleisusingmultipleplansdependingonbindvariable.

is_bind_aware (Y), means that Oracleknowsthatthedifferentdatapatternsmayresultdependingonbindvalue.
Oracleswitchestoabind-awarecursorandmayhardparsethestatement.

总结

• In 11g, Oracle uses bind-aware cursor matching.
• Share the plan when binds values are equivalent.
• Plans are marked with selectivity range.
• If current bind values fall within range they use the same plan .
• Create a new plan if binds are not equivalent.