父子游标不可共享的情况分析

解析操作的结果就是一个父游标和一个子游标存储在库缓存中的共享SQL区中。
以下分三个部分讨论一个游标在哪里不能进行共享。
1.父游标在哪里不能进行共享。
2.父游标可以共享情况下子游标什么时候不能共享。

3.执行环境不仅影响执行计划还有可能影响SQL执行结果。

--实验1.父游标在哪里不能进行共享。SCOTT@PROD1> DROP TABLE t;Table dropped.SCOTT@PROD1> SCOTT@PROD1> CREATE TABLE t  2  AS  3  SELECT rownum AS n, rpad('*',100,'*') AS pad  4  FROM dual  5  CONNECT BY level <= 1000;Table created.SCOTT@PROD1> SCOTT@PROD1> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')PL/SQL procedure successfully completed.SCOTT@PROD1> SCOTT@PROD1> ALTER SYSTEM FLUSH SHARED_POOL;System altered.SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET cursor_sharing = 'EXACT';Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT * FROM t WHERE n = 1234;no rows selectedSCOTT@PROD1> SCOTT@PROD1> select * from t where n = 1234;no rows selectedSCOTT@PROD1> SCOTT@PROD1> SELECT*FROM  t  WHEREn=1234;no rows selectedSCOTT@PROD1> SCOTT@PROD1> SELECT * FROM t WHERE n = 1234;no rows selectedSCOTT@PROD1> SCOTT@PROD1> SELECT * FROM t WHERE n = 01234;no rows selectedSCOTT@PROD1> COLUMN sql_text FORMAT A36SCOTT@PROD1> SELECT sql_id, sql_text, executions  2  FROM v$sqlarea  3  WHERE sql_text LIKE '%1234';SQL_ID      SQL_TEXT   EXECUTIONS------------- ------------------------------------ ----------6vdany43w59xn SELECT * FROM t WHERE n = 01234    12254m1487jg50 select * from t where n = 1234    1g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234    27n8p5s2udfdsn SELECT  *  FROM  t  WHERE  n=1234     1--字母大小写与空格数的不一致都会导致父游标不能被共享。

--实验2.父游标可以共享情况下子游标什么时候不能共享。SCOTT@PROD1> COLUMN sql_text FORMAT A22SCOTT@PROD1> COLUMN optimizer_mode FORMAT A14SCOTT@PROD1> COLUMN optimizer_mode_mismatch FORMAT A1SCOTT@PROD1> COLUMN optimizer_mismatch FORMAT A1SCOTT@PROD1> COLUMN xml_reason FORMAT A80SCOTT@PROD1> COLUMN reason FORMAT A22SCOTT@PROD1> COLUMN optimizer_mode_cursor FORMAT A21SCOTT@PROD1> COLUMN optimizer_mode_current FORMAT A22SCOTT@PROD1> COLUMN language_mismatch FORMAT A17SCOTT@PROD1> SCOTT@PROD1> COLUMN sql_id NEW_VALUE sql_idSCOTT@PROD1> SCOTT@PROD1> DROP TABLE t;Table dropped.SCOTT@PROD1> SCOTT@PROD1> CREATE TABLE t  2  AS  3  SELECT rownum AS n, rpad('*',100,'*') AS pad  4  FROM dual  5  CONNECT BY level <= 1000;Table created.SCOTT@PROD1> SCOTT@PROD1> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')PL/SQL procedure successfully completed.SCOTT@PROD1> SCOTT@PROD1> ALTER SYSTEM FLUSH SHARED_POOL;System altered.SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET cursor_sharing = 'EXACT';Session altered.SCOTT@PROD1> ALTER SESSION SET optimizer_mode = all_rows;Session altered.SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_1;Session altered.SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> start /tmp/1.sqlSCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_10;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_100;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_1000;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET optimizer_mode = rule;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET optimizer_mode = choose;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT count(*) FROM t;  COUNT(*)----------      1000SCOTT@PROD1> SCOTT@PROD1> SELECT sql_id, child_number, optimizer_mode, plan_hash_value, executions  2  FROM v$sql  3  WHERE sql_text = 'SELECT count(*) FROM t';SQL_ID      CHILD_NUMBER OPTIMIZER_MODE PLAN_HASH_VALUE EXECUTIONS------------- ------------ -------------- --------------- ----------5tjqf7sx5dzmj 0 ALL_ROWS       2966233522   15tjqf7sx5dzmj 1 FIRST_ROWS       2966233522   55tjqf7sx5dzmj 2 RULE        2966233522   15tjqf7sx5dzmj 3 CHOOSE       2966233522   1--结果可以看出创建了一个父游标‘5tjqf7sx5dzmj’和四个子游标，而且拥有相同的执行计划。--不过此处OPTIMIZER_MODE明显存在问题，first_rows_*都被识别为FIRST_ROWS，所以即使执行环境不一样，SQL也有可能错误共享子游标。--通过查看v$sql_shared_cursor视图可以发现哪些不匹配导致出现了多个子游标。--不匹配列值被设置为Y，匹配为N。SCOTT@PROD1> SCOTT@PROD1> SELECT *  2  FROM v$sql_shared_cursor  3  WHERE sql_id = '&sql_id'  4  AND child_number > 0;old   3: WHERE sql_id = '&sql_id'new   3: WHERE sql_id = '5tjqf7sx5dzmj'SQL_ID      ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D LANGUAGE_MISMATCH T B I------------- -------- -------- ------------ - - - - - - - - - - - - - - - - ----------------- - - -I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -REASON----------------------5tjqf7sx5dzmj 44AF3004 448E3358    1 N N N N N N N N N N N N N N N N N         N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N<ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><size>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cursor>2</optimizer_mode_cursor><optimizer_mode_current>3</optimizer_mode_current></ChildNode>5tjqf7sx5dzmj 44AF3004 4659611C    2 N N N N N N N N N N N N N N N N N         N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N<ChildNode><ChildNumber>2</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><size>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cursor>3</optimizer_mode_cursor><optimizer_mode_current>4</optimizer_mode_current></ChildNode>5tjqf7sx5dzmj 44AF3004 44AF3544    3 N N N N N N N N N N N N N N N N N         N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N--在11.0.2中，v$sql_shared_cursor中提供reason列来提供不匹配的文字描述。SCOTT@PROD1> SET LONG 1000SCOTT@PROD1> SCOTT@PROD1> SELECT xmltype('<Root>'||reason||'</Root>').  2        extract('/Root/ChildNode[1]').  3        getstringval() AS xml_reason  4  FROM v$sql_shared_cursor  5  WHERE sql_id = '&sql_id';old   5: WHERE sql_id = '&sql_id'new   5: WHERE sql_id = '5tjqf7sx5dzmj'XML_REASON--------------------------------------------------------------------------------<ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><size>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cursor>1</optimizer_mode_cursor><optimizer_mode_current>2</optimizer_mode_current></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><size>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cursor>2</optimizer_mode_cursor><optimizer_mode_current>3</optimizer_mode_current></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><size>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cursor>3</optimizer_mode_cursor><optimizer_mode_current>4</optimizer_mode_current></ChildNode>SCOTT@PROD1> SCOTT@PROD1> SELECT x.child_number, x.reason,  2      decode(x.optimizer_mode_cursor, 1, 'ALL_ROWS',  3      2, 'FIRST_ROWS',  4      3, 'RULE',  5      4, 'CHOOSE', x.optimizer_mode_cursor) AS optimizer_mode_cursor,  6      decode(x.optimizer_mode_current, 1, 'ALL_ROWS',  7       2, 'FIRST_ROWS',  8       3, 'RULE',  9       4, 'CHOOSE', x.optimizer_mode_current) AS optimizer_mode_current 10  FROM v$sql_shared_cursor s, 11    XMLTable('/Root' 12     PASSING XMLType('<Root>'||reason||'</Root>') 13     COLUMNS child_number NUMBERPATH '/Root/ChildNode[1]/ChildNumber', 14     id NUMBERPATH '/Root/ChildNode[1]/ID', 15     reason VARCHAR2(100) PATH '/Root/ChildNode[1]/reason', 16     optimizer_mode_hinted_cursor NUMBERPATH '/Root/ChildNode[1]/optimizer_mode_hinted_cursor', 17     optimizer_mode_cursor NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_cursor', 18     optimizer_mode_current NUMBERPATH '/Root/ChildNode[1]/optimizer_mode_current' 19     ) x 20  WHERE s.sql_id = '&sql_id';old  20: WHERE s.sql_id = '&sql_id'new  20: WHERE s.sql_id = '5tjqf7sx5dzmj'CHILD_NUMBER REASON    OPTIMIZER_MODE_CURSOR OPTIMIZER_MODE_CURRENT------------ ---------------------- --------------------- ----------------------   0 Optimizer mismatch(10) ALL_ROWS  FIRST_ROWS   1 Optimizer mismatch(10) FIRST_ROWS  RULE   2 Optimizer mismatch(10) RULE  CHOOSE

--实验3.执行环境不仅影响执行计划还有可能影响SQL执行结果。SCOTT@PROD1> TRUNCATE TABLE t;Table truncated.SCOTT@PROD1> SCOTT@PROD1> INSERT INTO t VALUES (1, '1');1 row created.SCOTT@PROD1> INSERT INTO t VALUES (2, '=');1 row created.SCOTT@PROD1> INSERT INTO t VALUES (3, 'Z');1 row created.SCOTT@PROD1> INSERT INTO t VALUES (4, 'z');1 row created.SCOTT@PROD1> COMMIT;Commit complete.SCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET nls_sort = binary;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT * FROM t ORDER BY pad; N----------PAD---------------------------------------------------------------------------------------------------- 11 2= 3Z 4zSCOTT@PROD1> SCOTT@PROD1> ALTER SESSION SET nls_sort = xgerman;Session altered.SCOTT@PROD1> SCOTT@PROD1> SELECT * FROM t ORDER BY pad; N----------PAD---------------------------------------------------------------------------------------------------- 2= 4z 3Z 11SCOTT@PROD1> SCOTT@PROD1> SELECT sql_id, child_number, plan_hash_value, executions  2  FROM v$sql  3  WHERE sql_text = 'SELECT * FROM t ORDER BY pad';SQL_ID      CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS------------- ------------ --------------- ----------1f7qg6nu40shd 0 961378228    11f7qg6nu40shd 1 961378228    1SCOTT@PROD1> SCOTT@PROD1> SELECT child_number, language_mismatch  2  FROM v$sql_shared_cursor  3  WHERE sql_id = '&sql_id'  4  AND child_number > 0;old   3: WHERE sql_id = '&sql_id'new   3: WHERE sql_id = '1f7qg6nu40shd'CHILD_NUMBER LANGUAGE_MISMATCH------------ -----------------   1 Y