首页 > 开发 > 综合 > 正文

不产生UNDO的情况

2024-07-21 02:34:16
字体:
来源:转载
供稿:网友
以下操作不产生UNDO或产生很少的undo1 Read-Only transaction2Direct Path 数据导入3对临时段操作,如排序等。其他情况,因为要保证一致读,都要产生必要的undo以下操作不产生UNDO1 Read-Only transaction SQL> set transaction read only;Transaction set.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selectedSQL> insert  into test select * from t;insert  into test select * from t             *ERROR at line 1:ORA-01456: may not perform insert/delete/update Operation inside a READ ONLYtransactionSQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selected2Direct Path 数据导入SQL> create table test as select * from t where 1=0;Table created.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selectedSQL> insert  /*+ append */ into test select * from t;58842 rows created.SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec ,used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1));    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC---------- ---------- ---------- ---------- ---------- ---------- ---------- USED_UBLK  USED_UREC---------- ----------         2         33      10273          0          0          0          0         1          1SQL> commit;Commit complete.SQL> insert  into test select * from t;58842 rows created.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1)); USED_UBLK  USED_UREC---------- ----------        27       1482
    Append插入数据未提交时候,dump回滚段头Start dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25buffer tsn: 1 rdba: 0x00800019 (2/25)scn: 0x0000.00984d1c seq: 0x01 flg: 0x04 tail: 0x4d1c2601frmt: 0x02 chkval: 0xaf28 type: 0x26=KTU SMU HEADER BLOCK  TRN TBL::  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num  ------------------------------------------------------------------------------------------------   0x21   10    0x80  0x2821  0x0002  0x0000.00984d1c  0x00000000  0x0000.000.00000000  0x00000000   0x00000000End dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25    没有为事务分配undo block.    再Dump表头Start dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395buffer tsn: 9 rdba: 0x0240018b (9/395)scn: 0x0000.00984d1c seq: 0x01 flg: 0x00 tail: 0x4d1c2301frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER  Extent Control Header  -----------------------------------------------------------------  Extent Header:: spare1: 0      spare2: 0      #extents: 28     #blocks: 1664                  last map  0x00000000  #maps: 0      offset: 2716      Highwater::  0x0240018c  ext#: 0      blk#: 3      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 0  mapblk  0x00000000  offset: 0                   Unlocked  --------------------------------------------------------  Low HighWater Mark :      Highwater::  0x0240018c  ext#: 0      blk#: 3      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 0  mapblk  0x00000000  offset: 0  Level 1 BMB for High HWM block: 0x02400189  Level 1 BMB for Low HWM block: 0x02400189  --------------------------------------------------------  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000  L2 Hint for inserts:  0x0240018a  Last Level 1 BMB:  0x02400c8a  Last Level II BMB:  0x0240018a  Last Level III BMB:  0x00000000     Map Header:: next  0x00000000  #extents: 28   obj#: 30508  flag: 0x20000000  Extent Map  -----------------------------------------------------------------   0x02400189  length: 8   0x024005d1  length: 8   0x024005d9  length: 8   0x024005e1  length: 8   0x024005e9  length: 8   0x024005f1  length: 8   0x024005f9  length: 8   0x02400601  length: 8   0x02400609  length: 8   0x02400611  length: 8   0x02400619  length: 8   0x02400621  length: 8   0x02400629  length: 8   0x02400631  length: 8   0x02400639  length: 8   0x02400641  length: 8   0x02400689  length: 128   0x02400709  length: 128   0x02400789  length: 128   0x02400809  length: 128   0x02400889  length: 128   0x02400909  length: 128   0x02400989  length: 128   0x02400a09  length: 128   0x02400a89  length: 128   0x02400b09  length: 128   0x02400c09  length: 128   0x02400c89  length: 128  Auxillary Map  --------------------------------------------------------   Extent 0     :  L1 dba:  0x02400189 Data dba:  0x0240018c   Extent 1     :  L1 dba:  0x02400189 Data dba:  0x024005d1   Extent 2     :  L1 dba:  0x024005d9 Data dba:  0x024005da   Extent 3     :  L1 dba:  0x024005d9 Data dba:  0x024005e1   Extent 4     :  L1 dba:  0x024005e9 Data dba:  0x024005ea   Extent 5     :  L1 dba:  0x024005e9 Data dba:  0x024005f1   Extent 6     :  L1 dba:  0x024005f9 Data dba:  0x024005fa   Extent 7     :  L1 dba:  0x024005f9 Data dba:  0x02400601   Extent 8     :  L1 dba:  0x02400609 Data dba:  0x0240060a   Extent 9     :  L1 dba:  0x02400609 Data dba:  0x02400611
   Extent 10    :  L1 dba:  0x02400619 Data dba:  0x0240061a   Extent 11    :  L1 dba:  0x02400619 Data dba:  0x02400621   Extent 12    :  L1 dba:  0x02400629 Data dba:  0x0240062a   Extent 13    :  L1 dba:  0x02400629 Data dba:  0x02400631   Extent 14    :  L1 dba:  0x02400639 Data dba:  0x0240063a   Extent 15    :  L1 dba:  0x02400639 Data dba:  0x02400641   Extent 16    :  L1 dba:  0x02400689 Data dba:  0x0240068b   Extent 17    :  L1 dba:  0x02400709 Data dba:  0x0240070b   Extent 18    :  L1 dba:  0x02400789 Data dba:  0x0240078b   Extent 19    :  L1 dba:  0x02400809 Data dba:  0x0240080b   Extent 20    :  L1 dba:  0x02400889 Data dba:  0x0240088b   Extent 21    :  L1 dba:  0x02400909 Data dba:  0x0240090b   Extent 22    :  L1 dba:  0x02400989 Data dba:  0x0240098b   Extent 23    :  L1 dba:  0x02400a09 Data dba:  0x02400a0b   Extent 24    :  L1 dba:  0x02400a89 Data dba:  0x02400a8b   Extent 25    :  L1 dba:  0x02400b09 Data dba:  0x02400b0b   Extent 26    :  L1 dba:  0x02400c09 Data dba:  0x02400c0b   Extent 27    :  L1 dba:  0x02400c89 Data dba:  0x02400c8b  --------------------------------------------------------   Second Level Bitmap block DBAs   --------------------------------------------------------   DBA 1:   0x0240018a    End dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395    发现High Water Mark 并没有提高。    此时,在其他session执行    SQL> insert into test select * from t where rownum<10;    该insert 被锁住    察看锁信息SQL> select a.sid,b.sid,a.type,(select object_name from dba_objects where object_id=a.id1) object,a.lmode,b.request,a.block from v$lock a,v$Lock b where a.id1=b.id1 and a.id2=b.id2 and b.request>0 and a.block>0;Lock   Session       TYPE        OBJECT          LMODE    REQUEST      BLOCK
---------- ---------- -------------------- ---------- -------------------- ---------- ---------- 11 block 16           TM           TEST                     6               3                       1    Direct Load Data的时候在表上加了绝对锁('Exclusive'),保证High Water Mark不被其他session修改;因High Water Mark不变,也就不会影响其他session一致读,也就不需要产生很多的undo。SQL> truncate table test;Table truncated.SQL> insert /*+ append */ into test select  * from t where rownum<10;9 rows created.SQL> select * from test;select * from test           *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallelSQL> delete from test;delete from test          *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallel    同时这也是为什么当前session也无法对表进行查询和修改的原因。    3对临时段操作,如排序等。

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表