检查及设置合理的undo表空间

Leshami

发布于 2018-08-13 15:13:11

7070

发布于 2018-08-13 15:13:11

文章被收录于专栏：乐沙弥的世界

UNDO是用于实现并发控制以及构建一致性读，也就是在数据变更之前产生前镜像，以保证用户能够回滚或撤销对数据库所作的修改。是Oracle数据库完整性的重要组成部分。因此合理的设计及配置undo以及使用undo都将对数据库有较大的影响。通常情况下，对于大规模数据的删除，更新操作，我们建议使用分批删除分次提交以减少对undo的占用和冲击。那么对于undo的大小到底应该设置多大？是启用自动扩展还是关闭自动扩展？这个问题仁者见仁，智者见智，见下文。

1、当前数据库环境及undo配置信息

sys@SYTST> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

sys@SYTST> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

sys@SYTST> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb
  2  from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME                FILE_NAME                                               AUT    SIZE_MB
------------------------------ ------------------------------------------------------- --- ----------
UNDOTBS                        /u02/database/SYTST/undo/undoSYTST1.dbf                 NO          20

--创建演示表t
sys@SYTST> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id,
  4         round(5678+dbms_random.normal*1234) AS n1,
  5         mod(255+trunc(dbms_random.normal*1000),255) AS n2,
  6         dbms_random.string('p',255) AS pad
  7  FROM dual
  8  CONNECT BY level <= 100000
  9  ORDER BY dbms_random.value;

Table created.

--直接用脚本得到undo的信息及建议值
sys@SYTST> @chk_advs_undo

Session altered.


- Undo Analysis started at : 24/10/2013 14:39:58 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 17/10/2013 14:39:58
End Time   : 24/10/2013 14:39:58

Current Undo Configuration
--------------------------
Current undo tablespace                                 : UNDOTBS
Current undo tablespace size (datafile size now)        : 20M
Current undo tablespace size (consider autoextend)      : 20M
AUTOEXTEND for undo tablespace is                       : OFF
Current undo retention                                  : 900
UNDO GUARANTEE is set to                                : FALSE

Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK.    --->当前的undo配置合理

Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.

Retention Recommendation
------------------------
The best possible retention with current configuration is    : 5996 Seconds
The longest running query ran for                            : 52 Seconds
The undo retention required to avoid errors is               : 52 Seconds

PL/SQL procedure successfully completed.

2、模拟undo超出并获得建议值

--先查看当前已产生的undo
sys@SYTST> @mystat "undo change"
sys@SYTST> set echo off

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                              363568

--我们来更新之前创建表t上的pad列
sys@SYTST> update t set pad=dbms_random.string('l',255);
update t set pad=dbms_random.string('l',255)
       *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'   --->得到了错误提示，无法扩展undo，因为当前环境undo未启用自动扩展

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

--看看update语句到底产生了多少undo
sys@SYTST> @mystat2  
sys@SYTST> set echo off

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
undo change vector size                                            18817316       18,453,748

--由下可知，上面的update语句已经产生了17M以上的undo,而当前的undo的大小为20M，显然不够，所以我们收到了ORA-30036
sys@SYTST> select 18453748/1024/1024 from dual;

18453748/1024/1024
------------------
        17.5988655

--再次获得undo建议值  
sys@SYTST> @chk_advs_undo

Session altered.

- Undo Analysis started at : 24/10/2013 14:49:07 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 17/10/2013 14:49:07
End Time   : 24/10/2013 14:49:07

Current Undo Configuration
--------------------------
Current undo tablespace                                 : UNDOTBS
Current undo tablespace size (datafile size now)        : 20M
Current undo tablespace size (consider autoextend)      : 20M
AUTOEXTEND for undo tablespace is                       : OFF
Current undo retention                                  : 900
UNDO GUARANTEE is set to                                : FALSE

Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 48 MB

Undo Space Recommendation
-------------------------
Minimum Recommendation           : Size undo tablespace to 48 MB
Rationale                        : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 48M    --->这里给出了undo的建议值

Retention Recommendation
------------------------                  --->下面是一些和undo相关的建议，如可能的保留时间等等
The best possible retention with current configuration is    : 306 Seconds
The longest running query ran for                            : 52 Seconds
The undo retention required to avoid errors is               : 52 Seconds

PL/SQL procedure successfully completed.

3、获得undo信息及建议值脚本

--脚本来自Oracle. 参考Metalink: Doc ID 1579035.1 
--以下脚本适用于Oracle 10g以上版本
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE
    v_analyse_start_time    DATE := SYSDATE - 7;
    v_analyse_end_time      DATE := SYSDATE;
    v_cur_dt                DATE;
    v_undo_info_ret         BOOLEAN;
    v_cur_undo_mb           NUMBER;
    v_undo_tbs_name         VARCHAR2(100);
    v_undo_tbs_size         NUMBER;
    v_undo_autoext          BOOLEAN;
    v_undo_retention        NUMBER(6);
    v_undo_guarantee        BOOLEAN;
    v_instance_number       NUMBER;
    v_undo_advisor_advice   VARCHAR2(100);
    v_undo_health_ret       NUMBER;
    v_problem               VARCHAR2(1000);
    v_recommendation        VARCHAR2(1000);
    v_rationale             VARCHAR2(1000);
    v_retention             NUMBER;
    v_utbsize               NUMBER;
    v_best_retention        NUMBER;
    v_longest_query         NUMBER;
    v_required_retention    NUMBER;
BEGIN
    select sysdate into v_cur_dt from dual;
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);
    
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
    IF V_UNDO_AUTOEXT THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');  
    ELSE
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');  
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

    IF v_undo_guarantee THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
    ELSE
        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE(CHR(9));

    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
    DBMS_OUTPUT.PUT_LINE('---------------------------');

    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
    DBMS_OUTPUT.PUT_LINE('-------------------------');

    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
    IF v_undo_health_ret > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);
        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);
        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
    END IF;
    
    SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;
/

4、后记 a、undo究竟要多大，推荐的做法是根据你的业务高峰期得到一个峰值，然后适当高于峰值来启用自动扩展。 b、undo保留时间与所需的空间成正比，保留时间越长，空间开销越大，不过出现ORA-01555的几率也就越低。 c、在未启用自动扩展的情形下，对于新的事务，UNDO_RETENTION会回收空闲空间，并重新使用，如果启用自动扩展，则该功能被忽略，而是先扩展。 d、小的undo表空间(数据文件)通常情况下对于数据库及操作系统影响更小，具有更好的性能以及减少备份所需的开销等。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2013年10月24日，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库