Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1082526
  • 博文数量: 145
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3510
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(145)

文章存档

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2013-05-01 21:21:13

这是来自于ITPUB一网友提出的问题(http://www.itpub.net/thread-1784748-1-1.html):
如题
有使用绑定变量还不如不使用绑定变量的情况吗?
主要是执行效率方面
--------------------------------------------------------------------
     要回答这个问题,得搞清楚绑定变量是干嘛的!绑定变量是为了减少硬解析,让我们的SQL能够用上软解析,甚至软软解析,其中很重要的一方面就是要能够共享library cache中的执行计划。SO,就要搞清楚啥时候共享执行计划好,啥时候共享执行计划不好!很简单,当我们传入不同的值,执行计划稳定,那么就好,否则要求传入不同的值,计划不同,但是用了绑定变量执行计划一样,那么不好!

      对于绑定变量造成影响效率的问题,是ORACLE处理的一个难点,9i binding variable peeking,cursor_sharing=similar,以及11g引入的CARDINALITY FEEDBACK和ADAPTIVE CUSOR SHARING都是解决这种问题,很显然,在11G出现之前,都解决的不够好。比如peeking,peeking只能解决ORACLE硬解析阶段选择正确计划的问题,但是遇到skew data的数据,就很可能有问题,similar虽然和直方图能够解决skew data的问题,但是太多BUG,比如造成version count急剧增加,SO 12C将要废掉similar。期待12C的ACS更加强大吧,ACS是目前为止解决绑定变量与直方图问题最好的ORACLE特性。

     因为你一旦使用绑定变量,peeking的话,就把计划固定了,比如where col=1,第一次硬解析固定了,走全表,col=1返回很多。但是万一我以后再经常查的col=2...都要求走索引好,那完蛋了。

     所以有了cursor_sharing=similar,但是有BUG,也不好用。后来11g有adaptive cursor sharing,cardinality feedback...类似于similar.但是这东西也不能非常好滴解决这玩意,因为他要经常算你返回的基数来比较啊,比较完了,可能子游标生成很多啊。。。

     都不能很好滴解决,当然11g的话,也只能用这个解决了,如果你的值类型很少,那么就不要绑定变量了,反而性能好。

     另外注意,不是有skew data就有问题,我们传入不同的值,就算是数据分布均匀的列,但是值传入的不同,访问方式要求不同,也会有问题,比如:
不过一个稳定的系统,搜索谓词应该搜索的行数差不多,计划应该一样,这样才用绑定变量
where id <:vid;
第一次传入个:vid =1,第2次传入个:vid=1000 0000; 你这表可能就100万行,还要绑定变量吗?用绑定变量,两次计划一样,不糟糕了吗?当然,在事实中,比如OLTP系统,应该是均衡的,应该计划一样,一般是id = :vid.这样的当然要绑定变量。


做个例子:

--关闭11g acs,因为11g的ACS会解决这个问题
alter SESSION set "_optimizer_extended_cursor_sharing_rel"=none;
alter SESSION set "_optimizer_extended_cursor_sharing"=none;
alter SESSION set "_optimizer_adaptive_cursor_sharing"=false;

dingjun123@ORADB> var vid number;
dingjun123@ORADB> exec :vid :=100;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
dingjun123@ORADB> select/*+gather_plan_statistics*/ * from test where object_id <= :vid
----
dingjun123@ORADB> @display_cursor;
SQL_ID  902f2vh0y4pha, child number 0
-------------------------------------
select/*+gather_plan_statistics*/ * from test where object_id <= :vid
Plan hash value: 115135762
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     99 |00:00:00.01 |      18 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |      1 |     69 |     99 |00:00:00.01 |      18 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST |      1 |     69 |     99 |00:00:00.04 |       9 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<=:VID)

dingjun123@ORADB> exec :vid := 100000;
dingjun123@ORADB> select/*+gather_plan_statistics*/ * from test where object_id <= :vid

---
dingjun123@ORADB> @display_cursor
SQL_ID  902f2vh0y4pha, child number 0
-------------------------------------
select/*+gather_plan_statistics*/ * from test where object_id <= :vid
Plan hash value: 115135762
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  74989 |00:00:00.87 |   11335 |    125 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |      1 |     69 |  74989 |00:00:00.87 |   11335 |    125 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST |      1 |     69 |  74989 |00:00:00.39 |    5143 |    125 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<=:VID)


      很显然,object_id<=100000的语句,走全表扫描较好,但是事实还是共享计划,走了索引

     为什么说skew data的要特别注意,因为常常是skew data引起的绑定变量问题,在OLTP中,我们使用绑定变量,除了skew data之外,一般执行计划共享没有啥问题,像上面的虽然是主键,但是你每次搜索差别太大,这共享也有问题,当然在OLTP中很少有这样的情况,OLTP我们搜索的数据量一般是很小的,基本都要求走索引,是吧。非要弄个极端出来,那很多种的。
OLAP不同,处理的数据量大,也耗时多,一般解析时间相对于处理时间忽略不计,因此不用绑定变量,因为每次处理的数据情况可能不同,计划很可能不同,你用了绑定变量,OLAP处理的量有很大,你把要走hash join的变成了前一次的nested loops,那不是跑不出来了啊!
阅读(3740) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~