初始化参数之cursor_sharing
一、Cursor_sharing简介：
这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。
Cursor_sharing参数有3个值可以设置：
1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。
2)、SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。
3)、FORCE：force是在任何情况下，无条件重用SQL。
备注：上面所说的SQL重用，仅仅是指谓词条件不同的SQL语句，实际上这样的SQL基本上都在执行同样的业务操作。
二、在Cursor_sharing参数值不同的时对SQL的影响：
2.1 创建实验环境：
----首先创建一张jack表----
1 SQL> create table jack (id int,name varchar2(10)); 2 3 Table created. 4 ----产生一些数据----
5 SQL> insert into jack values(1,'aa'); 6 7 1 row created. 8 9 SQL> insert into jack values(2,'bb'); 10 11 1 row created. 12 13 SQL> insert into jack values(3,'cc'); 14 15 1 row created. 16 17 SQL> insert into jack values(4,'dd'); 18 19 1 row created. 20 21 SQL> commit; 22 23 Commit complete. 24 25 SQL> select * from jack; 26 27 ID NAME 28 ---------- ---------- 29 1 aa 30 2 bb 31 3 cc 32 4 dd 33 ----创建下面实验将要用到的三张表----
34 SQL> create table jack_exact as select * from jack; 35 36 Table created. 37 38 SQL> create table jack_similar as select * from jack; 39 40 Table created. 41 42 SQL> create table jack_force as select * from jack; 43 44 Table created. 45 ----查看该session的trace文件的路径----
46 SQL> @/u01/scripts/showtrace 47 48 trace_file_name 49 -------------------------------------------------------------------------------- 50 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc
2.2 cursor_sharing=exact的情况：
----将cursor_sharing设置为exact----总结：当cursor_sharing=exact时，只有当SQL语句是完全一样的情况下才能被重用。
1 SQL> alter session set cursor_sharing=exact; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_exact where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_exact where id=3; 16 17 ID NAME 18 ---------- ---------- 19 3 cc 20
21 SQL> select * from jack_exact where id=1; 22 23 ID NAME 24 ---------- ---------- 25 1 aa 26 27 SQL> alter session set sql_trace=false; 28 29 Session altered. 30 ----从下面的查询可以看出执行了两次硬解析----
31 SQL> select sql_text from v$sql where sql_text like 'select * from jack_exact where%'; 32 33 SQL_TEXT 34 -------------------------------------------------------------------------------- 35 select * from jack_exact where id=1 36 select * from jack_exact where id=3 37 ----查看trace文件，通过tkprof工具
[oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc out.txt aggregate=no sys=no----
38 SQL ID: fnggytkynxz04 39 Plan Hash: 4127630146 40 select * 41 from 42 jack_exact where id=1 43 44 45 call count cpu elapsed disk query current rows 46 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 47 Parse 1 0.00 0.00 0 1 0 0 48 Execute 1 0.00 0.00 0 0 0 0 49 Fetch 2 0.00 0.00 0 4 0 1 50 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 51 total 4 0.00 0.00 0 5 0 1 52 53 Misses in library cache during parse: 1 ---id=1，执行一次硬解析 54 Optimizer mode: ALL_ROWS 55 Parsing user id: 105 56 57 Rows Row Source Operation 58 ------- --------------------------------------------------- 59 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 60 61 ******************************************************************************** 62 63 SQL ID: 1n0paamkf7sup 64 Plan Hash: 4127630146 65 select * 66 from 67 jack_exact where id=3 68 69 70 call count cpu elapsed disk query current rows 71 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 72 Parse 1 0.00 0.00 0 1 0 0 73 Execute 1 0.00 0.00 0 0 0 0 74 Fetch 2 0.00 0.00 0 4 0 1 75 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 76 total 4 0.00 0.00 0 5 0 1 77 78 Misses in library cache during parse: 1 ----id=3，执行一次硬解析 79 Optimizer mode: ALL_ROWS 80 Parsing user id: 105 81 82 Rows Row Source Operation 83 ------- --------------------------------------------------- 84 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 85 86 ******************************************************************************** 87 88 SQL ID: fnggytkynxz04 89 Plan Hash: 4127630146 90 select * 91 from 92 jack_exact where id=1 93 94 95 call count cpu elapsed disk query current rows 96 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 97 Parse 1 0.00 0.00 0 0 0 0 98 Execute 1 0.00 0.00 0 0 0 0 99 Fetch 2 0.00 0.00 0 4 0 1 100 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 101 total 4 0.00 0.00 0 4 0 1 102 103 Misses in library cache during parse: 0 ----执行一次软解析 104 Optimizer mode: ALL_ROWS 105 Parsing user id: 105 106 107 Rows Row Source Operation 108 ------- --------------------------------------------------- 109 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 110 111 ********************************************************************************
2.3 cursor_sharing=similar的情况：
----将curor_sharing设置为similar----
1 SQL> alter session set cursor_sharing=similar; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_similar where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_similar where id=4; 16 17 ID NAME 18 ---------- ---------- 19 4 dd 20 21 SQL> select * from jack_similar where id=8; 22 23 no rows selected 24 ----下面查询中可以看到Oracle将SQL中的谓词条件用同一个名词的一个变量替代，尽管看起来是一样的，但是Oracle依然把它们作为两条SQL来处理----
25 SQL> select sql_text from v$sql where sql_text like 'select * from jack_similar where%'; 26 27 SQL_TEXT 28 -------------------------------------------------------------------------------- 29 select * from jack_similar where id=:"SYS_B_0" 30 select * from jack_similar where id=:"SYS_B_0" 31 select * from jack_similar where id=:"SYS_B_0" 32 33 SQL> alter session set sql_trace=false; 34 35 Session altered. 36 37 [oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc out.txt sys=no 38 39 TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 22 10:18:16 2013 40 41 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 42 ----从trace文件中可以清楚看到上面的结论----
43 SQL ID: 80chtmbbwpx49 44 Plan Hash: 1559066762 45 select * 46 from 47 jack_similar where id=:"SYS_B_0" 48 49 50 call count cpu elapsed disk query current rows 51 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 52 Parse 3 0.00 0.00 0 0 0 0 53 Execute 3 0.00 0.00 0 3 0 0 54 Fetch 5 0.00 0.00 0 10 0 2 55 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 56 total 11 0.01 0.01 0 13 0 2 57 58 Misses in library cache during parse: 3 ----进行三次查询，都各执行了一次硬解析。 59 Optimizer mode: ALL_ROWS 60 Parsing user id: 105 61 62 Rows Row Source Operation 63 ------- --------------------------------------------------- 64 1 TABLE ACCESS FULL JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 65 66 ********************************************************************************
对于SIMILAR的情况，如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时，如果谓词条件的值有变化，就将会产生一个新的子游标，而不是重用之前的SQL；如果谓词没有其他的执行计划可选择，则忽略谓词的值，重用之前的SQL。
上面的例子还不能足以说明该情况，接着下面的模拟：
----清楚一下shared_pool中的内容，否则会影响后面的输出----
1 SQL> alter system flush shared_pool; 2 3 System altered. 4 5 SQL> select * from jack_similar; 6 7 ID NAME 8 ---------- ---------- 9 1 aa 10 2 bb 11 3 cc 12 4 dd 13 14 SQL> insert into jack_similar values(1,'gg'); 15 16 1 row created. 17 18 SQL> commit; 19 20 Commit complete. 21 ----创建索引，并进行分析----
22 SQL> create index jack_similar_ind on jack_similar(id); 23 24 Index created. 25 SQL> exec dbms_stats.gather_table_stats(user,'jack_similar',cascade=>true); 26 27 PL/SQL procedure successfully completed. 28 29 SQL> select * from jack_similar; 30 31 ID NAME 32 ---------- ---------- 33 1 aa 34 2 bb 35 3 cc 36 4 dd 37 1 gg 38 39 SQL> alter session set cursor_sharing=similar; 40 41 Session altered. 42 43 SQL> alter session set sql_trace=true; 44 45 Session altered. 46 47 SQL> select * from jack_similar where id=1 and name='aa'; 48 49 ID NAME 50 ---------- ---------- 51 1 aa 52 53 SQL> select * from jack_similar where id=1 and name='gg'; 54 55 ID NAME 56 ---------- ---------- 57 1 gg 58 59 SQL> alter session set sql_trace=false; 60 61 Session altered. 62 ----在这里可以看到执行两次SQL查询，只进行了一个硬解析----
63 SQL> select sql_text from v$sql where sql_text like 'select * from jack_similar where%'; 64 65 SQL_TEXT 66 -------------------------------------------------------------------------------- 67 select * from jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 68 69 ----在trace文件也验证了这一点。----
----虽然name的值发生了改变，但是id的值没有发生变，而id上有索引的，name上没有索引，CBO认为这样的情况不会改变SQL的执行计划。
70 SQL ID: 10ku2kuy1sqaj 71 Plan Hash: 2730352089 72 select * 73 from 74 jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 75 76 77 call count cpu elapsed disk query current rows 78 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 79 Parse 1 0.00 0.00 0 0 0 0 80 Execute 1 0.00 0.00 0 0 0 0 81 Fetch 2 0.00 0.00 0 4 0 1 82 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 83 total 4 0.00 0.00 0 4 0 1 84 85 Misses in library cache during parse: 1 86 Optimizer mode: ALL_ROWS 87 Parsing user id: 105 88 89 Rows Row Source Operation 90 ------- --------------------------------------------------- 91 1 TABLE ACCESS BY INDEX ROWID JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1) 92 2 INDEX RANGE SCAN JACK_SIMILAR_IND (cr=2 pr=0 pw=0 time=17 us cost=1 size=0 card=2)(object id 75044) 93 94 ******************************************************************************** 95 96 SQL ID: 10ku2kuy1sqaj 97 Plan Hash: 2730352089 98 select * 99 from 100 jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 101 102 103 call count cpu elapsed disk query current rows 104 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 105 Parse 1 0.00 0.00 0 0 0 0 106 Execute 1 0.00 0.00 0 0 0 0 107 Fetch 2 0.00 0.00 0 4 0 1 108 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 109 total 4 0.00 0.00 0 4 0 1 110 111 Misses in library cache during parse: 0 112 Optimizer mode: ALL_ROWS 113 Parsing user id: 105 114 115 Rows Row Source Operation 116 ------- --------------------------------------------------- 117 1 TABLE ACCESS BY INDEX ROWID JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1) 118 2 INDEX RANGE SCAN JACK_SIMILAR_IND (cr=2 pr=0 pw=0 time=3 us cost=1 size=0 card=2)(object id 75044) 119 120 ********************************************************************************
2.4 cursor_sharing=force的情况
----设置cursor_sharing=force----
1 SQL> alter session set cursor_sharing=force; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_force where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_force where id=4; 16 17 ID NAME 18 ---------- ---------- 19 4 dd 20 21 SQL> select * from jack_force where id=1; 22 23 ID NAME 24 ---------- ---------- 25 1 aa 26 27 SQL> alter session set sql_trace=false; 28 29 Session altered. 30 ----从下面的查询中可以看出只进行了一次硬解析，而且使用了绑定变量----
31 SQL> select sql_text from v$sql where sql_text like 'select * from jack_force where%'; 32 33 SQL_TEXT 34 -------------------------------------------------------------------------------- 35 select * from jack_force where id=:"SYS_B_0" 36 37 ----查看trace文件内容----
38 [oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5551.trc aggregate=no sys=no 39 40 SQL ID: 38vy9d4quwdwk 41 Plan Hash: 1272021682 42 select * 43 from 44 jack_force where id=:"SYS_B_0" 45 46 47 call count cpu elapsed disk query current rows 48 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 49 Parse 1 0.01 0.01 0 1 0 0 50 Execute 1 0.00 0.00 0 1 0 0 51 Fetch 2 0.00 0.00 0 4 0 1 52 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 53 total 4 0.01 0.01 0 6 0 1 54 55 Misses in library cache during parse: 1 ----id=1的时候进行一次硬解析 56 Optimizer mode: ALL_ROWS 57 Parsing user id: 105 58 59 Rows Row Source Operation 60 ------- --------------------------------------------------- 61 1 TABLE ACCESS FULL JACK_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 62 63 ******************************************************************************** 64 65 SQL ID: 38vy9d4quwdwk 66 Plan Hash: 1272021682 67 select * 68 from 69 jack_force where id=:"SYS_B_0" 70 71 72 call count cpu elapsed disk query current rows 73 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 74 Parse 1 0.00 0.00 0 0 0 0 75 Execute 1 0.00 0.00 0 0 0 0 76 Fetch 2 0.00 0.00 0 3 0 1 77 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 78 total 4 0.00 0.00 0 3 0 1 79 80 Misses in library cache during parse: 0 ----id=4的时候进行0次硬解析，一次软解析 81 Optimizer mode: ALL_ROWS 82 Parsing user id: 105 83 84 Rows Row Source Operation 85 ------- --------------------------------------------------- 86 1 TABLE ACCESS FULL JACK_FORCE (cr=3 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 87 88 ******************************************************************************** 89 90 SQL ID: 38vy9d4quwdwk 91 Plan Hash: 1272021682 92 select * 93 from 94 jack_force where id=:"SYS_B_0" 95 96 97 call count cpu elapsed disk query current rows 98 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 99 Parse 1 0.00 0.00 0 0 0 0 100 Execute 1 0.00 0.00 0 0 0 0 101 Fetch 2 0.00 0.00 0 4 0 1 102 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 103 total 4 0.00 0.00 0 4 0 1 104 105 Misses in library cache during parse: 0 ----id=1时进行0次硬解析，一次软解析 106 Optimizer mode: ALL_ROWS 107 Parsing user id: 105 108 109 Rows Row Source Operation 110 ------- --------------------------------------------------- 111 1 TABLE ACCESS FULL JACK_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 112 113 ********************************************************************************
总结：force是在任何情况下，无条件重用SQL。
三、总结：
FORCE和SIMIALR最大的区别在于，FORCE会把所有的谓词用变量代替，并且不管变量的值如何，一律重用第一条SQL语句，而SIMILAR会根据谓词的不同，来重新选择SQL的执行计划。
如果一个系统，它存在变量绑定的问题，并且这种问题已经影响到了系统的性能，这时候可以考虑将参数cursor_sharing的值设置为SIMILAR或FORCE来改善这种局面，不过在改成SIMILAR或FORCE都可能带来一些Bug以及很多未知的东西，所以需要慎用。
最后需要说明一点，对于OLTP系统，如果绑定变量情况不好的话，也许可以考虑通过设置这个参数来缓解一下问题；对于是在OLAP系统上，这个参数应该设置成EXACT，并且不应该使用绑定变量，因为在OLAP系统中，SQL的解析对于SQL的执行来看，话费的代价几乎可以忽略，而正确的SQL执行计划才是OLAP数据库最需要关注的。
四、引用Reference
