cursor_sharing的妙用

转载 2011年02月24日 23:46:00

cursor_sharing的妙用

Oracle 2009-12-28 10:17:16 阅读100 评论0   字号: 订阅

OLTP系统中,我们总是希望使用绑定变量sql语句共享在library cache中,Oracle将根据LRU算法将该语句的相关信息保存在library cache中,这样只有在sql语句第一次被加载时会发生hard parse,之后如果sql语句在library cache中,将会发生fast parse或者soft parse,就不用每次都重新生成解析树和执行计划。

 然而,在某些情况下,我们可能又不想使用绑定变量。比如:如果 sql语句的where条件之后的列值在表中分布非常不均匀,在条件是某些值的情况下走全表扫描成本比较低,而另外一些情况下走索引成本比较低,在使用绑定变量的情况下,由于bind peeking技术,在fast parse或soft parse的情况下将会使用第一次硬解析时的执行计划而不会生成新的执行计划,这样将使某些查询陷入困境。

下文中介绍的hints将能够在cursor_sharing=similar的情况下强制不使用绑定变量:

1. 正常情况下 如果启用了similar ,sql语句会强制使用绑定变量

session 1

SQL>alter session set cursor_sharing=similar;

会话已更改。

SQL>select * from test where id=1;

        ID

        1

SQL>select * from test where id=2;

        ID

         2

session 2

SQL>select sql_text,hash_value from v$sqlarea where sql_text like 'select * from test where id%' order by first_load_time desc;

SQL_TEXT

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

HASH_VALUE

select * from test where id=:"SYS_B_0"

 345325898

 

2.如果使用了hint CURSOR_SHARING_EXACT,将强制不使用绑定变量

session 1

SQL>select /*+ CURSOR_SHARING_EXACT */ * from test where id=1;

        ID

        1

SQL>select /*+ CURSOR_SHARING_EXACT */ * from test where id=2;

        ID

        2

SQL>select /*+ CURSOR_SHARING_EXACT */ * from test where id=3;

        ID

         3

SQL>

session 2

SQL>select sql_text,hash_value from v$sqlarea where sql_text like 'select /*+ CURSOR_SHARING_EXACT */ * from test where id%' order by first_load_time desc;

SQL_TEXT

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

HASH_VALUE

select /*+ CURSOR_SHARING_EXACT */ * from test where id=3

select /*+ CURSOR_SHARING_EXACT */ * from test where id=2

select /*+ CURSOR_SHARING_EXACT */ * from test where id=1

    另外,在cursor_sharing=force的情况下,使用该hints也会强制不使用绑定变量。

 

Turn off bind peeking - (_optim_peek_user_binds=false).

 

 

我们先看看在表没有分析无统计数据情况下的表现 

SQL> alter session set cursor_sharing = similar;

 

Session altered.

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4948

parse time elapsed                                                    4468

parse count (total)                                                   170148

parse count (hard)                                                    1619  (硬分析次数)

parse count (failures)                                                80

 

SQL> select count(*) from t where object_id = 1000;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4948

parse time elapsed                                                     4468

parse count (total)                                                  170172

parse count (hard)                                                     1620

parse count (failures)                                                   80

 

SQL> /

 

NAME                                                                  VALUE

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

parse time cpu                                                         4948

parse time elapsed                                                     4468

parse count (total)                                                  170176

parse count (hard)                                                     1620

parse count (failures)                                                   80

 

SQL> select count(*) from t where object_id = 1000;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4948

parse time elapsed                                                     4468

parse count (total)                                                  170178

parse count (hard)                                                     1620

parse count (failures)                                                   80

 

SQL> select count(*) from t where object_id = 1001;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4948

parse time elapsed                                                     4468

parse count (total)                                                  170180

parse count (hard)                                              1620(即使object_id发生变化依然没有硬解析)

parse count (failures)                                                   80

 

我们再来看分析表和字段信息后的表现

SQL> analyze table t1 compute statistics for table for columns object_id;

 

Table analyzed.

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4973

parse time elapsed                                                     4495

parse count (total)                                                  170982

parse count (hard)                                                     1640

parse count (failures)                                                   80

 

SQL> select count(*) from t1 where object_id = 5000;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4973

parse time elapsed                                                     4495

parse count (total)                                                  170984

parse count (hard)                                                     1641

parse count (failures)                                                   80

  

SQL> select count(*) from t1 where object_id = 5000;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

NAME                                                                  VALUE

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

parse time cpu                                                         4973

parse time elapsed                                                     4495

parse count (total)                                                  171008

parse count (hard)                                                     1641 (重复执行没发生变化)

parse count (failures)                                                   80

 

SQL> select count(*) from t1 where object_id = 5001;

 

   COUNT(*)

----------

          0

 

SQL> select name,value from v$sysstat where name like '%parse%';

  

NAME                                                                  VALUE

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

parse time cpu                                                         4973

parse time elapsed                                                     4495

parse count (total)                                                  171010

parse count (hard)                                                 1642 (当object_id变化的时候产生硬分析)

parse count (failures)                                                   80

 

SQL>

 

SQL> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t1 where%';

 

SQL_TEXT

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

CHILD_NUMBER

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

select count(*) from t1 where object_id = :"SYS_B_0"

            0

 

select count(*) from t1 where object_id = :"SYS_B_0"

            1

 solaris环境,将CURSOR_SHARING参数设置为SIMILAR后产生的bug。

 

系统在修改CURSOR_SHARING之前,很少出现ORA-7445错误,但是自从修改了SIMILAR之后,平均每天要出现3至5次的ORA-7445错误。

其中包括:

Bug# 3055216:Dump possible in lxsCntXExc。

Bug# 3118504:Dump possible in ldxmfn using TO_CHAR。

Bug# 4371571:Dump (ldxmfn) can occur。

Bug# 3453408:Intermittent dump (in lxkLikeMBNS) possible from LIKE '%' from JDBC。

Bug 2784796:Ora-00001:Unique Constraint (Perfstat.Stats$Sql_Summary_Pk) Violated。

虽然不能保证上面列出的这些bug全部都是CURSOR_SHARING设置为SIMILAR造成的,但是这个数据库在修改这个参数以前,只出现过一次Bug# 3453408中描述的错误。其他错误都是修改参数后第一次出现。而且,第一个错误和最后一个错误基本上可以断定确实是由于SIMILAR参数引起的。

从这点也可以看出,无论是FORCE参数还是改进后的SIMILAR参数,都是解决绑定变量的替代方式,这些方式都可能带来一些bug以及很多未知的东西。只有有可能就应该通过修改程序的方法去设置绑定变量。

 

 

cursor_sharing=force带来的后果

cursor_sharing=force带来的后果

把参数cursor_sharing=exact 该为cursor_sharing=force后会极大的提高非共享的动态sql语句的执行性能。(oracle自动通过变量绑定实现)

如:

select * from heat_handle where rder = 1;

select * from heat_handle where rder = 2;

oracle认为这两句是一样的,可以重用。

首先oracle 把select * from heat_handle where rder = 1;

改为select * from heat_handle where rder = :"SYS_B0";

然后用select * from heat_handle where rder = :"SYS_B0";的执行计划来

执行select * from heat_handle where rder = 2;

由此引起的问题:

把参数cursor_sharing=exact 该为cursor_sharing=force后

下面的语句

SELECT  RGSTJ_13.worker,

        (FLOOR(SUM(RGSTJ_13.T_TIME1)/60) + MOD(SUM(RGSTJ_13.T_TIME1),60)/100)

FROM WELDPR.RGSTJ_13

group by RGSTJ_13.worker;

执行后:在库缓存中的sql语句是

SELECT worker,

       ((FLOOR(SUM(RGSTJ_13.T_TIME1)/:"SYS_B_0"cursor_sharing的妙用 - suny628 - suny628的博客  

       +

       MOD(SUM(RGSTJ_13.T_TIME1),:"SYS_B_1"cursor_sharing的妙用 - suny628 - suny628的博客/:"SYS_B_2"cursor_sharing的妙用 - suny628 - suny628的博客)  

FROM WELDPR.RGSTJ_13

group by RGSTJ_13.worker;

当在次执行

SELECT  RGSTJ_13.worker,

        (FLOOR(SUM(RGSTJ_13.T_TIME1)/60) + MOD(SUM(RGSTJ_13.T_TIME1),60)/100)

FROM WELDPR.RGSTJ_13

group by RGSTJ_13.worker;

语句时提示错误

ORA-01006 bind variable not exits

因为已经把原来的sql语句中的60,60,100用:"SYS_B_0",:"SYS_B_1",:"SYS_B_2"绑定变量给替换了,所以报错了。

改正方法:

新建一个函数:f_10_60

1        CREATE or REPLACE FUNCTION F_10_60(A NUMBER)

2        return number IS

  2  I NUMBER(14,2);

  3  BEGIN

  4   RETURN (FLOOR(a/60) + MOD(a,60)/100);

  5* end;

SQL> /

函数已创建。

然后sql改为:

SELECT  RGSTJ_13.worker,f_10_60(SUM(RGSTJ_13.T_TIME1))

FROM WELDPR.RGSTJ_13 group by RGSTJ_13.worker;

再次反复执行就不会出问题了。

注:可以使用下面语句查询库缓存中的sql源码。

SELECT SQL_TEXT, EXECUTIONS FROM sys.V$SQL WHERE sql_text like '%RGSTJ_13%' AND EXECUTIONS>1;

EXECUTIONS:为该语句执行的次数。

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

声明:本篇实验思路感谢dbsnake老师指点。   在Oracle中,用户应用输入的SQL语句要进行所谓的Parse解析过程,用于生成执行计划,这也就是Query Optimizer的主要工作...

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

上篇我们介绍了Oracle SQL共享游标的机制,以及cursor_sharing参数EXACT取值作用。本篇我们继续介绍cursor_sharing参数的其他两个取值:FORCE和SIMILAR的实...

游标共享之cursor_sharing=EXACT

cursor sharing用来告诉oracle什么情况下可以共享游标,即SQL重用。oracle默认cursor_sharing 是exact  指的是SQL语句必须绝对一样的情况下才能共享游标,否...

使用TTS实现Oracle跨版本迁移

TTS实现数据库迁移,具有速度快、支持跨平台和跨版本等优点。本文记录了用TTS从10g single迁移到11g RAC的过程。Source数据库版本和字符集设置:SQL> select * from...

Goldengate 使用definition file完成异构表同步

goldengate默认只是按照创建表时,列的顺序来执行数据的复制过程。所以当两端的表有不同的name、length、type、顺序的时候,goldengate不会识别,会发生错误的现象。这个时候就需...

初始化参数之cursor_sharing

原帖地址http://www.cnblogs.com/Richardzhu/archive/2013/01/21/2869837.html 一、Cursor_sharing简介: ...

Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing: ACS) 说明

一.自适应游标共享(Adaptive Cursor Sharing) 说明 1.1 ACS概述绑定变量使Oracle DB 可以为多条SQL 语句共享单个游标,以减少分析SQL 语句所使用的共享内存量...

探秘oracle11g acs (adaptiver cursor sharing)

一、什么是ACS(adaptiver cursor sharing) Oracle通过绑定变量技术解决了SQL语句硬解析过多的问题,降低了资源的争用。但是绑定变量在引入cursor sharing,增...

Use cursor_sharing_exact refused to SQL variable binding

In the use cursor_sharing parameters, may cause some SQL children cursor too high, cause competition...

cursor_sharing参数!

cursor_sharing这个参数用来告诉Oracle在什么情况下可以共享游标,即SQL重用。它有3个值可以设置:CURSOR_SHARING = { SIMILAR | EXACT | FORCE...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:cursor_sharing的妙用
举报原因:
原因补充:

(最多只允许输入30个字)