注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

ORACLE 绑定变量的问题  

2011-08-28 15:26:38|  分类: oracle优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

很多人都会认为,应用程序的设计在传入参数的时候都采用绑定变量为最好,但其实不尽然,而且在调用和性能上也不一样,如果使用PL/SQL来编码,使用API,绑定变量就会相对简单,但如果使用JDBC来开发,则绑定变量就体现不了其优势。而且大家也知道,使用绑定变量可以减少硬解析,可以共享类似的SQL语句,如:

其中ID可以是任意的值:

SELECT COUNT(ID) FROM TEST.TT WHERE ID <:ID

但使用绑定变量有时候也会带来执行计划的问题,ORACLE的语句在使用的时候都会打开一个游标,会根据在SGA中的库缓存中找到共享的SQL的语句,找到父游标的ADDRESS,HASH_VALUE和子游标的值对应,然后进行相应的变量代值进行SQL语句的执并返回结果

注:父游标用来保存SQL语句的文本信息,子游标用来保存执行计划和环境

下面是关于绑定变量带来的执行计划的问题:

create table tt (id int,name varchar2(20));

create index id_tt_id on tt(id);

declare

   v number;

begin

    for v in 1 .. 1000 loop

         insert into tt values (v,'hello');

   end loop;

end;

/

案例:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  set autotrace on
SQL> variable id number
SQL> execute :id :=10;

PL/SQL procedure successfully completed.

SQL> select count(id) from test.tt where id < :id;

 COUNT(ID)
----------
         9


Execution Plan
----------------------------------------------------------
Plan hash value: 1506588481

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TT_ID |    50 |   650 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<TO_NUMBER(:ID))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> execute :id :=980;

PL/SQL procedure successfully completed.

SQL> select count(id) from test.tt where id < :id;

 COUNT(ID)
----------
       979


Execution Plan
----------------------------------------------------------
Plan hash value: 1506588481

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TT_ID |    50 |   650 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<TO_NUMBER(:ID))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@R2DB01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 28 15:17:03 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> variable id number
SQL> set autotrace on
SQL> execute :id :=980

PL/SQL procedure successfully completed.

SQL> select count(id) from test.tt where id < :id;

 COUNT(ID)
----------
       979


Execution Plan
----------------------------------------------------------
Plan hash value: 1506588481

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TT_ID |    50 |   650 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<TO_NUMBER(:ID))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> execute :id :=10;

PL/SQL procedure successfully completed.

SQL> select count(id) from test.tt where id < :id;

 COUNT(ID)
----------
         9


Execution Plan
----------------------------------------------------------
Plan hash value: 1506588481

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TT_ID |    50 |   650 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<TO_NUMBER(:ID))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

可以从上面的测试结果看到,按理论来说,后面ID=980的时候应该是走全表扫描才对,可是因为游标共享的原因,导致执行计划不对,而没有理会执行计划的效率如何,这也是绑定变量带来的问题,尽管说在11g中引入了一个叫扩展的游标共享,能够重用一个已经存在的但是会导致执行效率低下的游标,自动进行识别,但我在测试的过程中并没有感觉到该功能的实现。总的来讲,绑定变量可以适当使用,但有时候了更好的提高查询优化器的执行计划,还是谨慎使用。

 

  评论这张
 
阅读(239)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017