在Oracle中，用户应用输入的SQL语句要进行所谓的Parse解析过程，用于生成执行计划，这也就是Query Optimizer的主要工作。在Parse中，有两种具体类型，被称为“hard parse”（硬解析）和“Soft parse”（软解析）
“实现执行计划shared cursor共享，减少硬解析”是我们OLTP系统优化一个重要方向。但是，让Oracle真正实现SQL共享不是一件容易的事情，受到很多其他因素的影响。最常用的方式是使用绑定变量，让SQL字面值保持一致。如果应用端没有使用绑定变量，一种做法是设置系统参数cursor_sharing，将SQL语句中的条件进行绑定变量替换。本篇将从cursor_sharing可选值含义入手，讨论分析几种取值的确切含义和应用场景。以及为什么很多资料中都是对cursor_sharing设置望而却步
1.实验环境
我们在Oracle 11g下准备一个相对偏值的数据表。
SQL> select * from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
在Oracle 11g里，默认cursor_sharing取值为EXACT，表示不开启SQL字面取值绑定变量替换功能。
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
使用脚本生成数据表数据
Table created.
SQL> create index idx_t_id1 on t(id1);
Index created.
SQL> select object_id from dba_objects where owner='ZYSCM' and object_name='T';
OBJECT_ID
----------
170516
SQL> select id1, count(*) from t group by id1;
ID1 COUNT(*)
---------- ----------
P 11
D 22
A 7
SQL>
2、 统计量收集
这里单独谈谈统计量收集的问题。在Oracle统计量中，通常选择直方图histogram进行偏度描述。
注意：在Oracle 9i中，直方图默认使用dbms_stats是不会收集的，需要手工的制定method_opts参数。在Oracle 10g之后，使用“column auto”作为method_opts参数的默认取值
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where owner='ZYSCM' and table_name='T';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
ID1 3 1 NONE
ID2 3 1 NONE
ID3 3 1 NONE
SQL>
注意，默认是没有生成直方图的。主要原因在于需要使用一次id1作为条件列。
//使用一次条件列；
SQL> select count(*) from t where id1='D';COUNT(*)
----------
22
//重新收集一下统计量；
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');PL/SQL procedure successfully completed.
//发现统计量收集
SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where owner='ZYSCM' and table_name='T';COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
ID1 3 3 FREQUENCY
ID2 3 1 NONE
ID3 3 1 NONE
SQL>
当我们使用过一次id1条件之后，再次手动统计量，使用默认的auto参数，就生成id1列的频度直方图。
这里也就揭示了Oracle在收集统计量直方图auto选项的含义。当我们指定auto之后，Oracle会自动判断是否对数据列生成直方图、生成直方图bullet的个数。如果这个列从来就没有出现在SQL条件列中，也就不会被收集直方图。
3、EXACT——不进行条件列替换
EXACT是cursor_sharing参数的默认选项，表示含义是不进行SQL条件自动绑定变量替换。
SQL> show parameter cursor_sharingNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> alter system flush shared_pool;
System altered.
我们发出两句SQL，分别使用数据取值差异很大的id1值。
SQL> select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P';COUNT(*)
----------
11
SQL> select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='A';
COUNT(*)
----------
7
此时，父子游标library cache中情况如下：
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ cursor_sharing_exact_demo */%';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------- ----------- ----------- ---------
select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P' 1fu450v8ddmv9 1 1
select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='A' 16jfmsdnc6as0 1 1
在EXACT下，不会发生SQL字面值改写的情况。如果两个SQL的其他部分相同，只是where条件的取值有差异，Oracle是会将这两个语句作为两个单独SQL进行硬解析，分别生成执行计划。
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> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string FORCE
将当前会话的cursor_sharing设置为force，同时清空library cache。之后使用三条SQL语句进行试验。
SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='D';COUNT(*)
----------
22
SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='P';
COUNT(*)
----------
11
SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='A';
COUNT(*)
----------
7
如果在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" 2yczsvd6tcjuj 1 3
此时，我们观察到三次执行之后SQL游标共享情况。首先，三次的SQL语句从字面值上完全不同，差异只是存在在条件id1取值上。如果在cursor_sharing为EXACT模式下，是不能实现游标共享的。设置为FORCE之后，我们发现Oracle自动将id1=后面的条件替换为绑定变量。三次SQL调用均使用相同的父游标，而子游标只存在一个，意味著三次调用均是使用这个唯一的子游标。一个子游标对应一个执行计划，三个SQL使用相同的执行计划。
5、SIMILAR——另一个极端
刚刚我们讨论了FORCE。在FORCE下，问题是很简单的：进行绑定变量替换，共享全部游标。但是这样对于数据分布不均衡的条件列来说，是存在很多问题的。实际环境中会出现SQL性能时好时坏的情况。作为另一个极端，我们设置SIMILAR取值。
SQL> alter system set cursor_sharing='SIMILAR';System altered.
SQL> show parameter cursor_sharing;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
SIMILAR
为了更容易看清现象，我们使用逐步试验的方法：
--Invoke SQL 1
SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='P';COUNT(*)
----------
11
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"
f0rpwa1ja706p 1 1
第一次调用SQL语句，使用条件值D。在library cache中生成了父子游标，而且同FORCE一样，进行了绑定变量替换。下面进行第二次调用：
SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='A';
COUNT(*)
----------
7
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text f0rpwa1ja706p 2 2
like '%/*+ cursor_sharing_force_demo */%'
在使用一个新值A的情况下，生成了一个新的子游标（version_count=2）
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最终会确定适合的共享方案和执行计划。
扫一扫
2508
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
