Oracle DML（非select） 操作不commit 对select的影响

---

链接： http://blog.itpub.net/28602568/viewspace-1415926/

标题：Oracle DML（非select） 操作不commit 对select的影响

作者： ｌōττéｒｙ ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

---

注释：

  DML（非select）操作不commit，其他会话操作(update/delete)相同行/整表 产生锁等待(enq: TX - row lock contention)  

  那您是否知道事物不提交对select 的影响呢？...

   

 

***************************   SESSION 1  ********************************

SQL> create table lottery as select * from dba_objects;

Table created.

SQL> select count(1) from lottery ;

  COUNT(1)

----------

     93291

SQL> insert into lottery select * from lottery ;

93291 rows created.

SQL> /

186582 rows created.

SQL> select count(1) from lottery ;

  COUNT(1)

----------

     373164

SQL> commit;

Commit complete.

 

SQL> delete from lottery ;

373164 rows deleted.

SQL>  ...... 不提交

 

***************************    SESSION  2  ********************************

 

SQL> select count(1) from lottery ;

  COUNT(1)
----------
    373164

Elapsed: 00:00:06.60

Execution Plan
----------------------------------------------------------
Plan hash value: 201422644

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1448   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL|  lottery |   365K|  1448   (1)| 00:00:18 |

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

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      344240  consistent gets 
          0  physical reads
      301184  redo size
        424  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>

***************************   SESSION  1  ********************************

SQL>  commit;

Commit complete.

SQL>  

 

***************************   SESSION  2  ********************************

 

SQL> select count(1) from lottery ;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 201422644

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1446   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL|  lottery |     1 |  1446   (1)| 00:00:18 |

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

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        5335  consistent gets
          0  physical reads
           0  redo size
        421  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> 

总结： 

   Oracle DML（非select）操作不commit，其他会话查询该表数据时会产生额外的逻辑读和redo，影响整个查询时间 !

   ** 额外的消耗 要 结合undo回滚表空间 思考...

  

    【源于本人笔记】 若有书写错误，表达错误，请指正...

---

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

---

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

转载于:http://blog.itpub.net/28602568/viewspace-1415926/