SIMILAR 情况
设置为
SQL> alter system set cursor_sharing=SIMILAR;
系统已更改。
SQL> alter system flush shared_pool;
SQL> desc dba_tab_histograms;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(1000)
SQL> select count(*) from dba_tab_histograms where table_name='T3';
COUNT(*)
----------
2
系统已更改。
SQL> execute dbms_stats.delete_table_stats('XH','T3');
PL/SQL 过程已成功完成。
SQL> select count(*) from dba_tab_histograms where table_name='T3';~~~不存在histogram
COUNT(*)
----------
0
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 552
session cursor cache count 29
parse count (total) 324
parse count (hard) 69
SQL> select * from t3 where a=12;
A
----------
12
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 552
session cursor cache count 29
parse count (total) 329
parse count (hard) 70~~~~~~~~~~
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 1 1 3 0
_B_0"
SQL> select * from t3 where a=13;
A
----------
13
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 552
session cursor cache count 29
parse count (total) 330~~~~~~~~
parse count (hard) 70
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 2 2 3 0
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=917182724;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=:"SYS 1 2 2
_B_0"
~~~~~~~~~~~~~~~没histogram时跟force 一样
SQL> alter system flush shared_pool;
系统已更改。
已写入 file afiedt.buf~~~~~~~~~~~~~~~~~~~收集histogram信息
1 begin
2 dbms_stats.gather_table_stats(
3 'XH',
4 't3',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 120');
8* end;
9 /
PL/SQL 过程已成功完成。
SQL> select count(*) from dba_tab_histograms where table_name='T3';
COUNT(*)
----------
100
SQL> select * from t3 where a=1;
A
----------
1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 0
session cursor cache hits 465
session cursor cache count 29
parse count (total) 163
parse count (hard) 64
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 1 1 3 0
_B_0"
SQL> select * from t3 where a=2;
A
----------
2
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 0
session cursor cache hits 465
session cursor cache count 29
parse count (total) 164
parse count (hard) 65~~~~~~~~~~~~~~~
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 1 1 3 0
_B_0"
select * from t3 where a=:"SYS 1 1 3 1
_B_0"
SQL> select * from t3 where a=3;
A
----------
3
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 0
session cursor cache hits 465
session cursor cache count 29
parse count (total) 165
parse count (hard) 66~~~~~~~~~~~~~~~~
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 1 1 3 0
_B_0"
select * from t3 where a=:"SYS 1 1 3 1
_B_0"
select * from t3 where a=:"SYS 1 1 3 2
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=917182724;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=:"SYS 3 3 3
_B_0"
SQL> select * from t3 where a=1;
A
----------
1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 0
session cursor cache hits 465
session cursor cache count 29
parse count (total) 166~~~~
parse count (hard) 66
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
917182724 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS 2 2 3 0
_B_0"
select * from t3 where a=:"SYS 1 1 3 1
_B_0"
select * from t3 where a=:"SYS 1 1 3 2
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=917182724;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=:"SYS 3 4 4
_B_0"
分析收集histogram后sql未使用BIND变量，当SQL使用HISTOGRAM的列信息时，oracle认为SQL传递的每个常量都是不可靠的（a=2,a=1,a=3）出现了unsafe bind，会为每个SQL生成
一个子游标
（到最后是不共享执行计划的，只是存同一个BUCKET 挂在同一个 LIBRARY CACHE HANDLE上）造成大量的VERSION_COUNT 会产生cursor:pin s wait on x等待，而且若是
version_count太高 N多子指针都位于同一个BUCKET ，那么搜索bucket 由library cache handle串起来的双向链表时间将会长，长时间持有libarary cache latch，影响性能
，所以存在histogram时 跟exact差不多 每次都是hard parse，只是共享了SQL语句的存储 BUCKET =>LIBRARY CACHE HANDLE ，没共享执行计划
来自based cost oracle
Two workarounds appeared in 9i to deal with the traps introduced by cursor_sharing=force.
The easy workaround is the hint /*+ cursor_sharing_exact */, which can be added to a statement
to tell Oracle that the statement should not have its literal constants replaced by bind
variables.
The more subtle and dangerous workaround is to use the option cursor_sharing=similar.
With this value for cursor_sharing, Oracle will first replace literal constants with bind variables,
and then decide to peek at the bind variables so that it can optimize for the incoming values on
every single parse call for the statement if it seems to be a good idea.
The comments about this feature in the 9.2 manuals say that Oracle will reoptimize if the
values of the variables would make a difference to the execution plan. It seems that two things
will trigger this reoptimization: first, if any of the predicates involves a range scan, and second,
even on a simple equality, if there is histogram on a column that appears in a predicate, the
query will be reoptimized. (See script. similar.sql in the online code suite for an example
showing this.)
When this happens, the resources needed for optimization increase, as does the contention,
because Oracle rewrites the query with bind variables, decides it should not be sharable,
and inserts it into the library cache as a new child cursor in v$sql (where lots of copies of the
same, substituted text will presumably be accumulating under the same latch).
The moral of this story is that if you really think you have to set cursor_sharing=similar,
make sure you don’t create more histograms than you absolutely need to, or you may introduce
more performance problems than you solve. (In fact, you should always avoid creating histograms
that you don’t really need—it’s just that this setting for cursor_sharing really exacerbates
the problem.)
上面基本意思就是oracle将字面值(a=1)替换为bind变量,然后PEEK 该binds,这样时如果有必要可以对该SQL语句在每次调用时对输入的值进行优化.
上面示例执行计划都是一样的都是FTS且都是 字面=,下面看看 执行计划不一样的且是范围的,一个FTS,一个INDEX RANGE SCAN
,看下SIMILAR的特点
SQL> alter system flush shared_pool;
系统已更改。
SQL> conn xh/a123
已连接。
SQL> show user;
USER 为 "XH"
SQL> select distinct sid from v$mystat;
SID
----------
140
SQL>
SQL> show parameter cursor_sharing;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
SIMILAR
SQL> select count(*) from dba_tab_histograms where table_name='T4';
COUNT(*)
----------
242
SQL> select * from t4 where a>1;
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
314176702 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS 1 1 3 0
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS 1 1 1 33CB80AC
_B_0"
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2331
session cursor cache count 29
parse count (total) 1384
parse count (hard) 261~~~~~~~~
SQL> select operation from v$sql_plan where hash_value='314176702';
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> select * from t4 where a>9999;
A B
---------- ----------
10000 10001
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
314176702 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS 1 1 3 0
_B_0"
select * from t4 where a>:"SYS 1 1 3 1
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS 2 1 1 33CB80AC
_B_0"
select * from t4 where a>:"SYS 2 1 1 33CB80AC
_B_0"
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2331
session cursor cache count 29
parse count (total) 1385
parse count (hard) 262~~~~~~~~~~~~~
SQL> col options format a10
SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT 1
TABLE ACCESS BY INDEX R 1
OWID
INDEX RANGE SCAN 1
SELECT STATEMENT 0
TABLE ACCESS FULL 0
SQL>
SQL> select * from t4 where a>9998;
A B
---------- ----------
9999 10000
10000 10001
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS 1 1 3 0
_B_0"
select * from t4 where a>:"SYS 1 1 3 1
_B_0"
select * from t4 where a>:"SYS 1 1 3 2~~~
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS 3 1 1 33CB80AC
_B_0"
select * from t4 where a>:"SYS 3 2 2 33CB80AC
_B_0"
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2331
session cursor cache count 29
parse count (total) 1386
parse count (hard) 263~~
SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT 2
TABLE ACCESS BY INDEX R 2
OWID
INDEX RANGE SCAN 2
SELECT STATEMENT 1
TABLE ACCESS BY INDEX R 1
OWID
INDEX RANGE SCAN 1
SELECT STATEMENT 0
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
TABLE ACCESS FULL 0
已选择8行。
SQL> select * from t4 where a>9999;
A B
---------- ----------
10000 10001
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
314176702 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS 1 1 3 0
_B_0"
select * from t4 where a>:"SYS 2 2 3 1
_B_0"
select * from t4 where a>:"SYS 1 1 3 2
_B_0"
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS 3 1 1 33CB80AC~~~~~~~走FTS PLAN 执行1次,解析1次,是一个子游标
_B_0"
select * from t4 where a>:"SYS 3 3 3 33CB80AC~~~有index scan range plan 执行3次,解析3次(一次soft parse),2个子游标
_B_0"
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2331
session cursor cache count 29
parse count (total) 1387~~~~~~~一次soft parse
parse count (hard) 263~~~~~~
SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT 2
TABLE ACCESS BY INDEX R 2
OWID
INDEX RANGE SCAN 2
SELECT STATEMENT 1
TABLE ACCESS BY INDEX R 1
OWID
INDEX RANGE SCAN 1
SELECT STATEMENT 0
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
TABLE ACCESS FULL 0
已选择8行。
可以看到 v$sqlarea有点不同,每个执行计划对应了一个sql_text, 而version_count ,就是表示sql_text:select * from t4 where a>:"SYS _B_0" 这个hash value 对应的
bucket,library cache handle下有 3个子游标(是总和).可以看到 这造成了大量 HARD PARSE 虽然 a>1与a>2 执行计划都一样都是FTS ,与exact情况一样~~
从上面可以看出oracle窥探每个bind实际值,产生合理的执行计划(A>1 FTS,A>9999时窥视 变为 INDEX RANGE SCAN,从这点可以看出SIMILAR是每次执行调用时都窥视一次,而不是象
普通BINDS 只窥视第一次的 后面都共享前面窥视后的执行计划),不过也是认为每一个值都是不可靠的unsafe bind,造成version_count过多,hard parse过多
看下正常使用bind时候的PEEK
SQL> alter system flush shared_pool;
系统已更改。
SQL> variable b number
SQL> exec :b:=1
PL/SQL 过程已成功完成。
SQL> select * from t4 where a>:b;
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=1705074740;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:b 1 1 1 0
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=1705074740;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:b 1 1 1 33CB981C
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2376
session cursor cache count 29
parse count (total) 1436
parse count (hard) 270
SQL> select operation,options,child_number from v$sql_plan where hash_value=1705074
740;
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT 0
TABLE ACCESS FULL 0
SQL> exec :b:=9999
PL/SQL 过程已成功完成。
SQL> select * from t4 where a>:b;
A B
---------- ----------
10000 10001
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1705074740 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=1705074740;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:b 2 2 1 0
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=1705074740;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:b 1 2 2 33CB981C
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 8
session cursor cache hits 2376
session cursor cache count 29
parse count (total) 1438
parse count (hard) 271
SQL> select operation,options,child_number from v$sql_plan where hash_value=1705074
740;
OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT 0
TABLE ACCESS FULL 0
可以看到 还是共享了子游标 (只有一个version_count)
这就是peek,第一次使用时 将窥视bind值,产生执行计划(例中为FTS计划),然后后面 其他BIND值 都将共享这个执行计划(例中为FTS计划),而没按实际情况去分析,比如a>9999 应该
走INDEX RANG SCAN