(2010-06-19)关于undo表空间大小设定的讨论

     最近准备开始看看OCP试题了，不为考试，只为检测下自己的水平和从试题中去查看相关的资料来丰富自己的知识面。这不，刚看到第一道题就卡住了，答案选错了。第一道题是：选出三项决定undo tablespace的因素，对于undo表空间的资料很久之前看过了，现在又忘了，算了，重新温习下吧。温故而知新嘛。

  言归正传。

 

1.undo_retention值越大，需要的undo表空间越大
         undo_retention值表示一个undo数据块最久能在undo表空间中停留的时间,单位是秒，缺省值为900。（问：如果已经超过了这个时间，而事务还么有提交，此时该undo数据块可以被覆盖吗？）
         undo表空间资源是循环使用的，在undo_retention时间内，undo数据块的内容是不会被释放掉，也即不会被新的undo数据覆盖。所以设置停留时间越久，undo表空间就越大。

 

2.工作量很大，业务量大，事务很多时，每秒钟所产生undo数据块的个数越多，需要的undo表空间越大。

 

3.和db_block_size的大小有关

  select begin_time,end_time,undoblks
    from v$undostat;
 
  BEGIN_TIME           END_TEME             UNDOBLKS
  ------------------  -------------------  ----------
  28-SEP-O8 13:43:02  28-SEP-O8 13:44:18        19
  28-SEP-O8 13:33:02  28-SEP-O8 13:43:18      1474
  28-SEP-O8 13:23:02  28-SEP-O8 13:33:18      1347
  28-SEP-O8 13:13:02  28-SEP-O8 13:23:18      16 28
        此语句记录了undo数据块的历史使用情况，每隔10分钟刷一次。此结果表示记录前40分钟分别用到undo数据块19个、1474个、1347个、1628个；

  select addr,used_ublk
    from v$transaction;
 
  ADDR         USED_UBLK
 ---------   --------------
 5932F4A0           863

    此语句可以查看当前事务所需要数据块的个数，此结果表示，当前用户只有一个事务正在执行，此事务需要863个undo数据块。
    事务越多，操作影响数据越多，需要的undo数据块也越多。

 

如何计算所需undo表空间的大小：
1.计算业务高峰期每秒产生undo数据块的个数：
SQL> select max(undoblks / ((end_time - begin_time)*24*3600))
SQL>  from undostat;

2.得到undo数据块在undo表空间中可以保留的最长时间
show parameter undo_retention

3.得到数据块大小
show parameter db_block;

4.将以上三者的数据相乘就是所需undo表空间的大小数。

 

发现undo表空间不够的时候，赶紧增加undo表空间的大小，执行语句如下：
alter tablespace undotbs
  add datafile '/u01/oradata/undotbs2.dbf' size 700M
  autoextend on;

 

ORA-01555
  查询失败，其他事务产生的undo数据覆盖了undo表空间中查询需要的old数据块。

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

删除undo tablespace实验：

SQL> create undo tablespace undotbs2 

  2  datafile '/opt/oradata/oradata/orcl/undotbs02.dbf' SIZE 100m

  3  autoextend off;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string      AUTO
undo_retention                       integer     10800
undo_suppress_errors        boolean     FALSE
undo_tablespace                    string      UNDOTBS1

SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> select segment_name ,tablespace_name ,segment_id from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID
------------------------------ ------------------------------ ----------
SYSTEM                         SYSTEM                                  0
_SYSSMU1$                      UNDOTBS1                                1
_SYSSMU2$                      UNDOTBS1                                2
_SYSSMU3$                      UNDOTBS1                                3
_SYSSMU4$                      UNDOTBS1                                4
_SYSSMU5$                      UNDOTBS1                                5
_SYSSMU6$                      UNDOTBS1                                6
_SYSSMU7$                      UNDOTBS1                                7
_SYSSMU8$                      UNDOTBS1                                8
_SYSSMU9$                      UNDOTBS1                                9
_SYSSMU10$                     UNDOTBS1                               10

SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID
------------------------------ ------------------------------ ----------
_SYSSMU11$                     UNDOTBS2                               11
_SYSSMU12$                     UNDOTBS2                               12
_SYSSMU13$                     UNDOTBS2                               13
_SYSSMU14$                     UNDOTBS2                               14
_SYSSMU15$                     UNDOTBS2                               15
_SYSSMU16$                     UNDOTBS2                               16
_SYSSMU17$                     UNDOTBS2                               17
_SYSSMU18$                     UNDOTBS2                               18
_SYSSMU19$                     UNDOTBS2                               19
_SYSSMU20$                     UNDOTBS2                               20

21 rows selected.

SQL> select usn,status,xacts from v$rollstat;

       USN         STATUS               XACTS
-------------   ---------------             -------------
         0               ONLINE                   0
        10   PENDING OFFLINE          1
        11              ONLINE                   0
        12              ONLINE                   0
        13              ONLINE                   0
        14              ONLINE                   0
        15              ONLINE                   0
        16              ONLINE                   0
        17              ONLINE                   0
        18              ONLINE                   0
        19              ONLINE                   1

       USN         STATUS               XACTS
-------------   ---------------             -------------
        20              ONLINE                   0

12 rows selected.

发现原UNDOTBS1的回滚段处于PENDING OFFLINE状态，并有一个事物存在，需要等到事务完成后，才能drop UNDOTBS1

SQL> select usn,status,xacts from v$rollstat;

       USN         STATUS               XACTS
-------------   ---------------             -------------
         0               ONLINE                   0
        10   PENDING OFFLINE          0
        11              ONLINE                   0
        12              ONLINE                   0
        13              ONLINE                   0
        14              ONLINE                   0
        15              ONLINE                   0
        16              ONLINE                   0
        17              ONLINE                   0
        18              ONLINE                   0
        19              ONLINE                   1

       USN         STATUS               XACTS
-------------   ---------------             -------------

        20              ONLINE                   0

12 rows selected.

SQL> select usn,status,xacts from v$rollstat;

       USN         STATUS               XACTS
-------------   ---------------             -------------
         0              ONLINE                   0
        11              ONLINE                   0
        12              ONLINE                   0
        13              ONLINE                   0
        14              ONLINE                   0
        15              ONLINE                   0
        16              ONLINE                   0
        17              ONLINE                   0
        18              ONLINE                   0
        19              ONLINE                   1
        20              ONLINE                   0

11 rows selected.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>