連結：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 分類目錄。將固定連線加入收藏夾。

Oracle DML（非select） 操作不commit 對select的影響

相關文章

Oracle DML（非select）操作不commit 對select的影響