为什么Oracle要避免大事务呢？
1.高并发
2.回滚慢
3. 从UNDO读数据也是单块读
SQL> desc a1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
FLAG1 CHAR(10)
FLAG2 CHAR(10)
SQL> begin for i in 1 .. 1000000
2 loop
3 insert into a1 values (i,'a'||i,'b'||i);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> delete from a1;
全表扫描出现了单块读,因为是读的UNDO数据,查看UNDO数据:
SQL> select s.username, u.name from v$transaction t,
v$rollstat r, v$rollname u,v$session s
where s.taddr=t.addr and t.xidusn=r.
usn and r.usn=u.usn order by s.username; 2 3 4
USERNAME NAME
------------------------------ ------------------------------
TEST _SYSSMU3_1204390606$
为什么交易系统不能出现大事务:
SQL> set linesize 200
SQL> select * from v$lock where sid=34;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
35001810 3500183C 34 AE 100 0 4 0 1752 0
35001A54 35001A80 34 TO 65908 1 3 0 1605 0
开始删除100W数据:
SQL> select * from v$lock where sid=34;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
35001810 3500183C 34 AE 100 0 4 0 1772 0
35001A54 35001A80 34 TO 65908 1 3 0 1625 0
00707A9C 00707ACC 34 TM 75517 0 3 0 4 0
336F4CB0 336F4CF0 34 TX 131086 12696 6 0 4 0
删除数据时,长时间持有行锁,万一大事务失败,回滚慢,又是长时间占用行锁：
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
35001810 3500183C 34 AE 100 0 4 0 1829 0
35001A54 35001A80 34 TO 65908 1 3 0 1682 0
00707A9C 00707ACC 34 TM 75517 0 3 0 61 0
336F4CB0 336F4CF0 34 TX 131086 12696 6 0 61 0