注释：
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>
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>
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回滚表空间 思考...
【源于本人笔记】 若有书写错误，表达错误，请指正...
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8689259/viewspace-2128029/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
请登录后发表评论
登录
全部评论
