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

转自 http://blog.csdn.net/ballontt/article/details/17548991

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(*)

----------

  6

 

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

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

| 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 |    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

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

| 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    | 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(*)

----------

   20

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(*)

----------

    6

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(*)

----------

    6

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

 

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

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

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

|   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的设置。

 

 

ballontt
2013/12/25

---The End---

posted @ 2014-03-20 15:43  princessd8251  阅读(401)  评论(0编辑  收藏  举报