当前位置:  数据库>oracle

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

    来源: 互联网  发布时间：2017-05-31

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

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

实验环境

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.

 

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 | 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 | | 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 (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 (failures) 52

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

 

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

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

可以看到两次查询使用了绑定变量，将谓词的值用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值问题是相同的。

    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,