（原）Oracle事务与Undo段的分配过程

我们都知道，Oracle在进行dml过程中，有重要的一步，即创建undo和redo。redo用于事务重演，而undo用于事务的回退。

创建undo，记录data block的前映像，需要在undo tablespace 中分配undo segment，来记录undo record。

最近一个oracle群里遇到一个事务引起undo tablespace的一个undo segment暴涨的情况，群里也进行了讨论，其中有Q友发出了oracle doc中关于dml事务

对undo segment 的分配和使用情况，如下：

。

群里讨论最后，我也来测试下这个过程及结论。

（1）：准备工作

--创建一个新的undo tablespace，大小640k，数据文件禁止自动扩展，用于替换原来的undo tablespace.
--之前已经测试过一个初始的undo segment 大小为：128*1024=128k,这里指定这个数据文件大小:128k*5 = 640k
create undo  tablespace  undotbs5  DATAFILE 'E:/oradata/undotbs5.dbf'   SIZE 640k reuse autoextend off;

--切换回滚段： alter system set undo_tablespace=undotbs5 scope=both;
--把原来的undo 表空间离线，并删除（这里必须是原来的undo segment都要offline，才能删除undo tablespace）
alter tablespace undotbs1 offline;
drop tablespace undotbs1 including contents and datafiles;

(2)：创建事务操作的测试表

create table test(
id number,
name varchar2(20));


begin
    for i in 1..20 loop
        insert into test(id,name) values(i,'sina' || i);
    end loop;
    commit;
end;
/

（3）：查看目前undo tablespace的表空间及数据文件

--查看undo 表空间
col tablespace_name for a20
col status for a10
set lines 200
select tablespace_name,block_size,min_extents,max_extents,status,contents,extent_management,allocation_type from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME      BLOCK_SIZE MIN_EXTENTS MAX_EXTENTS STATUS     CONTENTS           EXTENT_MANAGEMENT    ALLOCATION_TYPE
-------------------- ---------- ----------- ----------- ---------- ------------------ -------------------- ------------------
UNDOTBS5                   8192           1  2147483645 ONLINE     UNDO               LOCAL             SYSTEM


--查看undo segment
col segment_name for a30
select segment_name,segment_type,bytes/1024 from dba_segments where tablespace_name='UNDOTBS5';

SEGMENT_NAME                   SEGMENT_TYPE                         BYTES/1024
------------------------------ ------------------------------------ ----------
_SYSSMU5$                      TYPE2 UNDO                                  128
_SYSSMU6$                      TYPE2 UNDO                                  192
_SYSSMU7$                      TYPE2 UNDO                                  128
_SYSSMU8$                      TYPE2 UNDO                                  128

我们可以看到，此undo tablespace包括4个undo segment,一个undo segment段的初始物理大小为128k，其中_SYSSMU6$为192k. 


--查询undo表空间的数据文件，我们这里把这个数据文件大小固定，且不能自动扩展
col file_name for a40
col tablespace_name for a20
set lines 200
select file_name,tablespace_name,bytes/1024 size_kb,autoextensible,maxbytes/1024/1024,user_bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS5';

FILE_NAME                                TABLESPACE_NAME         SIZE_KB AUTOEX MAXBYTES/1024/1024 USER_BYTES/1024/1024
---------------------------------------- -------------------- ---------- ------ ------------------ --------------------
E:\ORADATA\UNDOTBS5.DBF                  UNDOTBS5                    640 NO                      0        .5625

我们可以看到，undo tablespace只包括一个数据文件，且此数据文件大小 为640k，不能自动扩展。

 

(4)：更改一个undo segment为offline状态

--查询目前undo tablespace 已经分配的所有的undo segment，我们可以看到online状态和offline状态的
set lines 200
col segment_name for a20
col tablespace_name for a20
col status for a10
select segment_id,segment_name,tablespace_name,file_id,min_extents,max_extents,status from dba_rollback_segs order by 1;

SEGMENT_ID SEGMENT_NAME         TABLESPACE_NAME         FILE_ID MIN_EXTENTS MAX_EXTENTS STATUS
---------- -------------------- -------------------- ---------- ----------- ----------- ----------
         0 SYSTEM               SYSTEM                        1           1       32765 ONLINE
         5 _SYSSMU5$            UNDOTBS5                      2           2       32765 ONLINE
         6 _SYSSMU6$            UNDOTBS5                      2           2       32765 ONLINE
         7 _SYSSMU7$            UNDOTBS5                      2           2       32765 ONLINE
         8 _SYSSMU8$            UNDOTBS5                      2           2       32765 ONLINE
         
我们看到，四个undo segment都是online，这里我们手工把一个undo segment改为offline状态

SQL> show user
USER 为 "SYS"
SQL> alter system set "_smu_debug_mode"=4;

系统已更改。

SQL> alter rollback segment "_SYSSMU8$" offline;

回退段已变更。

--再次查看
set lines 200
col segment_name for a20
col tablespace_name for a20
col status for a10
select segment_id,segment_name,tablespace_name,file_id,min_extents,max_extents,status from dba_rollback_segs order by 1;

SEGMENT_ID SEGMENT_NAME         TABLESPACE_NAME         FILE_ID MIN_EXTENTS MAX_EXTENTS STATUS
---------- -------------------- -------------------- ---------- ----------- ----------- ----------
         0 SYSTEM               SYSTEM                        1           1       32765 ONLINE
         5 _SYSSMU5$            UNDOTBS5                      2           2       32765 ONLINE
         6 _SYSSMU6$            UNDOTBS5                      2           2       32765 ONLINE
         7 _SYSSMU7$            UNDOTBS5                      2           2       32765 ONLINE
         8 _SYSSMU8$            UNDOTBS5                      2           2       32765 OFFLINE

我们看到 _SYSSMU8$ 的undo segment已经由online变成了offline.

--查询目前online的undo segment的状态。
--xacts不为0，表示该undo segment上有active的事务，如果>1，则说明有回滚段争用，需要增加回滚段数目。
set lines 200
col name for a25
col status for a10
select a.name, b.extents, b.rssize/1024/1024,b.xacts,b.waits,b.gets,b.optsize,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn;

NAME                         EXTENTS B.RSSIZE/1024/1024      XACTS      WAITS       GETS    OPTSIZE STATUS
------------------------- ---------- ------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM                             6           .3671875          0          0         85            ONLINE
_SYSSMU5$                          3           .1796875          0          0       1967            ONLINE
_SYSSMU6$                          2           .1171875          0          0       2407            ONLINE
_SYSSMU7$                          2           .1171875          0          0       2301            ONLINE

此时没有_SYSSMU8$这个undo segment，上面我们已经手动让其offline。

(5)：进行测试
--创建N个会话，同时执行update test表的操作，这时每个active session将会占用一个undo segment，如果online 的undo segment不够，刚先将offline的变成online来使用。如果offline的用完，则自动增加,一直到undo tablespace 没有空间（这里undo tablespace 只有一个数据文件，只有640k，所以很快就会被用完）。此时，每个undo segment里只有一个active的事务，如果此时还有事务，则根据一定算法，将已经含有acitve的undo segment里再分配一个active的事务，即一个undo segment里含有两个active状态的事务。

session1:
update test set name ='gogoxx' where id = 1;

session2:
update test set name ='gogoxx' where id = 2;

session3:
update test set name ='gogoxx' where id = 3;


在 session0 里查看：
set lines 200
col name for a25
col status for a10
select a.name, b.extents, b.rssize/1024/1024,b.xacts,b.waits,b.gets,b.optsize,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn;

NAME                         EXTENTS B.RSSIZE/1024/1024      XACTS      WAITS       GETS    OPTSIZE STATUS
------------------------- ---------- ------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM                             6           .3671875          0          0         91            ONLINE
_SYSSMU5$                          3           .1796875          1          0       2262            ONLINE
_SYSSMU6$                          2           .1171875          1          0       2575            ONLINE
_SYSSMU7$                          2           .1171875          1          0       2581            ONLINE

只有三个segment处于online，且每个里面只有一个active的事务。

select segment_id,segment_name,tablespace_name,file_id,min_extents,max_extents,status from dba_rollback_segs order by 1;

SEGMENT_ID SEGMENT_NAME         TABLESPACE_NAME         FILE_ID MIN_EXTENTS MAX_EXTENTS STATUS
---------- -------------------- -------------------- ---------- ----------- ----------- ----------
         0 SYSTEM               SYSTEM                        1           1       32765 ONLINE
         5 _SYSSMU5$            UNDOTBS5                      2           2       32765 ONLINE
         6 _SYSSMU6$            UNDOTBS5                      2           2       32765 ONLINE
         7 _SYSSMU7$            UNDOTBS5                      2           2       32765 ONLINE
         8 _SYSSMU8$            UNDOTBS5                      2           2       32765 OFFLINE

此时 _SYSSMU8$ 处于offline。

 

再执行session4:

update test set name ='gogoxx' where id = 4;

在 session0 里查看：
set lines 200
col name for a25
col status for a10
select a.name, b.extents, b.rssize/1024/1024,b.xacts,b.waits,b.gets,b.optsize,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn;

NAME                         EXTENTS B.RSSIZE/1024/1024      XACTS      WAITS       GETS    OPTSIZE STATUS
------------------------- ---------- ------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM                             6           .3671875          0          0         97            ONLINE
_SYSSMU5$                          3           .1796875          1          0       2264            ONLINE
_SYSSMU6$                          2           .1171875          1          0       2577            ONLINE
_SYSSMU7$                          2           .1171875          1          0       2583            ONLINE
_SYSSMU8$                          2           .1171875          1          0       1864            ONLINE

此时_SYSSMU8$已经被online且包含了一个事务。

set lines 200
col segment_name for a20
col tablespace_name for a20
col status for a10
select segment_id,segment_name,tablespace_name,file_id,min_extents,max_extents,status from dba_rollback_segs order by 1;

SEGMENT_ID SEGMENT_NAME         TABLESPACE_NAME         FILE_ID MIN_EXTENTS MAX_EXTENTS STATUS
---------- -------------------- -------------------- ---------- ----------- ----------- ----------
         0 SYSTEM               SYSTEM                        1           1       32765 ONLINE
         5 _SYSSMU5$            UNDOTBS5                      2           2       32765 ONLINE
         6 _SYSSMU6$            UNDOTBS5                      2           2       32765 ONLINE
         7 _SYSSMU7$            UNDOTBS5                      2           2       32765 ONLINE
         8 _SYSSMU8$            UNDOTBS5                      2           2       32765 ONLINE

这里也显示_SYSSMU8$  由原来的offline变成了online.

执行 session5:

update test set name ='gogoxx' where id = 5;

在session 0里查看
set lines 200
col name for a25
col status for a10
select a.name, b.extents, b.rssize/1024/1024,b.xacts,b.waits,b.gets,b.optsize,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn;

NAME                         EXTENTS B.RSSIZE/1024/1024      XACTS      WAITS       GETS    OPTSIZE STATUS
------------------------- ---------- ------------------ ---------- ---------- ---------- ---------- --------
SYSTEM                             6           .3671875          0          0         65            ONLINE
_SYSSMU5$                          2           .1171875          1          0         71            ONLINE
_SYSSMU6$                          3           .1796875          2          0         87            ONLINE
_SYSSMU7$                          2           .1171875          1          0         87            ONLINE
_SYSSMU8$                          2           .1171875          1          0        130            ONLINE

我们此时看到 _SYSSMU6$ 的XACTS字段里的1变成了2，即此时，这个undo segment里已经有两个active的事务，说明，此时undo segment已经出现了争用。

 

由上证明了oracle doc里关于undo segment分配与事务的关系，这下关于undo 也搞得更清楚一点了。