分享
 
 
 

不产生UNDO的情况

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

以下操作不产生UNDO或产生很少的undo

1 Read-Only transaction

2Direct Path 数据导入

3对临时段操作,如排序等。

其他情况,因为要保证一致读,都要产生必要的undo

以下操作不产生UNDO

1 Read-Only transaction

SQL> set transaction read only;

Transaction set.

SQL> select 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));

no rows selected

SQL> 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 ONLY

transaction

SQL> select 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));

no rows selected

2Direct 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 saddr

from v$session where sid=(select sid from v$mystat where rownum=1));

no rows selected

SQL> insert /*+ append */ into test select * from t;

58842 rows created.

SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec ,used_ublk,used_ur

ec from v$transaction where ses_addr=(select saddr from v$session where sid=(sel

ect 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 1

SQL> 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 saddr

from 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 25

buffer tsn: 1 rdba: 0x00800019 (2/25)

scn: 0x0000.00984d1c seq: 0x01 flg: 0x04 tail: 0x4d1c2601

frmt: 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 0x00000000

End 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 395

buffer tsn: 9 rdba: 0x0240018b (9/395)

scn: 0x0000.00984d1c seq: 0x01 flg: 0x00 tail: 0x4d1c2301

frmt: 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 parallel

SQL> delete from test;

delete from test *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

同时这也是为什么当前session也无法对表进行查询和修改的原因。

3对临时段操作,如排序等。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有