undo系列学习之如何计算最优的undo_retention及undo_retention的初体验

undo系列学习之如何计算最优的undo_retention及undo_retention的初体验

 

undo_retention简单定义，就是最多数据的最少保留时间。AUM模式下，undo_retention参数用于事务commit后undo数据保留的时间。单位为秒。这是个no guarantee的限制。也就是，若空间足够，他只是个‘花瓶’；当可用空间不足而又有事务需要回滚空间，则这些数据依然会被覆盖。这个行为可能会导致ORA-01555错误，这些数据被记忆的时间可用v$undostat里面的字段TUNED_UNDORETENTION来查询。

  www.2cto.com  

    很多时候，我们希望undo数据能够被留存，而不是被覆盖。那么在10g，oracle对undo增加了guarantee控制，也就是，用户可以指定undo表空间必须满足undo_retention的限制。

[sql] 

alter tablespace undotbs1 retention guarantee|noguarantee；  

 

    通过设置期望的保留时间，修改undo表空间属性，就可以使undo表空间运行在guarantee模式。下面我们用一个实验体验一下，noguarantee和guarantee有什么区别：

[sql] 

sys@ORCL> select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like &＃39;%UNDOTBS%&＃39;;  

  

TABLESPACE_NAME                CONTENTS  RETENTION  

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

UNDOTBS1                       UNDO      NOGUARANTEE  

  

sys@ORCL> alter system set undo_retention=800;  

  

System altered.  

  

sys@ORCL> alter tablespace undotbs1 retention guarantee;  

    www.2cto.com  

Tablespace altered.  

  

sys@ORCL> select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like &＃39;%UNDOTBS%&＃39;;  

  

TABLESPACE_NAME                CONTENTS  RETENTION  

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

UNDOTBS1                       UNDO      GUARANTEE  

将undo表空间自动扩展属性取消  

sys@ORCL> select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files where tablespace_name like &＃39;%UNDOTBS%&＃39;;  

FILE_NAME                                                                TABLESPACE_NAME                         M  

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

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8050fkc6_.dbf       UNDOTBS1                               30  

sys@ORCL> alter database datafile &＃39;/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8050fkc6_.dbf&＃39; autoextend off;  

  

Database altered.  

尝试循环小批量删除数据，在guarantee设置下，很快会出现ORA-30036错误：  

hr@ORCL> select count(*) from t;  

 www.2cto.com  

  COUNT(*)  

----------  

   1462140  

  

hr@ORCL> begin  

  2        for i in 1..1000  

  3       loop  

  4         delete from t where rownum<1001;  

  5          commit;  

  6       end loop;  

  7      end;  

  7  /  

begin  

*  

ERROR at line 1:  

ORA-30036: unable to extend segment by 8 in undo tablespace &＃39;UNDOTBS1&＃39;  

ORA-06512: at line 4  

hr@ORCL> select count(*) from t;  

  

  COUNT(*)  

----------  

   14620000  

而在修改了undo表空间retention属性后，删除可以顺利完成  

hr@ORCL> begin  

  2        for i in 1..1000   

  3       loop    www.2cto.com  

  4         delete from t where rownum<1001;  

  5         commit;  

  6       end loop;  

  7      end;  

  8  /  

PL/SQL procedure successfully completed.  

 

    undo_retention的设置可尽责于闪回功能。对于列的类型为LOB的，自动undo数据的记忆是不支持LOBs，必须设置undo_retention的值来尽力挽留之。oracle在undo segment header上创建一个retention table用于记录相关undo存储的提交时间，从而实现其保留策略。

 

    最优的undo_retention如何计算

    模糊计算可以借助：

如果你的事务隔离级别是 serializable或read only ，你可以让undo_retention稍微长于最长运行的事务；

       如果你的事务隔离级别是read commited，你可以让undo_retention稍微长于最长运行的语句（DML)。

  www.2cto.com  

    精确计算可以借助公式：

    我们需要选一段比较有代表性的时间段来测试，或者去多段，然后加权平均

[sql] 

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",  

SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",  

ROUND((d.undo_size / (to_number(f.value) *  

g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"  

FROM (  

SELECT SUM(a.bytes) undo_size  

FROM v$datafile a,  

v$tablespace b,  

dba_tablespaces c  

WHERE c.cOntents= &＃39;UNDO&＃39;  

AND c.status = &＃39;ONLINE&＃39;  

AND b.name = c.tablespace_name  

AND a.ts# = b.ts#  

) d,  www.2cto.com  

v$parameter e,  

v$parameter f,  

(  

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec  

FROM v$undostat  

) g  

WHERE e.name = &＃39;undo_retention&＃39;  

AND f.name = &＃39;db_block_size&＃39;  

  

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]            OPTIMAL UNDO RETENTION [Sec]  

  

                     30    900                                                   40421  

 

作者 linwaterbin