用户名:  密码:
兄弟在线   

标题:浅谈cursor_sharing取值对SQL共享的影响(下)

作者:佚名 来源:网络 时间:2013-09-12

[网站建设之]浅谈cursor_sharing取值对SQL共享的影响(下)


上篇我们介绍了Oracle SQL共享游标的机制,以及cursor_sharing参数EXACT取值作用。本篇我们继续介绍cursor_sharing参数的其他两个取值:FORCE和SIMILAR的实际含义。

  4、 FORCE——强制共享执行计划

  默认值EXACT的作用是不对非字面SQL绑定变量进行替换操作。而FORCE值和SIMILAR取值意味着Oracle需要对输入的SQL语句进行处理,首先就是对条件值进行绑定变量化,其次就是针对不同的取值采用不同的执行计划共享策略。

  当选择FORCE值的时候,意味着Oracle会对SQL字面值进行绑定变量处理。一个语句形成父游标和仅有的一个子游标。子游标执行计划通过Oracle binds peeking技术实现,以后所有类似形态的SQL都是先共享。

  SQL> alter system flush shared_pool;

  System altered

  SQL> alter session set cursor_sharing='FORCE';

  Session altered

  SQL> select name, value from v$parameter where name='cursor_sharing';

  NAME                VALUE

  -------------------- --------------------

  cursor_sharing      FORCE

  将当前会话的cursor_sharing设置为force,同时清空library cache。之后使用三条SQL语句进行试验。

  SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='D';

  COUNT(*)

  ----------

  10000

  SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='P';

  COUNT(*)

  ----------

  8000

  SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='G';

  COUNT(*)

  ----------

  5

  如果在EXACT取值的时候,三个执行语句一定会生成三个父游标和三个子游标的。每一个游标对应一个单独的执行计划。第一和第二条SQL对应全表扫描FTS方案较好,而第三条SQL显然索引路径较优。我们看看在FORCE取值的时候,生成计划情况如何呢?

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_force_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_force_demo */ count(*) from t where id1=:"SYS_B_0"    24vkux5z1rsjy            1         3

  SQL> select sql_id, child_number, sql_text from v$sql where sql_id='24vkux5z1rsjy';

  SQL_ID       CHILD_NUMBER SQL_TEXT

  ------------- ------------ --------------------------------------------------------------------------------

  24vkux5z1rsjy           0select /*+ cursor_sharing_force_demo */ count(*) from t where id1=:"SYS_B_0"

  此时,我们观察到三次执行之后SQL游标共享情况。首先,三次的SQL语句从字面值上完全不同,差异只是存在在条件id1取值上。如果在cursor_sharing为EXACT模式下,是不能实现游标共享的。设置为FORCE之后,我们发现Oracle自动将id1=后面的条件替换为绑定变量。三次SQL调用均使用相同的父游标,而子游标只存在一个,意味著三次调用均是使用这个唯一的子游标。一个子游标对应一个执行计划,三个SQL使用相同的执行计划。

  我们使用抽取手段抽取出执行计划,如下:

  SQL> select * from table(dbms_xplan.display_cursor('24vkux5z1rsjy',0,'advanced'));

  PLAN_TABLE_OUTPUT

  --------------------------------------------------------------------------------

  SQL_ID 24vkux5z1rsjy, child number 0

  -------------------------------------

  select /*+ cursor_sharing_force_demo */ count(*) from t where

  id1=:"SYS_B_0"

  Plan hash value: 2966233522

  ---------------------------------------------------------------------------

  | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

  ---------------------------------------------------------------------------

  |  0 | SELECT STATEMENT  |     |      |      |    9 (100)|         |

  |  1 | SORT AGGREGATE   |     |    1 |    2 |           |         |

  |* 2 |  TABLE ACCESS FULL| T   | 10000 | 20000 |    9 (12)| 00:00:01 |

  ---------------------------------------------------------------------------

  Query Block Name / Object Alias (identified by operation id):

  -------------------------------------------------------------

  1 - SEL$1

  2 - SEL$1 / T@SEL$1

  Peeked Binds (identified by position):

  --------------------------------------

  1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'D'

  Predicate Information (identified by operation id):

  ---------------------------------------------------

  2 - filter("ID1"=:SYS_B_0)

  49 rows selected

  注意,这个执行计划进行的是数据表的全表扫描。三次调用中,id1取定D\P因为数据量大,都可以使用FTS。但是G取值量少,应该使用索引路径较为合适。所以,在第一次生成执行计划之后,Oracle都会使用该执行计划作为相同SQL结构的语句计划。

  这类问题很类似于大名鼎鼎的bind peeking。在执行计划中,我们也的确看到了bind peeking信息,说明其中是使用‘D’值peeking出的执行计划。

  此处,我们已经可以知道FORCE取值的效果,当cursor_sharing参数选择FORCE的时候:

  ü       Oracle对输入的SQL值,会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时,会进行cursor sharing共享游标;

  ü       在第一次进行自动替换绑定变量的时候,Oracle会依据bind peeking取值,获取到一个执行计划,对应成子游标;

  ü       在以后的SQL语句中,只要出现父游标可共享的情况,Oracle都会强制使用生成的唯一子游标进行sharing。不去在乎是不是对SQL是最优的执行计划;

  FORCE取值的规则思想很简单,对SQL语句进行强制的绑定变量替换。使用第一次的bind peeking值生成执行计划,之后全部使用这个执行计划。这种方式实现了游标共享,避免出现大量的library cache硬解析,限制一个父游标的version_count数量。

  如果这种SQL语句本身是“Good SQL”,也就是条件列分布比较平均,没有出现过大的偏移分布。我们认为这种FORCE是很有益的。但是如果数据列分布不平均,这样借用第一次输入的bind peeking生成并且共享执行计划就很成问题。我们说,在cursor_sharing取定FORCE遇到的潜在问题,和我们使用绑定变量时候使用的bind peeking值问题是相同的。

  5、SIMILAR——另一个极端

  刚刚我们讨论了FORCE。在FORCE下,问题是很简单的:进行绑定变量替换,共享全部游标。但是这样对于数据分布不均衡的条件列来说,是存在很多问题的。实际环境中会出现SQL性能时好时坏的情况。作为另一个极端,我们设置SIMILAR取值。

  SQL> alter session set cursor_sharing='SIMILAR';

  Session altered

  SQL> show parameter cursor_sharing;

  NAME                                TYPE       VALUE

  ------------------------------------ ----------- ------------------------------

  cursor_sharing                      string     EXACT

  SQL> select name, value from v$parameter where name='cursor_sharing';

  NAME                VALUE

  -------------------- --------------------

  cursor_sharing      SIMILAR

  为了更容易看清现象,我们使用逐步试验的方法:

  --Invoke SQL 1

  SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='D';

  COUNT(*)

  ----------

  10000

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y            1         1

  第一次调用SQL语句,使用条件值D。在library cache中生成了父子游标,而且同FORCE一样,进行了绑定变量替换。我们抽出执行计划进行查看。

  SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',0,'advanced'));

  PLAN_TABLE_OUTPUT

  --------------------------------------------------------------------------------

  SQL_ID 0s63s6sjytz4y, child number 0

  -------------------------------------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where

  id1=:"SYS_B_0"

  Plan hash value: 2966233522

  ---------------------------------------------------------------------------

  | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

  ---------------------------------------------------------------------------

  |  0 | SELECT STATEMENT  |     |      |      |    9 (100)|         |

  |  1 | SORT AGGREGATE   |     |    1 |    2 |           |         |

  |* 2 |  TABLE ACCESS FULL| T   | 10000 | 20000 |    9 (12)| 00:00:01 |

  ---------------------------------------------------------------------------

  Peeked Binds (identified by position):

  --------------------------------------

  1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'D'

  Predicate Information (identified by operation id):

  ---------------------------------------------------

  49 rows selected

  对子游标(child_number=0)而言,使用D进行bind peeking后,生成全表扫描的执行计划。下面进行第二次调用:

  SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='G';

  COUNT(*)

  ----------

  5

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y            2         2

  在使用一个新值G的情况下,生成了一个新的子游标(version_count=2)。我们抽取出该执行计划进行查看。

  SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',1,'advanced'));

  PLAN_TABLE_OUTPUT

  --------------------------------------------------------------------------------

  SQL_ID 0s63s6sjytz4y, child number 1

  -------------------------------------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where

  id1=:"SYS_B_0"

  Plan hash value: 555228874

  -------------------------------------------------------------------------------

  | Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time    |

  -------------------------------------------------------------------------------

  |  0 | SELECT STATEMENT |          |      |      |    1 (100)|         |

  |  1 | SORT AGGREGATE  |          |    1 |    2 |           |         |

  |* 2 |  INDEX RANGE SCAN| IDX_T_ID1 |    5 |   10 |    1  (0)| 00:00:01 |

  -------------------------------------------------------------------------------

  Peeked Binds (identified by position):

  --------------------------------------

  1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'G'

  Predicate Information (identified by operation id):

  ---------------------------------------------------

  49 rows selected

  在使用一个新的变量值G的情况下,Oracle生成了一个新的游标执行计划作为对应。新生成的执行计划是使用索引路径。下面进行第三次调用。

  SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='D';

  COUNT(*)

  ----------

  10000

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y            2         3

  第三次执行,我们选择了第一次执行使用过的D值。此时,我们发现executions次数增加一次,但是version_count版本数量没有增加。可以知道使用了第一次生成的child_number=0的子游标执行计划。也就是说,当使用相同的绑定变量值的时候,Oracle会共享子游标。

  第四次执行,使用一个新的对应值。

  SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='P';

  COUNT(*)

  ----------

  8000

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y            3         4

  SQL> select sql_text,sql_id, child_number, LAST_LOAD_TIME from v$sql where sql_id='0s63s6sjytz4y';

  SQL_TEXT                                                                        SQL_ID       CHILD_NUMBER LAST_LOAD_TIME

  -------------------------------------------------------------------------------- ------------- ------------ --------------------------------------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           0 2011-07-30/19:25:31

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           1 2011-07-30/19:26:40

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           2 2011-07-30/19:29:42

  对于一个新的绑定变量取值P,Oracle进行bind peeking之后生成了一个新的子游标(child_number=2)与之对应。也就是生成了一个新的执行计划:

  SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',2,'advanced'));

  PLAN_TABLE_OUTPUT

  --------------------------------------------------------------------------------

  SQL_ID 0s63s6sjytz4y, child number 2

  -------------------------------------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where

  id1=:"SYS_B_0"

  Plan hash value: 2966233522

  ---------------------------------------------------------------------------

  | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

  ---------------------------------------------------------------------------

  |  0 | SELECT STATEMENT  |     |      |      |    9 (100)|         |

  |  1 | SORT AGGREGATE   |     |    1 |    2 |           |         |

  |* 2 |  TABLE ACCESS FULL| T   | 8000 | 16000 |    9 (12)| 00:00:01 |

  ---------------------------------------------------------------------------

  Peeked Binds (identified by position):

  --------------------------------------

  1 - :SYS_B_0 (VARCHAR2(30), CSID=852):'P'

  49 rows selected

  虽然child_number=2的执行计划也是使用全表扫描的执行计划,本质上同child_number=0的性质相同。但是Oracle没有进行共享,而是重新为这个bind peeking生成了一个新的子游标。

  下面继续执行SQL:

  SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='A';

  COUNT(*)

  ----------

  10

  SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

  SQL_TEXT                                                                        SQL_ID       VERSION_COUNT EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y            4         5

  SQL> select sql_text,sql_id, child_number, LAST_LOAD_TIME,executions from v$sql where sql_id='0s63s6sjytz4y';

  SQL_TEXT                                                                        SQL_ID       CHILD_NUMBER LAST_LOAD_TIME                        EXECUTIONS

  -------------------------------------------------------------------------------- ------------- ------------ -------------------------------------- ----------

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           0 2011-07-30/19:25:31                            2

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           1 2011-07-30/19:26:40                            1

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           2 2011-07-30/19:29:42                            1

  select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0"  0s63s6sjytz4y           3 2011-07-30/19:32:12                            1

  根据一个新的绑定变量值A,Oracle又为其生成了一个新的执行计划。

  至此,我们可以初步猜出SIMILAR的特征,相对于FORCE取值带来的问题,SIMILAR往灵活的方向前进了一步:

  ü       当cursor_sharing设置为SIMILAR的时候,Oracle对没有使用绑定变量的SQL字面语句都会进行处理,将where条件后自动替换为绑定变量;

  ü       在执行语句是,对每一个条件设置值,都会生成一个新的child cursor子游标,与父游标相对应。也就意味着对每一个语句,都会发生一次隐式的bind peeking动作;

  ü       当一个语句输入的时候,如果之前存在过相同条件值的SQL子游标,就共享该子游标。否则生成一个新的child cursor,生成一个匹配的执行计划;

  SIMILAR是FORCE的进化版。在SIMILAR模式下,Oracle对游标共享的条件变得比较敏感。如果绑定变量值发生变化,就意味着执行计划可能存在不匹配的情况。所以索性Oracle对每一个新的值都bind peeking一下,生成执行计划。而执行计划游标的共享只在相同绑定变量的时候才发生。

  这个与FORCE相比,cursor_sharing=SIMILAR的确缓解了由于bind peeking单次带来的执行计划不匹配问题。但是会引入两个新问题:

  ü       如果对应条件列的取值相对较少,这样生成执行计划的个数起码是可以控制的。如果是一种连续取值情况或者对应取值很多,必然引起parent cursor对应的child cursor数目增多,每次从child cursor列中遍历的时间增加,latch和pin发生的时间增多。这也是similar取值是一个常见的问题;

  ü       生成child cursor的标准不是是否执行计划相同,而是绑定变量值相同。这样如果数据分布较为平均,所有值对应的执行计划都是相同的。那么生成很多的子游标执行计划必然是相同的。这样又会带来性能和其他一些问题。

  6、结论

  cursor_sharing的取值和参数是Oracle library cache中管理生成乃至共享执行计划的重要参数。EXACT值是默认值,实现了直接使用字面SQL不开启转变绑定变量的功能。

  而FORCE和SIMILAR取值却开启了字面转绑定变量的功能。在这两个模式下,Oracle会自动的将where后面的条件替换为绑定变量,以增加SQL共享的概率。具体实现sharing的方式上,FORCE和SIMILAR取值又有所差异。

  FORCE的sharing原则是共享一切,只生成一个子游标,之后所有都去共享这个子游标的执行计划。随之而来的就是bind peeking问题风险。

  而SIMILAR过于谨慎,对每一个SQL都进行类似bind peeking操作。对每个可能取值都生成单独的子游标执行计划。相同的输入共享相同的执行计划。这个虽然避免了bind peeking问题,却带来了新的多version count问题。

  笔者认为:从EXACT到FORCE到SIMIlAR,到Oracle 11g中推出的ACS(Adaptive Cursor Sharing),Oracle一直试图去实现cursor sharing的自动化和高效化。过去,只能通过手工显示绑定变量来实现SQL共享最大化的目标。而手工书写的大部分SQL由于字面值的原因很难共享。cursor_sharing参数的作用就是进行这方面的尝试,虽然从目前看还是有一些问题,但是已经进行了有益的尝试。

  Oracle 11g中推出的ACS自适应游标,将游标共享的标准从SQL字面值相同,绑定变量Peeking值相同,拓展到执行计划相同。在不断的自适应尝试过程中,Oracle ACS最终会确定适合的共享方案和执行计划。


编辑:admin 总点击 [1708]   评论  0 查看评论
上一篇:Oracle数据库中文件加密详解
下一篇:Oracle10安装apex3.2步骤
【关闭窗口】
您可能感兴趣的文章
我要评论
          
评论标题:   可以输入250
 
验证数字: 8 + 8 =
兄弟友情提示
· 请自觉遵守国家有关法律、法规,尊重网上道德。
· 兄弟在线坚决抵制不良言行,违者文责自负。
· 如果文章有版权或其他问题等,请联系我们,我们会尽快处理。
· 文章注名来自网络的旨在传播共享信息,不做其它用途;注名原创的本站支持原创,但不代表同意其观点。
· 兄弟在线拥有管理用户与其文章和评论的一切权利,并有权在网站内转载或引用。
兄弟在线
兄弟热门文章
兄弟推荐文章
兄弟站内搜索

兄弟感兴趣的文章
兄弟最新影视