浅谈cursor_sharing 取值对SQL共享的影响

刺骨的言语ヽ痛彻心扉 2022-06-07 03:58 146阅读 0赞

在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  
  
使用脚本生成数据表数据

SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));  
  
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\_sharing  
  
NAME                     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取值的时候，生成计划情况如何呢？

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> 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 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最终会确定适合的共享方案和执行计划。