【oracle11g，13】表空间管理2：undo表空间管理(调优) ，闪回原理

首页 > 代码库 > 【oracle11g，13】表空间管理2：undo表空间管理(调优) ，闪回原理

【oracle11g，13】表空间管理2：undo表空间管理(调优) ，闪回原理

2024-07-23 03:39:18 222人阅读

一.undo空间原理：

dml操作会产生undo数据。

update时，sever process 会在databuffer 中找到该记录的buffer块，没有就从datafile中找并读入data buffer。在修改之前，原始数据先放到undo段,并在数据块头记录undo段(acitve 状态)中该数据块的位置，读写这个块时会占用事务槽，会将该事务号记录在数据块的头部。然后在进行update，并将该块放到dirty list检查点队列，等待dbwr进行写操作。

二.创建新的undo表空间替换老的表空间：并扩展undo大小

1.查看系统默认undo表空间：

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undotbs

2.创建undo表空间

SQL> create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/prod/disk3/undotbs2.dbf‘ size 50m;

#修改默认undo表空间，如果是pfile启动的，要修改参数文件。

SQL>alter system set undo_tablespace=undotbs2;

删除老的undo表空间

SQL>drop tablespace undotbs including contents and datafiles;

undo 表空间扩展大小

SQL> alter database datafile 8 autoextend on next 10m maxsize 500m;

三.undo段作用:

1.undo 自动管理：

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undotbs

2.undo段的管理方式：

SQL> select Name from v$rollname;

NAME

------------------------------

SYSTEM

_SYSSMU1_548815$

_SYSSMU2_4162303249$

_SYSSMU3_1137786205$

_SYSSMU4_1264943161$

_SYSSMU5_1041434215$

_SYSSMU6_2715111370$

_SYSSMU7_1539595696$

_SYSSMU8_1799925899$

_SYSSMU9_3367637386$

_SYSSMU10_148416192$

11 rows selected.

一个回滚段尽量让少的事务区操作。刚开始10个回滚段，每来一个事务给一个回滚段，直到回滚段达到30个，以后就让多个事务共用同一个回滚段。

3.undo的作用：(四大作用)

①.回滚事务:

dml产生的undo的量：

在dml操作提交前，可以进行回滚操作，回滚信息就是从undo段中读取的。

回滚等效于反向操作。undo不会记录语句，记录的是数据的变化。

insert产生undo 只记录rowid

delete 产生undo，记录改行全部信息

update 产生undo，修改前后的字段。

②.读一致性：(通过比较scn实现)

a).oracle在dml操作提交之前，其他用户访问的数据是undo段中的镜像数据和数据块中未被修改的数据构造的CR块。这是通过对比scn号来判断数据块上的数据是否被修改。

b).任何数据的状态以开始查询的时刻的状态为准，如：数据库中有1000万行记录，一个查询需要从九点查询到九点半才能结束，在执行查询其间数据库又插入了1000条，但select语句只会查出1000万条。

例：任何数据的状态以开始查询的时刻的状态为准。

scott@BJDB>var c_emp refcursor;

scott@BJDB> open :c_emp for select * from emp;

PL/SQL procedure successfully completed.

#打印游标中的数据，其中有12条数据。

scott@BJDB>print :c_emp

12 rows selected.

#当在open游标之后，在其他会话修改了查询的数据，然后再打印数据，结果不会显示修改后的内容，因为先做查询然后才做的修改。

scott@BJDB> open :c_emp for select * from emp;

PL/SQL procedure successfully completed.

③.实例恢复：

如果非正常关机，实例恢复时，对没有正常提交的事务，跑redo日志会将所有的块和undo块重做一遍，如果发现没有进行提交，就进行一次回滚操作。

比如我一个小时没有提交这个事务，但是脏块累计到一定量时自动写，数据已经写入数据文件了，并且写了redo日志，但是只要不提交，undo块就处于active，回滚时只要不提交的都回滚。

④.倒叙查询或叫闪回查询：

10g后增加了新特性--闪回，利用undo已经提交的块，闪回数据库和表，已经提交的块inacitive数据时不能回滚的，但可以倒回某个时间点。

3.查看undo信息：

①.查询回滚段信息

SQL> select segment_name ,owner,tablespace_name ,file_id ,initial_extent/1024 init,next_extent/1024 next,status from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_N FILE_ID INIT NEXT STATUS

-------------------- ------ ------------ ---------- ---------- ---------- --------

SYSTEM SYS SYSTEM 1 112 56 ONLINE

_SYSSMU1_548815$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU2_4162303249$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU3_1137786205$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU4_1264943161$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU5_1041434215$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU6_2715111370$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU7_1539595696$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU8_1799925899$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU9_3367637386$ PUBLIC UNDOTBS 3 128 64 ONLINE

_SYSSMU10_148416192$ PUBLIC UNDOTBS 3 128 64 ONLINE

11 rows selected.

例2：查询undo段的状态，xacts为1时表示活跃。

SQL> select a.* ,b.extents,b.writes,b.xacts,b.waits from v$rollname a,v$rollstat b where a.usn = b.usn;

USN NAME EXTENTS WRITES XACTS WAITS

---------- ------------------------------ ---------- ---------- ---------- ---------- 0 SYSTEM 6 5312 0 0 1 _SYSSMU1_548815$ 38 124956 0 0 2 _SYSSMU2_4162303249$ 39 118264 0 0 3 _SYSSMU3_1137786205$ 37 274520 0 0 4 _SYSSMU4_1264943161$ 5 366954 0 0 5 _SYSSMU5_1041434215$ 43 247250 0 0 6 _SYSSMU6_2715111370$ 5 657760 0 0 7 _SYSSMU7_1539595696$ 38 276818 0 0 8 _SYSSMU8_1799925899$ 43 394868 0 0 9 _SYSSMU9_3367637386$ 37 265512 0 0 10 _SYSSMU10_148416192$ 33 230560 0 0

11 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> select a.* ,b.extents,b.writes,b.xacts,b.waits from v$rollname a,v$rollstat b where a.usn = b.usn;

USN NAME EXTENTS WRITES XACTS WAITS

---------- ------------------------------ ---------- ---------- ---------- ----------

0 SYSTEM 6 5312 0 0

1 _SYSSMU1_548815$ 38 124956 0 0

2 _SYSSMU2_4162303249$ 39 120798 1 0

3 _SYSSMU3_1137786205$ 37 274520 0 0

4 _SYSSMU4_1264943161$ 5 366954 0 0

5 _SYSSMU5_1041434215$ 43 247250 0 0

6 _SYSSMU6_2715111370$ 5 657760 0 0

7 _SYSSMU7_1539595696$ 38 276818 0 0

8 _SYSSMU8_1799925899$ 43 394868 0 0

9 _SYSSMU9_3367637386$ 37 265650 0 0

10 _SYSSMU10_148416192$ 33 230560 0 0

11 rows selected.

例3：查询当前活跃的undo段

col username for a5;

col name for a10;

col status for a6;

select a.username,a.sid,a.serial#,b.xidusn,b.xidslot,b.status,c.usn,c.name,d.extents,d.xacts from v$session a,v$transaction b,v$rollname c,v$rollstat d where a.saddr=b.ses_addr and b.xidusn=c.usn and c.usn=d.usn;

USERN SID SERIAL# XIDUSN XIDSLOT STATUS USN NAME EXTENTS XACTS

----- ---------- ---------- ---------- ---------- ------ ---------- --------------------- ---------- ----------

SYS 1 5 2 24 ACTIVE 2 _SYSSMU2_4162303249$ 39 1

②.

v$session 查看用户建立的session

v$transaction 当前事务

v$rollname 回滚段的名称

v$rollstat 回滚段的状态

四.undo和redo区别：

undo redo

内容： dml造成的数据的变化记录所有数据的改变

目的：为了撤销回滚事务为了重现并恢复数据

方向回滚前滚

位置 undo段 redo日志

实质关系事务是否完成记录所有数据变化

五. undo_retention参数和ora-01555 错误

1.ora-01555 错误快照过旧snapshot too old。

发生错误的原因：

在进行非常复杂的查询时，如果数据块被修改了，查询时要构建一致性读，当这个查询语句执行到一半的时候事务被提交了，而此时数据库特别繁忙(由于undo空间过小，事务量过大，过于频繁的提交inactive可以被覆盖)，导致undo段中的修改前的镜像数据已经被覆盖，不能构建一致性读块，会报ora-01555错误。

解决ora-01555错误：

扩大undo表空间，设置undo_retention足够长。

2.undo_retention参数：设置undo数据的保护时间。

设置了undo_retention参数为90分钟，数据块事务提交之后，仍能保存至少90分钟。

如果undo表空间没空间了，优先覆盖undo段中expire的数据块。但如果空间还不足然后即使没到undo_retention设置的时间也会被覆盖。

#如果想在undo_retention设置的时间内不被覆盖，执行：

SQL> alter tablespace undotbs retention guarantee;

但是前提是undo表空间足够大，才能设置retention guarantee。否则是dml操作会失败。

注意：

在生成库上如果有足够的空间，可以将undo空间设的足够大， undo_retention参数的值尽量长。

在undo扩展时，会产生等待事件。

sys@BJDB>select tablespace_name, contents, retention from dba_tablespaces;

TABLESPACE_NAME CONTENTS RETENTION

------------------------------ --------- -----------

SYSTEM PERMANENT NOT APPLY

SYSAUX PERMANENT NOT APPLY

TEMPTS1 TEMPORARY NOT APPLY

USERS PERMANENT NOT APPLY

SMALL_UNDO UNDO NOGUARANTEE

NEWTS PERMANENT NOT APPLY

六.undo段状态：

1.undo段状态：

#查看回滚段的状态：

SQL> col segment_name for a10

SQL> col status for a10;

SQL> select segment_name,extent_id,file_id,blocks,bytes/1024 k,status from dba_undo_extents;

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU10$ 0 2 8 64 EXPIRED

_SYSSMU10$ 1 2 8 64 EXPIRED

_SYSSMU10$ 2 2 128 1024 EXPIRED

_SYSSMU10$ 3 2 128 1024 EXPIRED

_SYSSMU10$ 4 2 128 1024 EXPIRED

_SYSSMU10$ 5 2 128 1024 EXPIRED

_SYSSMU10$ 6 2 128 1024 EXPIRED

_SYSSMU9$ 0 2 8 64 EXPIRED

_SYSSMU9$ 1 2 8 64 EXPIRED

_SYSSMU9$ 2 2 128 1024 EXPIRED

_SYSSMU9$ 3 2 128 1024 EXPIRED

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU9$ 4 2 128 1024 EXPIRED

_SYSSMU9$ 5 2 128 1024 EXPIRED

_SYSSMU9$ 6 2 128 1024 EXPIRED

_SYSSMU9$ 7 2 128 1024 EXPIRED

_SYSSMU9$ 8 2 128 1024 EXPIRED

_SYSSMU8$ 0 2 8 64 EXPIRED

_SYSSMU8$ 1 2 8 64 EXPIRED

_SYSSMU8$ 2 2 128 1024 EXPIRED

_SYSSMU7$ 0 2 8 64 EXPIRED

_SYSSMU7$ 1 2 8 64 EXPIRED

_SYSSMU7$ 2 2 128 1024 EXPIRED

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU7$ 3 2 128 1024 EXPIRED

_SYSSMU6$ 0 2 8 64 EXPIRED

_SYSSMU6$ 1 2 8 64 EXPIRED

_SYSSMU6$ 2 2 128 1024 EXPIRED

_SYSSMU5$ 0 2 8 64 EXPIRED

_SYSSMU5$ 1 2 8 64 EXPIRED

_SYSSMU5$ 2 2 128 1024 EXPIRED

_SYSSMU4$ 0 2 8 64 EXPIRED

_SYSSMU4$ 1 2 8 64 EXPIRED

_SYSSMU4$ 2 2 8 64 EXPIRED

_SYSSMU4$ 3 2 8 64 EXPIRED

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU4$ 4 2 8 64 EXPIRED

_SYSSMU4$ 5 2 8 64 EXPIRED

_SYSSMU4$ 6 2 8 64 EXPIRED

_SYSSMU4$ 7 2 8 64 EXPIRED

_SYSSMU4$ 8 2 8 64 EXPIRED

_SYSSMU4$ 9 2 8 64 EXPIRED

_SYSSMU4$ 10 2 8 64 EXPIRED

_SYSSMU4$ 11 2 8 64 EXPIRED

_SYSSMU4$ 12 2 8 64 EXPIRED

_SYSSMU4$ 13 2 8 64 EXPIRED

_SYSSMU4$ 14 2 8 64 EXPIRED

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU4$ 15 2 128 1024 EXPIRED

_SYSSMU4$ 16 2 8 64 EXPIRED

_SYSSMU3$ 0 2 8 64 UNEXPIRED

_SYSSMU3$ 1 2 8 64 UNEXPIRED

_SYSSMU3$ 2 2 128 1024 UNEXPIRED

_SYSSMU3$ 3 2 128 1024 EXPIRED

_SYSSMU3$ 4 2 128 1024 UNEXPIRED

_SYSSMU2$ 0 2 8 64 EXPIRED

_SYSSMU2$ 1 2 8 64 EXPIRED

_SYSSMU2$ 2 2 128 1024 EXPIRED

_SYSSMU2$ 3 2 128 1024 EXPIRED

SEGMENT_NA EXTENT_ID FILE_ID BLOCKS K STATUS

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU1$ 0 2 8 64 EXPIRED

_SYSSMU1$ 1 2 8 64 EXPIRED

_SYSSMU1$ 2 2 128 1024 EXPIRED

_SYSSMU1$ 3 2 128 1024 EXPIRED

59 rows selected.

七.回滚段自动扩展：

八.undo段手工管理：(一般高手使用)

1.修改 undo_management=manual (静态参数，重启数据库才生效)

SQL> alter system set undo_management=manual scope=spfile;

QL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 5400

undo_tablespace string undotbs

#手工管理时需要手工创建undo段，否则执行dml操作会失败。

SQL> delete from scott.emp;

delete from scott.emp

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS‘

2. 创建一个MSSM管理的表空间，用于undo表空间，注意：手工管理时，不区分undo表空间，每个表空间都可以是undo表空间.

SQL> create tablespace rbs datafile ‘/u01/app/oracle/oradata/PROD/disk3/rbs01.dbf‘ size 50m segment space management manual;

3.创建undo段：首先要在system表空间上创建undo段,才可以再其他表空间上创建回滚段。

①.system上创建回滚段：

SQL> create rollback segment haha tablespace system;

Rollback segment created.

#创建回滚段以后要online

SQL> alter rollback segment haha online;

Rollback segment altered.

#查询回滚段：

SQL> select name from v$rollname;

NAME

--------------------

SYSTEM

HAHA

②.批量创建20个回滚段：设置回滚段初始大小为1280k，自动管理时默认的大小是64k，这里是原来的20倍，这样可以就减少undo扩展，会产生等待事件。。

自动管理时回滚段数量默认为10，这里建20个回滚段。

（下面写的是640，但建出来是1280）

#增加表空间的大小。

SQL> alter database datafile 5 autoextend on next 10m maxsize 500m;

Database altered.

SQL> declare

2 v_sql varchar2(200);

3 begin

4 for i in 1..20 loop

5 v_sql:=‘create rollback segment seg‘||lpad(i,2,‘0‘)||‘ tablespace rbs storage(initial 640k next 640k)‘;

6 execute immediate v_sql;

7 v_sql:=‘alter rollback segment seg‘||lpad(i,2,‘0‘)||‘ online ‘;

8 execute immediate v_sql;

9 end loop;

10 end;

11 /

PL/SQL procedure successfully completed.

#查询回滚段；

SQL> select name from v$rollname;

NAME

--------------------

SYSTEM

HAHA

SEG01

SEG02

SEG03

SEG04

SEG05

SEG06

SEG07

SEG08

NAME

--------------------

SEG09

SEG10

SEG11

SEG12

SEG13

SEG14

SEG15

SEG16

SEG17

SEG18

SEG19

NAME

--------------------

SEG20

22 rows selected.

#查询回滚段信息，二十个回滚段，回滚段比原来大20倍。

SQL> select segment_name,tablespace_name,owner,file_id,status, initial_extent/1024 init,next_extent/1024 next from dba_rollback_segs;

SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT

---------- ---------- ------ ---------- ---------- ---------- ----------

SYSTEM SYSTEM SYS 1 ONLINE 112

_SYSSMU1$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU2$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU3$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU4$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU5$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU6$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU7$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU8$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU9$ UNDOTBS PUBLIC 2 OFFLINE 128

_SYSSMU10$ UNDOTBS PUBLIC 2 OFFLINE 128

SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT

---------- ---------- ------ ---------- ---------- ---------- ----------

HAHA SYSTEM SYS 1 ONLINE 2048

SEG01 RBS SYS 5 ONLINE 1280

SEG02 RBS SYS 5 ONLINE 1280

SEG03 RBS SYS 5 ONLINE 1280

SEG04 RBS SYS 5 ONLINE 1280

SEG05 RBS SYS 5 ONLINE 1280

SEG06 RBS SYS 5 ONLINE 1280

SEG07 RBS SYS 5 ONLINE 1280

SEG08 RBS SYS 5 ONLINE 1280

SEG09 RBS SYS 5 ONLINE 1280

SEGMENT_NA TABLESPACE OWNER FILE_ID STATUS INIT NEXT

---------- ---------- ------ ---------- ---------- ---------- ----------

SEG10 RBS SYS 5 ONLINE 1280

SEG11 RBS SYS 5 ONLINE 1280

SEG12 RBS SYS 5 ONLINE 1280

SEG13 RBS SYS 5 ONLINE 1280

SEG14 RBS SYS 5 ONLINE 1280

SEG15 RBS SYS 5 ONLINE 1280

SEG16 RBS SYS 5 ONLINE 1280

SEG17 RBS SYS 5 ONLINE 1280

SEG18 RBS SYS 5 ONLINE 1280

SEG19 RBS SYS 5 ONLINE 1280

SEG20 RBS SYS 5 ONLINE 1280

32 rows selected.

③.将回滚段的名称列到参数文件中，这样中下次启动时就可以自动创建回滚段。

九.使用自动管理undo段原则：

设置undo空间足够大。

设置undo retention参数

设置retention guarantee

及时提交事务。

十.回滚段的调优工具：

1.查看段头发生等待和总的请求的百分比。

SQL> SELECT trunc(sum(waits)* 100 /sum(gets),3)||‘%‘ "Ratio",sum(waits) "Waits", sum(gets) "Gets" FROM v$rollstat;

Ratio Waits Gets

----------------------------------------- ---------- ----------

0% 0 147

2.查询回滚段的信息：

SQL> select segment_name,segment_type,bytes/1024 k,extents,blocks ,tablespace_name from dba_segments where segment_name like ‘_SYSSMU%‘;

SEGMENT_NA SEGMENT_TY K EXTENTS BLOCKS TABLESPACE

---------- ---------- ---------- ---------- ---------- ----------

_SYSSMU1$ TYPE2 UNDO 2176 4 272 UNDOTBS

_SYSSMU2$ TYPE2 UNDO 2176 4 272 UNDOTBS

_SYSSMU3$ TYPE2 UNDO 3200 5 400 UNDOTBS

_SYSSMU4$ TYPE2 UNDO 2048 17 256 UNDOTBS

_SYSSMU5$ TYPE2 UNDO 1152 3 144 UNDOTBS

_SYSSMU6$ TYPE2 UNDO 1152 3 144 UNDOTBS

_SYSSMU7$ TYPE2 UNDO 2176 4 272 UNDOTBS

_SYSSMU8$ TYPE2 UNDO 1152 3 144 UNDOTBS

_SYSSMU9$ TYPE2 UNDO 7296 9 912 UNDOTBS

_SYSSMU10$ TYPE2 UNDO 5248 7 656 UNDOTBS

10 rows selected.

3. statspacke的advisory

①.Buffer busy wait

不应该大于1%，表空间非自动管理。

表空间段空间自动管理，重建索引。 ASSM

buffer busy wait：当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待。该值不应该大于1%。当出现等待问题时，可以检查缓冲等待统计部分(或V$WAITSTAT)，确定该等待发生在什么位置：

a) 如果等待是否位于段头(Segment Header)。这是由于并发DML操作引起的。这种情况表明段中的空闲列表（freelist）的块比较少。可以考虑增加空闲列表(freelist，对于Oracle8i DMT)或者增加freelist groups(在很多时候这个调整是立竿见影的(alter table tablename strorage(freelists 2))，在8.1.6之前，这个freelists参数不能动态修改;在8.1.6及以后版本，动态修改feelists需要设置COMPATIBLE至少为8.1.6)。也可以增加PCTUSED与PCTFREE之间距离（PCTUSED-to-pctfree gap），其实就是说降低PCTUSED的值，尽快使块返回freelist列表被重用。如果支持自动段空间管理（ASSM），也可以使用ASSM模式，这是在ORALCE 920以后的版本中新增的特性。

b) 如果这一等待位于undo header，说明多个事务使用同一个回滚段。可以通过增加回滚段(rollback segment)来解决缓冲区的问题。

c) 如果等待位于undo block上，我们需要增加提交的频率，使block可以尽快被重用；使用更大的回滚段；降低一致读所选择的表中数据的密度；增大DB_CACHE_SIZE。

d) 如果等待处于data block，表明出现了hot block，可能有多个事务访问同一个块的数据。可以考虑如下方法解决： ①将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增大pctfree值，扩大数据分布，减少竞争)，以避开这个"热点"数据块。②也可以减小数据块的大小，从而减少一个数据块中的数据行数，降低数据块的热度，减小竞争；③检查对这些热块操作的SQL语句，优化语句。④增加hot block上的initrans值。但注意不要把initrans值设置的过于高了，通常设置为5就足够了。因为增加事务意味着要增加ITL事务槽，而每个ITL事务槽将占用数据块中24个字节长度。默认情况下，每个数据块或者索引块中是ITL槽是2个，在增加initrans的时候，可以考虑增大数据块所在的表的PCTFREE值，这样Oracle会利用PCTFREE部分的空间增加ITL slot数量，最大达到maxtrans指定。(select * from dba_tables,获取ini_trans,maxtrans,修改alter table tablename initrans 2)

e) 如果等待处于index block，应该考虑重建索引、分割索引或使用反向键索引。为了防止与数据块相关的缓冲忙等待，也可以使用较小的块，在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙"。或者可以设置更大的PCTFREE,使数据扩大物理分布，减少记录间的热点竞争。在执行DML (insert/update/ delete)时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。在Oracle9i 中，可以使用ASSM这个新特性Oracle 使用位图来管理空间使用，减小争用。

②.Rollback per transaction

Rollback per transaction 如果很高，说明做了很多无用的事务。

SQL> select * from v$sysstat where name in (‘user commits‘,‘user rollbacks‘,‘transaction rollbacks‘);

STATISTIC# NAME CLASS VALUE STAT_ID

---------- ------------------------- ---------- ---------- ----------

4 user commits 1 5 582481098

5 user rollbacks 1 0 3671147913

187 transaction rollbacks 128 0 3988650402

4.回滚段的统计信息：

十一.如何产生较少的undo数据：

在export时，可以不设置consistent=n，但是要保证undo表空间足够大，防止export时产生的undo数据将undo占满，影响业务操作。

1.如何正确的插入数据：

①.一百条一提交，或者一千条一提交：

SQL> create table test(id number(8),name varchar2(30));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into test values(i,‘zhangsan‘||i);

4 if mod(i,1000)=0 then

5 commit;

6 end if;

7 end loop;

8 commit;

9 end;

10 /

PL/SQL procedure successfully completed.

②.一天一提交:

案例：

#部署环境

SQL> create table emp_cu as select * from scott.emp;

SQL> insert into emp_cu select * from emp_cu;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> /

448 rows created.

SQL> /

896 rows created.

SQL> commit;

Commit complete.

SQL> create index i_cu on emp_cu(empno);

Index created.

#更新数据，用于下面的试验

SQL> declare

cursor cu_emp is select * from emp_cu for update;

4 v_date date;

5 k number;

6 begin

7 k:=1;

8 v_date:=to_date(‘2010-01-01 08:00:00‘,‘yyyy-mm-dd HH24:MI:SS‘);

9 for i in cu_emp loop

10 update emp_cu set hiredate=v_date,empno=k where current of cu_emp;

11 v_date:=v_date+1;

12 k:=k+1;

13 end loop;

14 commit;

15 end;

16 /

创建第二张表

SQL> create table emp_cu2 as select * from emp_cu where 1=2;

Table created.

#建存储过程，将第一张表数据导到第二张表的

SQL> create or replace procedure proc_insert_data_t

2 as

3 current_time date;

4 begin

5 current_time:=to_date(‘2010-01-01 00:00:00‘,‘yyyy-mm-dd HH24:MI:SS‘);

6 for count in 1..1000000 loop

7 insert into emp_cu2

8 select t.* from emp_cu t

9 where t.hiredate>=current_time

10 and t.hiredate<current_time+1;

11 current_time:=current_time+1;

12 commit;

13 if current_time>=to_date(‘2015-01-01‘,‘yyyy-mm-dd‘) then

14 exit;

15 end if;

16 end loop;

17 end;

18 /

Procedure created.

#执行存储过程

SQL> exec proc_insert_data_t;

③.分页插入：

SQL> truncate table emp_cu2;

Table truncated.

SQL> declare

2 i number;

3 v_total number;

4 v_pagesize number:=1000;

5 v_totalpage number;

6 v_startindex number;

7 v_endindex number;

8 v_pageindex number; --current page num

9 begin

10 select count(*) into v_total from emp_cu;

11 v_totalpage:= CEIL(v_total/v_pagesize);

12 dbms_output.put_line(v_total);

13 dbms_output.put_line(v_totalpage);

14 for i in 1..v_totalpage loop

15 v_pageindex:=i;

16 v_startindex:=(v_pageindex-1)*v_pagesize + 1;

17 v_endindex:=v_pageindex*v_pagesize;

18 if v_endindex>v_total then

19 v_endindex:=v_total;

20 end if;

21 --dbms_output.put_line(v_startindex||‘ ‘||v_endindex);

22 insert into emp_cu2

23 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from (

24 select a.*,rownum rn

25 from (select * from emp_cu order by empno) a

26 ) where rn>=v_startindex and rn<=v_endindex;

27 commit;

28 end loop;

29 end;

30 /

④.重排数据并插入：

SQL> truncate table emp_cu2;

Table truncated.

SQL> create or replace procedure proc_insert_data_t

2 as

3 current_time date;

4 i number;

5 v_total number;

6 v_pagesize number:=1000;

7 v_totalpage number;

8 v_startindex number;

9 v_endindex number;

10 v_pageindex number; --current page num

11 begin

12 current_time:=to_date(‘2010-01-01 00:00:00‘,‘yyyy-mm-dd HH24:MI:SS‘);

13 for count in 1..1000000 loop

16 select count(*) into v_total from emp_cu t

17 where t.hiredate>=current_time

18 and t.hiredate<current_time+1;

19 v_totalpage:= CEIL(v_total/v_pagesize);

20 dbms_output.put_line(v_total);

21 dbms_output.put_line(v_totalpage);

22 for i in 1..v_totalpage loop

23 v_pageindex:=i;

24 v_startindex:=(v_pageindex-1)*v_pagesize + 1;

25 v_endindex:=v_pageindex*v_pagesize;

26 if v_endindex>v_total then

27 v_endindex:=v_total;

28 end if;

29 --dbms_output.put_line(v_startindex||‘ ‘||v_endindex);

30 insert into emp_cu2

31 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from (

32 select a.*,rownum rn

33 from (select * from emp_cu t

34 where t.hiredate>=current_time

35 and t.hiredate<current_time+1 order by empno

36 ) a

37 ) where rn>=v_startindex and rn<=v_endindex;

38 commit;

39 end loop;

41 current_time:=current_time+1;

42 commit;

43 if current_time>=to_date(‘2015-01-01‘,‘yyyy-mm-dd‘) then

44 exit;

45 end if;

46 end loop;

47 end;

48 /

Procedure created.

SQL> exec proc_insert_data_t;

十二.undo段调优总结：

第二部分：闪回原理：

一.开启闪回：

1.开启闪回步骤：

①.正常关库

②.创建目录

③.修改初始参数文件(注意两个参数的顺序：先设置size，再设置地址)

db_recovery_file_dest_size=

db_recovery_file_dest=

④启动到mount状态执行开启闪回命令：

alter database flashback on;

⑤.启动数据库到open状态

注意：数据库在归档模式下，才能开启闪回，否则首先要开启归档。当要关闭归档时，首先关不闪回再关闭归档。

例：使用spfile启动时，开启闪回

SQL> select name,flashback_on from v$database;

NAME FLASHBACK_ON

--------- ------------------

PROD NO

SQL> alter system set db_recovery_file_dest_size=‘1G‘;

System altered.

SQL> alter system set db_recovery_file_dest=‘/home/oracle/prodfalshback‘;

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /home/oracle/prodfalshback

db_recovery_file_dest_size big integer 1G

#db_flashback_retention_target 是闪回最大时间 (单位为秒，默认1440秒一天的时间)

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 281020828 bytes

Database Buffers 130023424 bytes

Redo Buffers 6103040 bytes

Database mounted.

#开启闪回

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

#查询闪回状态

SQL> select name , flashback_on from v$database;

NAME FLASHBACK_ON

--------- ------------------

二.闪回的种类：(重点)

1.闪回drop

闪回drop利用回收站闪回删除的表.

#首先要开启回收站

10g可以用:alter system set recyclebin=off; 和alter system set recyclebin=on;来关闭和开启

11g可以用:alter system set recyclebin=off deferred; 和alter system set recyclebin=on deferred;

注意:system表空间的表不进回收站。

2.闪回表和闪回查询

利用undo数据闪回表，如果数据被覆盖就不能查询。

例：闪回查询：

SQL>set time on;

SQL> delete from emplx;

SQL> commit;

#闪回查询（时间点）

SQL>select * from emplx as of timestamp to_timestamp(‘2014-6-26 15:49:29‘,‘yyyy-mm-dd hh24:mi:ss‘);

#可以利用闪回恢复数据：

SQL> insert into emplx select * from emplx as of timestamp to_timestamp(‘2014-6-26 15:49:29‘,‘yyyy-mm-dd hh24:mi:ss‘);

3.闪回数据库：

闪回数据库是利用闪回日志进行闪回。

开启闪回时，会启动一个进程（rvwr）写闪回日志，记录ddl操作，

[oracle@master ~]$ ps -ef| grep ora_

oracle 5249 1 0 15:33 ? 00:00:00 ora_pmon_prod

oracle 5251 1 0 15:33 ? 00:00:00 ora_vktm_prod

oracle 5255 1 0 15:33 ? 00:00:00 ora_gen0_prod

oracle 5257 1 0 15:33 ? 00:00:00 ora_diag_prod

oracle 5259 1 0 15:33 ? 00:00:00 ora_dbrm_prod

oracle 5261 1 0 15:33 ? 00:00:00 ora_psp0_prod

oracle 5263 1 0 15:33 ? 00:00:01 ora_dia0_prod

oracle 5265 1 0 15:33 ? 00:00:00 ora_mman_prod

oracle 5267 1 0 15:33 ? 00:00:00 ora_dbw0_prod

oracle 5269 1 0 15:33 ? 00:00:00 ora_lgwr_prod

oracle 5271 1 0 15:33 ? 00:00:01 ora_ckpt_prod

oracle 5273 1 0 15:33 ? 00:00:00 ora_smon_prod

oracle 5275 1 0 15:33 ? 00:00:00 ora_reco_prod

oracle 5277 1 0 15:33 ? 00:00:01 ora_mmon_prod

oracle 5279 1 0 15:33 ? 00:00:00 ora_mmnl_prod

oracle 5324 1 0 15:34 ? 00:00:00 ora_rvwr_prod

oracle 5332 1 0 15:34 ? 00:00:00 ora_arc0_prod

oracle 5334 1 0 15:34 ? 00:00:00 ora_arc1_prod

oracle 5336 1 0 15:34 ? 00:00:00 ora_arc2_prod

oracle 5338 1 0 15:34 ? 00:00:00 ora_arc3_prod

oracle 5340 1 0 15:34 ? 00:00:00 ora_qmnc_prod

oracle 5357 1 0 15:34 ? 00:00:00 ora_q000_prod

oracle 5359 1 0 15:34 ? 00:00:00 ora_q001_prod

oracle 5564 1 0 15:44 ? 00:00:00 ora_smco_prod

oracle 6192 1 0 16:14 ? 00:00:00 ora_w000_prod

oracle 6325 4752 0 16:20 pts/2 00:00:00 grep ora_

4.闪回归档（flashback archive）

falshback archive 只能在assm的tablespace上建立。

闪回归档：利用快照。

三.truncate 不能用闪回查询：

四.闪回区的作用：

1.flashback log为了对数据库进行后滚操作而设立的，为了存放flashback log ，oracle建立了闪回区。

2.闪回数据库是用flashacklog ，当数据库方式ddl等变化就记录。

3.开启闪回后，oracle 会在share pool 中添加一个flashback buffer。

4.闪回区存放的文件有：

控制文件

归档日志文件 (归档默认放置的位置)

闪回日志

控制文件和spfile自动备份

rman 备份集

数据文件拷贝

5.数据启动闪回后，rvwr进程就会启动，该进程会向flash recovery area 中写入flashback log；

五.查询闪回区空间使用情况:

#查询闪回区空间使用情况:

SQL> select name,space_limit/1024/1024 space_limit, space_used/1024/1024, space_used,space_reclaimable, number_of_files from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED/1024/1024 SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

-------------------------- ----------- -------------------- ---------- ----------------- ---------------

/home/oracle/prodfalshback 1024 7.8125 8192000 0 1

【oracle11g，13】表空间管理2：undo表空间管理(调优) ，闪回原理

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 【oracle11g，13】表空间管理2：undo表空间管理(调优) ，闪回原理