前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >检查及设置合理的undo表空间

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

作者头像
Leshami
发布2018-08-13 15:13:11
7060
发布2018-08-13 15:13:11
举报
文章被收录于专栏:乐沙弥的世界

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

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

代码语言:javascript
复制
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超出并获得建议值

代码语言:javascript
复制
--先查看当前已产生的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信息及建议值脚本 

代码语言:javascript
复制
--脚本来自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 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档