ITPub博客

首页 > Linux操作系统 > Linux操作系统 > For Update操作分析——不同Oracle版本之间的差异研究

For Update操作分析——不同Oracle版本之间的差异研究

原创 Linux操作系统 作者:realkid4 时间:2011-05-05 21:32:55 0 删除 编辑

 

在《Select For update语句浅析》(http://space.itpub.net/17203031/viewspace-694383)中,笔者详细介绍了for update字句的用法。一个朋友在做实验的时候,说出现一些和文章中结果不同的现象。仔细研究下,还真发现不少玄妙。特此记录下来。(感谢xhl同学

 

问题

 

在文章中,for update的方式会将select后面所涉及到的数据表全部加以锁定。笔者实验的环境中,也的确如此。

 

//oracle11g下的实验

SQL> conn scott/tiger@ora11g;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL>  select sid from v$mystat where rownum<2;

       SID

----------

        44

 

SQL>  select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

EMPNO ENAME      JOB         MGR       SAL

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

 7369 SMITH      CLERK      7902    800.00

 

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=44;

 

ADDR       SID TYPE        ID1        ID2      LMODE    REQUEST BLOCK

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

51A80764         44 AE          100          0          4          0    0

B6CA8AB8         44 TM        73179          0          3        0   0

B6CA8AB8         44 TM        73181          0          3        0   0

50ABF340         44 TX       196629       2924          6         0   0

 

我们发现同文章中的情况和结果相同。同时我们观察下执行计划。

 

 

SQL> explain plan for select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

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

|   0 | SELECT STATEMENT              |         |     1 |    32 |     1   (0)| 0

|   1 |  FOR UPDATE                   |         |       |       |            |

|   2 |   NESTED LOOPS                |         |     1 |    32 |     1   (0)| 0

|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    29 |     1   (0)| 0

|*  4 |     INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 0

|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     4 |    12 |     0   (0)| 0

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

Predicate Information (identified by operation id):

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

   4 - access("EMPNO"=7369)

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

 

18 rows selected

 

 

当相同的语句相同的环境,我们转移到oracle10g下,也就是朋友实验的环境下,情况就有所差异了。

 

//oracle10g环境下

SQL> conn scott/tiger@otstest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

 

SQL> select sid from v$mystat where rownum<2;

       SID

----------

       131

 

SQL> select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

 

EMPNO ENAME      JOB         MGR       SAL

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

 7369 SMITH      CLERK      7902    700.00

 

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=131;

 

ADDR      SID TYPE        ID1        ID2      LMODE    REQUEST  BLOCK

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

27F3F1D8        131 TM        51151          0          3          0     0

27FA4BB8        131 TX       393219      23597          6          0     0

 

 

和我们预想的差异出现了,在oracle10g下,同样的语句只会对一个数据表进行加锁。那么产生这种差异的原因是什么呢?我们同样观察一下执行计划。

 

 

SQL> explain plan for select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 222764943

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

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

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

|   0 | SELECT STATEMENT              |        |     1 |    29 |     1   (0)| 00:

|   1 |  FOR UPDATE                   |        |       |       |            |

|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    29 |     1   (0)| 00:

|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:

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

Predicate Information (identified by operation id):

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

   2 - filter("EMP"."DEPTNO" IS NOT NULL)

   3 - access("EMPNO"=7369)

16 rows selected

 

 

从执行计划上看,执行计划没有涉及到数据表DEPT。但是SQL语句中有包括DEPT的部分,看来for update没有对dept表的锁定原因是和执行计划有关。

 

那么,仔细研究那句SQL语句就可以发现,虽然这个SQL中包括了Dept数据表的内容。但是无论是条件where字句后,还是显示字段列表中,都没有该数据表的列信息内容。这个也许就是出现奇怪现象的原因。

 

Oracle10g在生成执行计划的时候,发现该SQL中虽然包括dept数据表,但是没有实际的含义,所以进行优化改写的时候,就将dept剔除出了执行计划。而oracle11g显然不认可这种方式的改写,将dept重新纳入了执行计划。

 

如果我们加入dept字段在where或者列表中,那么是不是就会实现dept两个数据表的锁定了?下面分别在oracle11g10g上进行试验。

 

//Oracle10g情况

SQL> select empno,ename,job,mgr,sal,dept.deptno from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

(篇幅原因,省略)

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=131;

 

ADDR   SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

28434394        131 TO         8706          1          3        0          0

27F3F1D8        131 TM        51149          0          3       0         0

27F3F29C        131 TM        51151          0        3          0          0

27FA4BB8        131 TX       393261      23597       6          0          0

 

SQL> explain plan for select empno,ename,job,mgr,sal,dept.deptno from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

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

|   0 | SELECT STATEMENT              |         |     1 |    32 |     1   (0)| 0

|   1 |  FOR UPDATE                   |         |       |       |            |

|   2 |   NESTED LOOPS                |         |     1 |    32 |     1   (0)| 0

|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    29 |     1   (0)| 0

|*  4 |     INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 0

|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     4 |    12 |     0   (0)| 0

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

Predicate Information (identified by operation id):

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

   3 - filter("EMP"."DEPTNO" IS NOT NULL)

   4 - access("EMPNO"=7369)

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

 

19 rows selected

 

 

11g的情况下,同样会进行两个数据表的锁定,执行计划如下:

 

//Oracle 11g的情况

SQL>  select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

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

|   0 | SELECT STATEMENT              |         |     1 |    32 |     1   (0)| 0

|   1 |  FOR UPDATE                   |         |       |       |            |

|   2 |   NESTED LOOPS                |         |     1 |    32 |     1   (0)| 0

|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    29 |     1   (0)| 0

|*  4 |     INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 0

|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     4 |    12 |     0   (0)| 0

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

Predicate Information (identified by operation id):

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

   4 - access("EMPNO"=7369)

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

 

18 rows selected

 

 

当我们将dept相应的内容加入到select或者where之后,dept就会出现在执行计划中。执行计划中的for update操作,就会实现将数据表dept的锁定。

 

 

这个案例,告诉我们下面几个方面问题:

 

ü        从执行计划角度看,for update锁定的范围一定是for update操作所涉及的所有数据表。如果执行计划中for update下没有数据表,就一定不会被锁定;

ü        不同版本的Oracle优化器,在生成执行计划时差异很大,这种差异可能造成一些时候的困扰;

ü        Oracle会对输入的SQL进行一定程度改写,根据一些即定义好的规则进行改写。有时候会将一些内容加以剔除,间接影响到执行计划了;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-694555/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7678946