初始化参数之cursor_sharing

最新推荐文章于 2017-05-19 10:49:50 发布

cmg3399

最新推荐文章于 2017-05-19 10:49:50 发布

阅读量118

点赞数

文章标签：数据库

一、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----
  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 ********************************************************************************

总结：当cursor_sharing=exact时，只有当SQL语句是完全一样的情况下才能被重用。

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