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
First we create and populate a test table with skewed data for record_type 2 CREATE TABLE adaptive_test ( id NUMBER, record_type NUMBER, description VARCHAR2(50), CONSTRAINT adaptive_test_pk PRIMARY KEY (id) ); CREATE INDEX adaptive_test_record_type_i ON adaptive_test(record_type); DECLARE TYPE t_adaptive_test IS TABLE OF adaptive_test%ROWTYPE; l_tab t_adaptive_test := t_adaptive_test() ; BEGIN FOR i IN 1 .. 100000 LOOP l_tab.extend; IF MOD(i,2)=0 THEN l_tab(l_tab.last).record_type := 2; ELSE l_tab(l_tab.last).record_type := i; END IF; l_tab(l_tab.last).id := i; l_tab(l_tab.last).description := 'Description for ' || i; END LOOP; FORALL i IN l_tab.first .. l_tab.last INSERT INTO adaptive_test VALUES l_tab(i); COMMIT; END; / Gather statistics for the table EXEC DBMS_STATS.gather_table_stats(USER, 'adaptive_test', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE); The data in the RECORD_TYPE column is skewed we can check using : select record_type,count(*) from adaptive_test group by record_type having count(*) >1; Here we can can see record_type of 2 is repeated 50000 so full table scan is better than index in this case RECORD_TYPE COUNT(*) ----------- ---------- 2 50000
显示adaptive cursor如何工作的
This will be shown by executing the query below and checking how the execution plan is changed to adapt to the new bind value.
SELECT MAX(id) FROM adaptive_test WHERE record_type = 1; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); MAX(ID) ---------- 1 1 row selected. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 9 | | | | 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable. VARIABLE bind NUMBER; EXEC :bind := 1; SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); MAX(ID) ---------- 1 1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | &n sp; | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------- So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thinks is optimium for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong thing to do for other bind values. VARIABLE bind NUMBER; EXEC :bind := 2; SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); MAX(ID) ---------- 100000 1 row selected. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 9 | | | | 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = 'SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind'; SQL_ID CHILD_NUMBER I I ------------- ------------ - - 9bmm6cmwa8saf 0 Y N If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement. VARIABLE bind NUMBER; EXEC :bind := 2; SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); MAX(ID) ---------- 100000 1 row selected. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 138 (100)| | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | TABLE ACCESS FULL| ADAPTIVE_TEST | 48031 | 422K| 138 (2)| 00:00:02 | ----------------------------------------------------------------------------------- This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y". SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = 'SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind'; SQL_ID CHILD_NUMBER I I ------------- ------------ - - 9bmm6cmwa8saf 0 Y N 9bmm6cmwa8saf 1 Y Y is_bind_sensitive (Y), means that Oracle is using multiple plans depending on bind variable. is_bind_aware (Y), means that Oracle knows that the different data patterns may result depending on bind value. Oracle switches to a bind-aware cursor and may hard parse the statement.
总结
- 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.