shared SQL,parent cursor,child cursor

近日有朋友問到 cursor 如何確定是否sharable的，Metalink Note 296377.1 說得比較好，簡單註釋如下：

1. shared SQL,parent cursor,child cursor 概念：
所有SQL都是Oracle暗式共享的(implicitly sharable)。當使用者A發出一條SQL後，Oracle會根據SQL文字內容生成hash value(10g還有唯一的SQL_ID)，以便能夠快速找到 Shared pool已經存在的相同SQL。如果找不到，則Oracle會為這個SQL建立一個parent cursor和一個child cursor，這與SQL是否共享是沒有關係的。
parent cursor包含了SQL TEXT和相關的hash value，v$sqlarea中的每一行代表了一個parent cursor，根據address表示了其記憶體地址。
child cursor包含了SQL的metadata，即使得這個SQL可以執行的所有相關資訊，如OBJECT和許可權，優化器設定，執行計劃等。v$sql中中的每一行表示了一個child cursor，根據hash value和address與parent cursor 關聯。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。
第一個child cursor總是使用0來表示其建立順序，V$SQL.CHILD_NUMBER = 0。因此，當從V$SQL_PLAN中查詢某個SQL的執行計劃時，要注意你寫對了CHILD_NUMBER 。
如果有多個child cursor，則表示parent cursor有多個版本，v$sqlarea中的version_count欄位就會紀錄下來。

2. 如何確定SQL 是否可以共享：
假設使用者A執行完一條SQL後，退出然後重新登陸，發出同一條SQL，則根據hash value找到Shared pool中已經存在的parent cursor，然後把 此parent cursor下的child cursor list搜尋一邊，判斷metadata是否完全相同，如果是，則這條sql可以共享，就完成了一次soft parse。
假設使用者B之後發出一條SQL文字完全一樣的SQL，但訪問的table不是A使用者的，而是B使用者自己的，則metadata出現AUTH_CHECK_MISMATCH 和
TRANSLATION_MISMATCH ，無法共享child cursor。Oracle會因此在此parent cursor 下建立一個新的child cursor，也就是一個hard parse。
因此，SQL 是否可以共享是與parent cursor無關的，而是由child cursor決定的。
從v$sql_shared_cursor可以獲得詳細的無法共享的原因：
select * from v$sql_shared_cursor where kglhdpar =   --or sql_id = ''
select * from v$sql_shared_cursor where address =    --or sql_id = ''

一般常見的mismatch是：
  OPTIMIZER_MISMATCH  : 優化器環境設定不同，一般是optimizer相關引數
  BIND_MISMATCH      : 繫結變數的值的長度在第二次執行的時候發生顯著的變化    AUTH_CHECK_MISMATCH : 授權關係不匹配
  TRANSLATION_MISMATCH: 事務環境不匹配

其實最常見的是 BIND_MISMATCH ，在10g中可以測試一下：

create table t1(col1 varchar2(4000));

declare
v_col1 varchar2(4000);
begin
v_col1 := 't';
for i in 1..30 loop
v_col1 := v_col1 ||'t';
insert into t1 values(v_col1);
end loop;
end;
/

--可以看出，變數長度在30以下的時候，還是隻有一個child cursor:

SQL_TEXT                    EXECUTIONS CHILD_NUMBER  ADDRESS  HASH_VALUE
--------------------------- ---------- ------------  -------- ----------
INSERT INTO T1 VALUES(:B1 )         30            0  9E355F10 2351142747

declare
v_col1 varchar2(4000);
begin
v_col1 := 'tttttttttttttttttttttttttttttt';
for i in 31..4000 loop
v_col1 := v_col1 ||'t';
insert into t1 values(v_col1);
end loop;
end;
/

--可以看出，變數長度變化導致了四個child cursor 存在：

SQL_TEXT                     EXECUTIONS CHILD_NUMBER CHILD_ADDRESS  ADDRESS  HASH_VALUE
---------------------------- ---------- ------------ -------------  -------- ----------
INSERT INTO T1 VALUES(:B1 )          32            0 9E355DCC       9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 )          96            1 9E34BA18       9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 )        1872            2 9E34B8D4       9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 )        2060            3 9E34B790       9E355F10 2351142747

3. 使用cursor trace獲得child cursor 無法共享的詳細原因：

--9i
 alter session set events '10270 trace name context forever, level 10';

--10g
alter system set events 'immediate trace name cursortrace level 577, address ';
--(level 578/580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)

alter system set events 'immediate trace name cursortrace level 2147483648, address 1';

4. cursor_sharing=similar 和 force 時的行為
cursor_sharing=similar 時
如果 predicate 為範圍( >,=,<= )，則出現literal replacement 時，一般不會share cursor，除非literal 完全相等；
因為在CBO下，這個時候的literal被用來確定執行計劃，literal replacement會被認 為是UNSAFE的，無法共享。
在RBO時候，CURSOR是會共享的。
       
CBO下，如果 predicate 為=，則出現literal replacement 時，是否share cursor，取決於這個column是否有histgrams。
cursor_sharing=force 時，不管 predicate，如果出現literal replacement ，則會shared cursor，但往往容易得到效能較差的執行計劃。

可以使用10046 判斷一個bind variable 是否 unsafe：
The flag oacfl2 in 9i and fl2 in 10g will show if a variable is unsafe.
  BINDS #2:
  bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 acflg=10 oacfl2=500 size=24 ffset=0 bfp=1036d6408 bln=22 avl=04 flg=09 value=16064
  bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 acflg=10 oacfl2=500 size=24 ffset=0 bfp=1036d4340 bln=22 avl=04 flg=09
 
  If you note oacfl2=500
  #define UACFBLTR 0x00000100 /* Bind was generated by LiTeRal replacement */
  #define UACFUNSL 0x00000200 /* UNSafe Literal */
  #define UACFNDTL 0x00000400 /* Non-DaTa LiteRal */

在11g中，child cursor 共享這部分改動不小，如果從10g升級上去，AWR往往發現某些SQL的VERSION COUNT會變多，可能會命中BUG。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-713050/，如需轉載，請註明出處，否則將追究法律責任。