解析操作的结果就是一个父游标和一个子游标存储在库缓存中的共享SQL区中。
以下分三个部分讨论一个游标在哪里不能进行共享。
1.父游标在哪里不能进行共享。
2.父游标可以共享情况下子游标什么时候不能共享。
以下分三个部分讨论一个游标在哪里不能进行共享。
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 selected
SCOTT@PROD1>
SCOTT@PROD1> select * from t where n = 1234;
no rows selected
SCOTT@PROD1>
SCOTT@PROD1> SELECT * FROM t WHERE n=1234;
no rows selected
SCOTT@PROD1>
SCOTT@PROD1> SELECT * FROM t WHERE n = 1234;
no rows selected
SCOTT@PROD1>
SCOTT@PROD1> SELECT * FROM t WHERE n = 01234;
no rows selected
SCOTT@PROD1> COLUMN sql_text FORMAT A36
SCOTT@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 1
2254m1487jg50 select * from t where n = 1234 1
g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234 2
7n8p5s2udfdsn SELECT * FROM t WHERE n=1234 1
--字母大小写与空格数的不一致都会导致父游标不能被共享。--实验2.父游标可以共享情况下子游标什么时候不能共享。
SCOTT@PROD1> COLUMN sql_text FORMAT A22
SCOTT@PROD1> COLUMN optimizer_mode FORMAT A14
SCOTT@PROD1> COLUMN optimizer_mode_mismatch FORMAT A1
SCOTT@PROD1> COLUMN optimizer_mismatch FORMAT A1
SCOTT@PROD1> COLUMN xml_reason FORMAT A80
SCOTT@PROD1> COLUMN reason FORMAT A22
SCOTT@PROD1> COLUMN optimizer_mode_cursor FORMAT A21
SCOTT@PROD1> COLUMN optimizer_mode_current FORMAT A22
SCOTT@PROD1> COLUMN language_mismatch FORMAT A17
SCOTT@PROD1>
SCOTT@PROD1> COLUMN sql_id NEW_VALUE sql_id
SCOTT@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(*)
----------
1000
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_1;
Session altered.
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1> start /tmp/1.sql
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_10;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_100;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows_1000;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = first_rows;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = rule;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET optimizer_mode = choose;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SCOTT@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 1
5tjqf7sx5dzmj 1 FIRST_ROWS 2966233522 5
5tjqf7sx5dzmj 2 RULE 2966233522 1
5tjqf7sx5dzmj 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 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 N N Y N N N N N N N N N N N N N N N
<ChildNode><ChildNumbe
r>1</ChildNumber><ID>3
</ID><reason>Optimizer
mismatch(10)</reason>
<size>3x4</size><optim
izer_mode_hinted_curso
r>0</optimizer_mode_hi
nted_cursor><optimizer
_mode_cursor>2</optimi
zer_mode_cursor><optim
izer_mode_current>3</o
ptimizer_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 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 N N Y N N N N N N N N N N N N N N N
<ChildNode><ChildNumbe
r>2</ChildNumber><ID>3
</ID><reason>Optimizer
mismatch(10)</reason>
<size>3x4</size><optim
izer_mode_hinted_curso
r>0</optimizer_mode_hi
nted_cursor><optimizer
_mode_cursor>3</optimi
zer_mode_cursor><optim
izer_mode_current>4</o
ptimizer_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 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 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 1000
SCOTT@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_c
ursor><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_c
ursor><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_c
ursor><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 NUMBER PATH '/Root/ChildNode[1]/ChildNumber',
14 id NUMBER PATH '/Root/ChildNode[1]/ID',
15 reason VARCHAR2(100) PATH '/Root/ChildNode[1]/reason',
16 optimizer_mode_hinted_cursor NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_hinted_cursor',
17 optimizer_mode_cursor NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_cursor',
18 optimizer_mode_current NUMBER PATH '/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
----------------------------------------------------------------------------------------------------
1
1
2
=
3
Z
4
z
SCOTT@PROD1>
SCOTT@PROD1> ALTER SESSION SET nls_sort = xgerman;
Session altered.
SCOTT@PROD1>
SCOTT@PROD1> SELECT * FROM t ORDER BY pad;
N
----------
PAD
----------------------------------------------------------------------------------------------------
2
=
4
z
3
Z
1
1
SCOTT@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 1
1f7qg6nu40shd 1 961378228 1
SCOTT@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
1008
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
