oracle實驗記錄 （cursor_sharing(2)SIMILAR）
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-616541/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （cursor_sharing(1)exact&force）Oracle
- CURSOR_SHARING=SIMILARMILA
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（2）)Oracle
- 關於 cursor_sharing = similarMILA
- cursor_sharing=similar深度剖析MILA
- cursor_sharing : exact , force , similarMILA
- 關於cursor_sharing=similarMILA
- 關於cursor_sharing = similar(ZT)MILA
- ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’MILA
- 關於cursor_sharing = similar (zt)MILA
- cursor_sharing = similar , exact 區別MILA
- oracle實驗記錄（logfile基礎操作2）Oracle
- oracle實驗記錄 （storage儲存引數(2)）Oracle
- oracle實驗記錄（手動dupliacate database(2)）OracleDatabase
- oracle實驗記錄 （恢復-rman維護(2)）Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- cursor_sharing=similar 與 直方圖MILA直方圖
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 （恢復read only tablespace(2)）Oracle
- oracle實驗記錄 (buffer_cache分析（2）cbc latch)Oracle
- oracle實驗記錄 (oracle 10G dataguard(2)引數部分)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- cursor_sharing設定為similar 的弊端MILA
- oracle實驗記錄 （恢復-關於控制檔案(2)）Oracle
- oracle實驗記錄 (分析oracle硬解析&軟解析&fast soft parse(2))OracleAST
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle