cursor_sharing参数设置exact,similar,force的区别

川长思鸟来 2022-05-28 23:25 59阅读 0赞

[Oracle][]中为了提高sql的执行效率，需要减少硬解析，实现shared cursor共享，最常见的方法是使用绑定变量，但很多时候由于各种原因未能在开发初期使用绑定变量，对于减少硬解析的目的，退而求其次地方法是设置cursor\_sharing.

**1.准备环境**

**实验环境**

BALLON@PROD> select \* from v$version;

BANNER

\----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

**使用脚本插入数据后：**

BALLONTT@PROD> desc t;

Name Null? Type

\----------------------- -------- ----------------

ID VARCHAR2(5)

NAME NUMBER(38)

BALLONTT@PROD> select id,count(\*) from t group by id;

ID COUNT(\*)

\----- ----------

d 6

a 10000

b 20000

c 20

BALLONTT@PROD> create index ind\_id on t(id);

Index created.

**2.取值为exact时（默认）：**

BALLONTT@PROD> show parameter cursor\_sharing;

NAME TYPE VALUE

\------------------------------------ ----------- -----------------

cursor\_sharing string EXACT

BALLONTT@PROD> select count(\*) from t where id='b';

COUNT(\*)

\----------

20000

BALLONTT@PROD> select count(\*) from t where id='d';

COUNT(\*)

\----------

BALLONTT@PROD> select sql\_text,sql\_id,version\_count,executions from v$sqlarea

where sql\_text like 'select count(\*) from t where id=%';

SQL\_TEXT SQL\_ID VERSION\_COUNT EXECUTIONS

\---------------------------------------- ------------- ------------- ----------

select count(\*) from t where id='d' b0gfs7u9r55rv 1 1

select count(\*) from t where id='b' fqurbumy7bsg6 1 1

可以看到两条查询语句没有使用绑定变量，有各自对应的sql\_id，子游标数均为1个。两个sql查询没有任何关系。

查看两次sql的执行计划：

BALLONTT@PROD>select \* from table(dbms\_xplan.

display\_cursor('b0gfs7u9r55rv',0,'advanced'));

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

SQL\_ID b0gfs7u9r55rv, child number 0

\-------------------------------------

select count(\*) from t where id='d'

Plan hash value: 3666266488

\---------------------------------------------------------------------------

\----------------------------------------------------------------------------

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

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

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

|\* 2 | INDEX RANGE SCAN| IND\_ID | 14 | 28 | 1 (0)| 00:00:01 |

\----------------------------------------------------------------------------

第一次查询利用了索引。

BALLONTT@PROD> select \* from table(dbms\_xplan.display\_cursor('fqurbumy7bsg6',0,'advanced'));

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

SQL\_ID fqurbumy7bsg6, child number 0

\-------------------------------------

select count(\*) from t where id='b'

Plan hash value: 2966233522

\---------------------------------------------------------------------------

\---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 15 (100)| |

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

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

|\* 2 | TABLE ACCESS FULL| T | 19783 | 39566 | 15 (0)| 00:00:01 |

\---------------------------------------------------------------------------

第二次查询利用了全表扫描

我们在更直观地来看一下两次sql查询后的硬解析统计情况：

BALLONTT@PROD> select count(\*) from t where id='a';

COUNT(\*)

\----------

10000

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\---------------------------------------------------------------- ----------

parse time cpu 2133

parse time elapsed 4463

parse count (total) 54889

parse count (hard) 6579（硬解析数目）

parse count (failures) 52

BALLONTT@PROD> select count(\*) from t where id='c';

COUNT(\*)

\----------

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\---------------------------------------------------------------- ----------

parse time cpu 2134

parse time elapsed 4464

parse count (total) 54895

parse count (hard) 6580（硬解析数目）

parse count (failures) 52

硬解析数目再次加1

**因为数据的巨大差异性，导致了对两次查询有不同的执行计划，这也说明在cursor设置为exact时，两条sql语句如果存在一点不同，就不会共享cursor，而进行两次硬解析。**

**3.设置为force时**

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

BALLONTT@PROD> alter system set cursor\_sharing=force;

System altered.

BALLONTT@PROD> show parameter cursor\_sharing;

NAME TYPE VALUE

\------------------------- ------------ -------

cursor\_sharing string FORCE

清除一下share pool中已缓存的cursor

BALLONTT@PROD> alter system flush shared\_pool;

System altered.

查看硬解析情况：

BALLONTT@PROD> select count(\*) from t where id='b';

COUNT(\*)

\----------

20000

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\------------------------------------- ----------

parse time cpu 2163

parse time elapsed 4506

parse count (total) 55097

parse count (hard) 6668

parse count (failures) 52

BALLONTT@PROD> select count(\*) from t where id='d';

COUNT(\*)

\----------

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\----------------------------------------- ----------

parse time cpu 2164

parse time elapsed 4507

parse count (total) 55101

parse count (hard) 6669

parse count (failures) 52

硬解析加1了，这不应该呀！！

BALLONTT@PROD> select sql\_text,child\_number from v$sql where sql\_text like 'select count(\*) from t where id%';

SQL\_TEXT CHILD\_NUMBER

\---------------------------------------- ------------

select count(\*) from t where id='d' 0

select count(\*) from t where id='b' 0

可以看到并没有使用绑定变量，force的设置没有生效。

**解决办法**：应在设置cursor\_sharing前，执行两次下面语句：

alter system flush shared\_pool;

BALLONTT@PROD> alter system flush shared\_pool;

System altered.

BALLONTT@PROD> alter system flush shared\_pool;

System altered.

BALLONTT@PROD> alter system set cursor\_sharing=force;

System altered.

设置好了，接着进行sql测试

BALLONTT@PROD> select count(\*) from t where id='d';

COUNT(\*)

\----------

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\-------------------------------------- ----------

parse time cpu 2216

parse time elapsed 4572

parse count (total) 55867

parse count (hard) 6910

parse count (failures) 55

BALLONTT@PROD> select count(\*) from t where id='b';

COUNT(\*)

\----------

20000

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

\--------------------------------------- ----------

parse time cpu 2216

parse time elapsed 4572

parse count (total) 55869

parse count (hard) 6910

parse count (failures) 55

硬解析的次数没有发生变化

BALLONTT@PROD> select sql\_text,sql\_id,version\_count,executions from v$sqlarea

2 where sql\_text like 'select count(\*) from t where%';

SQL\_TEXT SQL\_ID VERSION\_COUN EXECUTIONS

\---------------------------------------------- ------------- ------------- -------------

select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 1 2

可以看到两次查询使用了绑定变量，将谓词的值用sys\_B\_0代替。该语句执行了两次，有一个child cursor（子游标）。

在来看一下两次查询语句的执行计划：

BALLONTT@PROD> select \* from table(dbms\_xplan.display\_cursor('g82ztj8p3q174',0,'advanced'));

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

SQL\_ID g82ztj8p3q174, child number 0

\-------------------------------------

select count(\*) from t where id=:"SYS\_B\_0"

Plan hash value: 3666266488

\----------------------------------------------------------------------------

\----------------------------------------------------------------------------

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

| 1 | SORT AGGREGATE | | 1 | 4 | | |

PLAN\_TABLE\_OUTPUT

\----------------------------------------------------------------------------------------------------

|\* 2 | INDEX RANGE SCAN| IND\_ID | 6 | 24 | 1 (0)| 00:00:01 |

\----------------------------------------------------------------------------

两次的查询使用了同一个执行计划：索引扫描。这就是force的设置。

对与参数cusor\_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值问题是相同的。
 *  **4.设置为similar时**
    
    当cursor\_sharing设置为SIMILAR的时候，[Oracle][]对没有使用绑定变量的SQL字面语句都会进行处理，将where条件后自动替换为绑定变量；并在执行语句时，对每一个条件设置值，都会生成一个新的child cursor子游标，匹配一个新的执行计划。
    
     
    
    同上面的force设置：
    
    BALLONTT@PROD> alter system flush shared\_pool;
    
    System altered.
    
    BALLONTT@PROD> alter system flush shared\_pool;
    
    System altered.
    
    BALLONTT@PROD> alter system set cursor\_sharing=similar;
    
    System altered.
    
    BALLONTT@PROD> show parameter cursor\_sharing;
    
    NAME TYPE VALUE
    
    \------------------------------------ ------------------------------- -----------------------
    
    cursor\_sharing string SIMILAR
    
     
    
    收集一下统计信息和直方图：
    
    BALLONTT@PROD> exec dbms\_stats.gather\_table\_stats('BALLONTT','T', cascade => true,METHOD\_OPT =>'FOR COLUMNS SIZE AUTO');
    
    PL/SQL procedure successfully completed.
    
    BALLONTT@PROD> select column\_name, num\_distinct, NUM\_BUCKETS, HISTOGRAM from dba\_tab\_col\_statistics
    
    2 where wner='BALLONTT' and table\_name='T';
    
    COLUMN\_NAME NUM\_DISTINCT NUM\_BUCKETS HISTOGRAM
    
    \------------------------------ ------------ ----------- ---------------
    
    ID 4 4 FREQUENCY
    
    NAME 10000 1 NONE
    
     
    
    进行测试：
    
    BALLONTT@PROD> select count(\*) from t where id='d';
    
    COUNT(\*)
    
    \----------
    
    6
    
    BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
    
     
    
    NAME VALUE
    
    \------------------------------------------- ----------
    
    parse time cpu 2412
    
    parse time elapsed 4792
    
    parse count (total) 62015
    
    parse count (hard) 8059
    
    parse count (failures) 64
    
     
    
    BALLONTT@PROD> select count(\*) from t where id='b';
    
    COUNT(\*)
    
    \----------
    
    20000
    
    BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
    
     
    
    NAME VALUE
    
    \----------------------------------------- ----------
    
    parse time cpu 2412
    
    parse time elapsed 4792
    
    parse count (total) 62017
    
    parse count (hard) 8060
    
    parse count (failures) 64
    
    硬解析次数加1
    
     
    
    BALLONTT@PROD> select sql\_text,sql\_id,child\_number,executions from v$sql where sql\_text like 'select count(\*) from t where id%';
    
    SQL\_TEXT SQL\_ID CHILD\_NUMBER EXECUTIONS
    
    \----------------------------------------- ---------------- ------------- ------------ 
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 0 1
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 1 1
    
    BALLONTT@PROD> select sql\_text,sql\_id,version\_count,executions from v$sqlarea where sql\_Text like 'select count(\*) from t where id%';
    
    SQL\_TEXT SQL\_ID VERSION\_COUNT EXECUTIONS
    
    \----------------------------------------------- ------------- ------------- --------- 
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 2 2
    
    使用了绑定变量，但是两次查询生成了两个子游标(child cursor)，即对应两个执行计划。
    
     
    
    为了更清楚地看到两次sql使用了两个不同的子游标，即对应着使用了两个执行计划，我们再来分步查询一次：
    
    BALLONTT@PROD> select count(\*) from t where id='d';
    
    COUNT(\*)
    
    \----------
    
    6
    
    BALLONTT@PROD> select sql\_text,sql\_id,child\_number from v$sql where sql\_text like '%select count(\*) from t where id%';
    
     
    
    SQL\_TEXT SQL\_ID CHILD\_NUMBER
    
    \----------------------------------------------- ------------- ------------- 
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 0
    
     
    
    查看执行计划：
    
    BALLONTT@PROD> select \* from table(dbms\_xplan.display\_cursor('g82ztj8p3q174',0,'advanced'));
    
     
    
    PLAN\_TABLE\_OUTPUT
    
    \----------------------------------------------------------------------------------------------------
    
    SQL\_ID g82ztj8p3q174, child number 0
    
    \-------------------------------------
    
    select count(\*) from t where id=:"SYS\_B\_0"
    
     
    
    Plan hash value: 3666266488
    
     
    
    \----------------------------------------------------------------------------
    
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
    \----------------------------------------------------------------------------
    
    | 0 | SELECT STATEMENT | | | | 1 (100)| |
    
    | 1 | SORT AGGREGATE | | 1 | 2 | | |
    
     
    
    PLAN\_TABLE\_OUTPUT
    
    \----------------------------------------------------------------------------------------------------
    
    |\* 2 | INDEX RANGE SCAN| IND\_ID | 1 | 2 | 1 (0)| 00:00:01 |
    
    \----------------------------------------------------------------------------
    
    执行计划为索引路径
    
     
    
    再次查询：
    
    BALLONTT@PROD> select count(\*) from t where id='b';
    
    COUNT(\*)
    
    \----------
    
    20000
    
    BALLONTT@PROD> select sql\_text,sql\_id,child\_number from v$sql where sql\_text like 'select count(\*) from t where id%'
    
    2 ;
    
     
    
    SQL\_TEXT SQL\_ID CHILD\_NUMBER
    
    \----------------------------------------------- ------------- ------------- 
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 0
    
    select count(\*) from t where id=:"SYS\_B\_0" g82ztj8p3q174 1
    
     
    
    查看子游标（chilid\_number=1）的执行计划：
    
    BALLONTT@PROD> select \* from table(dbms\_xplan.display\_cursor('g82ztj8p3q174',1,'advanced'));
    
     
    
    PLAN\_TABLE\_OUTPUT
    
    \----------------------------------------------------------------------------------------------------
    
    SQL\_ID g82ztj8p3q174, child number 1
    
    \-------------------------------------
    
    select count(\*) from t where id=:"SYS\_B\_0"
    
     
    
    Plan hash value: 2966233522
    
     
    
    \---------------------------------------------------------------------------
    
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
    \---------------------------------------------------------------------------
    
    | 0 | SELECT STATEMENT | | | | 15 (100)| |
    
    | 1 | SORT AGGREGATE | | 1 | 2 | | |
    
     
    
    PLAN\_TABLE\_OUTPUT
    
    \----------------------------------------------------------------------------------------------------
    
    |\* 2 | TABLE ACCESS FULL| T | 20081 | 40162 | 15 (0)| 00:00:01 |
    
    \---------------------------------------------------------------------------
    
    执行计划为全表扫描，生成了一个新的执行计划。
    
    **由此我们可以得到结论：**
    
     
    
     *  当cursor\_sharing设置为SIMILAR的时候，Oracle对没有使用绑定变量的SQL字面语句都会进行处理，将where条件后自动替换为绑定变量；
     *  在执行语句是，对每一个条件设置值，都会生成一个新的child cursor子游标，与父游标相对应。当一个语句输入的时候，如果之前存在过相同条件值的SQL子游标，就共享该子游标。否则生成一个新的child cursor，生成一个匹配的执行计划；
     *  SIMILAR是FORCE的进化版。在SIMILAR模式下，Oracle对游标共享的条件变得比较敏感。如果绑定变量值发生变化，就意味着执行计划可能存在不匹配的情况。所以索性Oracle对每一个新的值都bind peeking一下，生成执行计划。而执行计划游标的共享只在相同绑定变量的时候才发生。
     *  这个与FORCE相比，cursor\_sharing=SIMILAR的确缓解了由于bind peeking单次带来的执行计划不匹配问题。但是会引入两个新问题：
    
     
    
     
    
    1.  如果对应条件列的取值相对较少，这样生成执行计划的个数起码是可以控制的。如果是一种连续取值情况或者对应取值很多，必然引起parent cursor对应的child cursor数目增多，每次从child cursor列中遍历的时间增加，latch和pin发生的时间增多。这也是similar取值是一个常见的问题；
    2.  生成child cursor的标准不是是否执行计划相同，而是绑定变量值相同。这样如果数据分布较为平均，所有值对应的执行计划都是相同的。那么生成很多的子游标执行计划必然是相同的。这样又会带来性能和其他一些问题。
    
     *  在谓词条件的列后没有直方图时，其作用和force类似，会共享子游标（可以删除where条件后列上的直方图重复最后一步实验验证）
    
     
    
    Cursor\_sharing设置为similar，存在一些BUG，即使是force也同样有BUG存在。例如导致EXPDP的导出时间变成，物化视图的更新变慢等。Oracle 并不建议你设置此参数。在12C中ORACLE将废除similar的设置。

[Oracle]: http://www.linuxidc.com/topicnews.aspx?tid=12