实验看下cursor_sharing与histogram
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the
statement or the degree to which the plan is optimized.
EXACT
Only allows statements with identical text to share the same cursor.
有以上3个值
exact必须精确
force:server 端强制绑定变量
similar:oracle存在histogram时对于不同的变量值重新解析,相当于SIMILAR=EXACT
不存时与force一样
下面实验
首先exact情况
SQL> show user
USER 为 "XH"
SQL> create table t3(a int);
SQL> ed
已写入 file afiedt.buf
1 declare
2 begin
3 for i in 1..100 loop
4 insert into t3 values(i);
5 end loop;
6 commit;
7* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> execute dbms_stats.gather_table_stats('XH','T3');
PL/SQL 过程已成功完成。
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> select distinct sid from v$mystat;
SID
----------
136
SQL>
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 68
session cursor cache count 30
parse count (total) 103
parse count (hard) 20
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 68
session cursor cache count 30
parse count (total) 114
parse count (hard) 21
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1879812956 0
SQL> col sql_text format a30
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1879812956;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=1 1 1 1
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=1879812956;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=1 1 1 3 0
SQL>
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 68
session cursor cache count 30
parse count (total) 115
parse count (hard) 22
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
640433521 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=640433521;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=2 1 1 3 0
SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=640433521;
SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=2 1 1 3 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> alter system set cursor_sharing=force;
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 0
session cursor cache count 5
parse count (total) 19
parse count (hard) 5
SQL> select * from t3 where a=10;
A
----------
10
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=11;
A
----------
11
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 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 1
session cursor cache count 10
parse count (total) 33
parse count (hard) 10
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 0
session cursor cache hits 1
session cursor cache count 16
parse count (total) 34~~~
parse count (hard) 10
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 3 3 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 3 3
_B_0"
force 与peek
实验表xh.t4 10000 rows , distinct 10000,
当使用a>1时应该 走FTS,A>9999时应该走INDEX RANGE SACN
SQL> select * from t4 where a>1;
执行计划
----------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 10000 | 70000 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A">1)
SQL> select * from t4 where a>9999;
执行计划
----------------------------------------------------------
Plan hash value: 4096627024
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 7 | 3 (0)| 00:0
0:01 |
|* 2 | INDEX RANGE SCAN | IND_T4 | 1 | | 2 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A">9999)
SQL> alter system set cursor_sharing=FORCE;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
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 2048
session cursor cache count 30
parse count (total) 1181
parse count (hard) 208
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"
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 25568278
_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 2106
session cursor cache count 29
parse count (total) 1198
parse count (hard) 225
SQL>
SQL> select * from t4 where a>9998;~~~还应该走INDEX
A B
---------- ----------
9999 10000
10000 10001
SQL>
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 2 2 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 2 2 25568278
_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 2106
session cursor cache count 29
parse count (total) 1199~~~~~~~~~~~
parse count (hard) 225
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 3 3 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 3 3 25568278~~~没产生子游标
_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 2106
session cursor cache count 30~~~~~~~cursor CACHE HINT
parse count (total) 1200~~~~~~~~还是一次 SOFT PARSE
parse count (hard) 225
另一个SESSION 看下执行计划
SQL> set autotrace trace exp
SQL> select * from xh.t4 where a>9999;
执行计划
----------------------------------------------------------
Plan hash value: 4096627024
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 7 | 3 (0)| 00:0
0:01 |
|* 2 | INDEX RANGE SCAN | IND_T4 | 1 | | 2 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A">9999)
SQL> select * from xh.t4 where a>1;
执行计划
----------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 10000 | 70000 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A">1)
可以看到 一个是 INDEX RANGE SACN,一个是FTS ,既然 A>1时候 计划不一样应该产生新的 子游标 并且HARD PARSE
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 4 4 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 4 4 25568278
_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 2107~~~~~~~~~
session cursor cache count 30
parse count (total) 1201
parse count (hard) 225
还是没有 产生新子游标,新hard parse,而是出现一次fast soft parse,完全共享直接使用~~
SQL> set autotrace off
SQL> select operation from v$sql_plan where hash_value='314176702';
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
INDEX
用v$sql_plan看~~~~A>1实际 是用了 INDEX RANGE SCAN 方式,这是由于oracle peeking,oracle 会窥视第一次输入变量的实际值产生PLAN,然后一直用这个执行计划,set
autotrace显示的不对
再证实一下
SQL> alter system flush shared_pool;
系统已更改。
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 2163
session cursor cache count 30
parse count (total) 1268
parse count (hard) 231
SQL>
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;
未选定行
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 25568278
_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 2181
session cursor cache count 29
parse count (total) 1285
parse count (hard) 232
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 2 2 3 0
_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 2181
session cursor cache count 29
parse count (total) 1286~~~~~~~~一次soft parse
parse count (hard) 232
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 2 2 25568278
_B_0"
SQL> select operation from v$sql_plan where hash_value='314176702';
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS~~~~~~~~~~~~~~~~~~~~~~还是FTS,应该走INDEX RANGE SCAN,可以看出是由于oracle peek