undo自动调优介绍

Oracle 10gr2的后续版本中添加了撤销(UNDO)信息最短保留时间段自动调优的特性,不再仅仅依据参数UNDO_RETENTION的设定,其调优原则如下:

l  当撤销表空间(UNDO TABLESPACE)大小固定,Oracle将根据表空间的大小和实际的系统负载动态调整撤销信息保存时间,该最短保存时间的具体长短基于撤销表空间大小的一定比例值公式换算后获得;它总是比设定的UNDO_RETENTION大,当撤销表空间大量空闲情况下可能远远大于UNDO_RETENTION。

l  当撤销表空间设定为自动扩展空间情况下,Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者,即MAX((MAXQUERYLEN+300),UNDO_RENTION);同样的,该最短保存时间可能远远大于设定的UNDO_RETENTION。

在自动调整情况下,实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。

在无法就撤销表空间做相应修改的情况,我们可以通过修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后,V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新,且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

Comments

  1. admin says

    Hdr: 5387030 10.2.0.2.0 RDBMS 10.2.0.2.0 TXN MGMT LOCAL PRODID-5 PORTID-212
    Abstract: AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS

    So when:
    a) not using GUARANTEED retention at undo tablespace level and
    b) not using auto-extensible data files (fixed sized data file). then the
    tuned_undoretention stays high to satisfy the maxquerylen at that time and
    causes out of undo space error. The tuned_undoretention value did not return
    to
    a lower value even after 36 hours of quiet time (not a single query was
    submitted).

    Then, we changed undo tablespace’s data files to be auto_extensible, but did
    not allow it to actually extend any further. The max size was set to the
    current allocated size.

    With this change the tuned_undoretention returned to the set value for und
    o_retention parameter immediately after the long running query completed.
    This is same behavior we have seen in 10g R1 without the need for
    auto_extensible data files.
    And that is the difference in 10g R2 from R1. This change forces the bogus
    use
    of auto_extensible option.

    I will upload a text file for you to review in support of what I mentioned
    here.
    It is causes confusion and misleading to customer needs.

    So, this workaround is not a big deal, but 10g R2 is not backward compatible
    to 10g R1 in this respect. And is not clearly documented as such. If there is
    no chance to change this to match what 10g R1did, then I think a Metalink
    Note to that effect will clear things up for all who may run into this
    issue.

    SQL> show parameter undo

    NAME TYPE VALUE
    ———————————— ———–
    ——————————
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDO_TBS
    SQL>

    SQL> alter session set nls_date_format=’mm/dd/yy hh24:mi:ss’;

    Session altered.

    SQL> set lines 132
    SQL> get f1.sql
    1 select begin_time
    2 , end_time
    3 , undoblks
    4 , tuned_undoretention
    5 , maxquerylen
    6 , maxqueryid
    7* from v$undostat
    SQL> /

    BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
    MAXQUERYLEN MAXQUERYID
    —————– —————– ———- ——————-
    ———– ————-
    07/05/06 14:32:23 07/05/06 14:37:25 1 900
    0
    07/05/06 14:22:23 07/05/06 14:32:23 3 900
    0
    07/05/06 14:12:23 07/05/06 14:22:23 4 900
    0
    07/05/06 14:02:23 07/05/06 14:12:23 2 900
    0
    07/05/06 13:52:23 07/05/06 14:02:23 50 900
    0
    07/05/06 13:42:23 07/05/06 13:52:23 5 900
    0
    07/05/06 13:32:23 07/05/06 13:42:23 2 900
    0
    07/05/06 13:22:23 07/05/06 13:32:23 4 900
    0
    07/05/06 13:12:23 07/05/06 13:22:23 6 900
    0
    07/05/06 13:02:23 07/05/06 13:12:23 0 900
    0
    07/05/06 12:52:23 07/05/06 13:02:23 63 900
    0

    BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
    MAXQUERYLEN MAXQUERYID
    —————– —————– ———- ——————-
    ———– ————-
    07/05/06 12:42:23 07/05/06 12:52:23 265 1800
    962 271b023vduant
    07/05/06 12:32:23 07/05/06 12:42:23 449 1201
    362 271b023vduant
    07/05/06 12:22:23 07/05/06 12:32:23 404 2948
    2108 271b023vduant
    07/05/06 12:12:23 07/05/06 12:22:23 372 2348
    1506 271b023vduant
    07/05/06 12:02:23 07/05/06 12:12:23 353 1747
    906 271b023vduant
    07/05/06 11:52:23 07/05/06 12:02:23 513 1421
    582 271b023vduant
    07/05/06 11:42:23 07/05/06 11:52:23 169 1005
    164 fby3wmzn3hzvw
    07/05/06 11:32:23 07/05/06 11:42:23 3 900
    0
    07/05/06 11:22:23 07/05/06 11:32:23 2 900
    0
    07/05/06 11:12:23 07/05/06 11:22:23 11 900
    0
    07/05/06 11:02:23 07/05/06 11:12:23 4 900
    0

    SQL> get f2.sql
    1 select tablespace_name
    2 , retention
    3 from dba_tablespaces
    4* where tablespace_name = ‘UNDO_TBS’
    SQL> /

    TABLESPACE_NAME RETENTION
    —————————— ———–
    UNDO_TBS NOGUARANTEE

    SQL> col file_name for a45
    SQL>
    SQL> get f3.sql
    SQL> get f3.sql
    1 select file_name
    2 , autoextensible
    3 , bytes/1048576 MB
    4 , increment_by
    5 , maxbytes/1048576 Max_MB
    6 from dba_data_files
    7* where tablespace_name = ‘UNDO_TBS’
    SQL> /

    FILE_NAME AUT MB INCREMENT_BY
    MAX_MB
    ——————————————— — ———- ————
    ———-
    /u10/oradata/VZCT/undo_tbs_02.dbf YES 3000 128
    3000
    /u11/oradata/VZCT/undo_tbs_01.dbf YES 2000 128
    2000
    /u13/oradata/VZCT/undo_tbs_03.dbf YES 1000 128
    1000

    TAR: 5337754.992
    —-

    PROBLEM:
    ——–

    1) Unused Undo segments are not being release, originally the database was
    running on 10.1 without problem, but after upgrade to release 10.2.0.2 the
    problem started.

    2) UNDO_RETENTION parameter was correctly set = 900

    3) Undo Tablespace was fixed sized, in other word the Undo tablespace was not
    enabled with Autoextensible option for the associated datafiles.

    4) Also, the Undo tablespace was never enabled with the guaranty retention
    option.

    5) So, after upgrade to 10.2 the problem is the TUNED_UNDORETENTION stayed
    very high causing UNDO_TABLESPACE run out of disk space and it is not
    releasing the unused undo segments.

    6) The Oracle Undo Advisor is always asking to increase the Undo tablespace
    (e.g. 17032 MB) which is not acceptable.

    7) TUNED_UNDORETENTION column (v$undostat) is always increasing and never
    returning back to 900 as on 10gR1:
    =============================================================
    SQL> get f1.sql
    1 select begin_time, end_time, undoblks, tuned_undoretention, maxquerylen,
    maxqueryid
    2* from v$undostat
    SQL> /

    BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
    MAXQUERYLEN MAXQUERYID
    —————– —————– ———- ——————-
    ———– ————-
    04/15/06 12:37:38 04/15/06 12:40:00 1443 61233
    36031 gf37j3d3q2h15
    04/15/06 12:27:38 04/15/06 12:37:38 6285 61261
    36031 gf37j3d3q2h15
    04/15/06 12:17:38 04/15/06 12:27:38 5450 60206
    35430 gf37j3d3q2h15
    04/15/06 12:07:38 04/15/06 12:17:38 6348 60110
    34830 gf37j3d3q2h15
    04/15/06 11:57:38 04/15/06 12:07:38 5005 60094
    34229 gf37j3d3q2h15
    04/15/06 11:47:38 04/15/06 11:57:38 5440 59954
    33628 gf37j3d3q2h15
    04/15/06 11:37:38 04/15/06 11:47:38 6662 58994
    33028 gf37j3d3q2h15
    04/15/06 11:27:38 04/15/06 11:37:38 5197 58995
    32427 gf37j3d3q2h15
    04/15/06 11:17:38 04/15/06 11:27:38 5597 59184
    31826 gf37j3d3q2h15
    04/15/06 11:07:38 04/15/06 11:17:38 6035 59943
    31225 gf37j3d3q2h15

    04/15/06 06:47:38 04/15/06 06:57:38 5185 56068
    15607 gf37j3d3q2h15
    04/15/06 06:37:38 04/15/06 06:47:38 5002 54669
    15007 gf37j3d3q2h15
    04/15/06 06:27:38 04/15/06 06:37:38 4905 54491
    14406 gf37j3d3q2h15

    04/13/06 21:57:38 04/13/06 22:07:38 3474 345600
    213 0mdbrtbvdd9gu
    04/13/06 21:47:38 04/13/06 21:57:38 25 345600
    0
    04/13/06 21:37:38 04/13/06 21:47:38 22 345600
    0
    04/13/06 21:27:38 04/13/06 21:37:38 96 345600
    0

    236 rows selected.
    =============================================================

    DIAGNOSTIC ANALYSIS:
    ——————–

    1) This behavior was diagnosed through the v$undostat view (OWC session)

    2) Please check the undo_retention_too_much.txt log file.

    WORKAROUND:
    ———–

    None.

    RELATED BUGS:
    ————-

    None

    REPRODUCIBILITY:
    —————-

    It is always reproducible in the customer’s environment.

    TEST CASE:
    ———-

    None

    STACK TRACE:
    ————

    SUPPORTING INFORMATION:
    ———————–

    Analyst: Esteban Bernal
    AIM: ebernalus

    24 HOUR CONTACT INFORMATION FOR P1 BUGS:
    —————————————-

    DIAL-IN INFORMATION:
    ——————–

    IMPACT DATE:
    ————

    REDISCOVERY INFORMATION:
    Look for:
    – System Managed Undo
    undo_management=auto in init.ora
    – Fixed size undo tablespace
    select autoextensible from dba_data_files
    where tablespace_name=’
    returns “No” for all the undo ts datafiles.
    – The undo tablespace is already sized such that
    it always has more than enough space to store
    all the undo generated within the undo_retention
    time, and the in-use undo space never exceeds the
    undo tablespace warning alert threshold (see
    below for the query to show the thresholds).
    – The tablespace threshold alerts recommend that
    the DBA add more space to the undo tablespace:
    select creation_time, metric_value, message_type,
    reason, suggested_action
    from dba_outstanding_alerts
    where object_name=’
    ‘;
    returns a suggested action of:
    “Add space to the tablespace”
    Or, this recommendation has been reported in the
    past but the condition has now cleared:
    select creation_time, metric_value, message_type,
    reason, suggested_action, resolution
    from dba_alert_history
    where object_name=’
    ‘;
    – The undo tablespace in-use space exceeded the
    warning alert threshold at some point in time:
    To see the warning alert percentage threshold:
    select object_type, object_name,
    warning_value, critical_value
    from dba_thresholds
    where object_type=’TABLESPACE’;
    To see the (current) undo tablespace percent of
    space in-use:
    select
    ((select (nvl(sum(bytes),0))
    from dba_undo_extents
    where tablespace_name=’

    and status in (‘ACTIVE’,’UNEXPIRED’)) *100) /
    (select sum(bytes)
    from dba_data_files
    where tablespace_name=’
    ‘)
    “PCT_INUSE”
    from dual;
    WORKAROUND:
    There are 3 possible alternate workarounds (any one of
    these should resolve the problem of the alerts triggering
    unnecessarily):
    1. Set the autoextend and maxsize attribute of each datafile
    in the undo ts so it is autoextensible and its maxsize is
    equal to its current size so the undo tablespace now has
    the autoextend attribute but does not autoend:
    alter database datafile ‘
    autoextend on maxsize ;
    With this setting, v$undostat.tuned_undoretention is not
    calculated based on a percentage of the undo tablespace
    size, instead v$undostat.tuned_undoretention is set to
    the maximum of:
    maxquerylen secs + 300
    undo_retention specified in init.ora
    2. Set the following hidden parameter:
    in init.ora: _smu_debug_mode=33554432
    or use: alter system set “_smu_debug_mode” = 33554432;
    With this setting, v$undostat.tuned_undoretention is not
    calculated based on a percentage of the fixed size undo
    tablespace, instead v$undostat.tuned_undoretention is
    set to the maximum of:
    maxquerylen secs + 300
    undo_retention specified in init.ora
    3. Set the following hidden parameter:
    in init.ora: _undo_autotune = false
    or use: alter system set “_undo_autotune” = false;
    With this setting, v$undostat (and therefore
    v$undostat.tuned_undoretention) is not maintained and
    and the undo_retention used is the one specified in
    init.ora
    RELEASE NOTES:
    ]]In 10.2, for a non-autoextend SMU undo tablespace which
    ]]is already adequately sized to handle the transaction
    ]]workload for the specified undo_retention time, the
    ]]tablespace threshold alert mechanism may still recommend
    ]]that the DBA add more space to the undo tablespace.

  2. On an instance with
    – Automatic Undo Management
    – High number of concurrent transactions
    – Many offline undo segs
    – Space Pressure on the UNDO TS

    following an instance recycle the intial value of V$UNDOSTAT.TUNED_UNDORETENTION
    can be calculated too high preventing undo blocks to be expired and their allocated
    space is not immediatly reused.

    BEGIN TXN MAXQUERY UNEXPIRED EXPIRED TUNED_UNDO
    TIME COUNT LENGHT BLKS BLKS RETENTION
    ——– —– ——- ———- —— ———-
    10:09:58 3082 138 12458840 53792 345600
    10:19:58 24178 432 12458840 53792 345600
    10:29:58 90407 344 12446816 51048 182905

    As the number of transactions increases so is their need for space but if there is
    little space free because most is still allocated to unexpired blocks the sessions
    first search for free space in offline undo segments (OfUS).
    If there are many of OfUS the search for space can generate lots of hits on
    dc_rollback_segments, the latch and US enqueue.

    Top 5 Wait Events Waits Avg Wt(ms) %Total Call Time Wt
    ———————— ———— ———- —-
    latch: row cache objects 36,521,692 3 31.3

    Cache Get Requests
    ——————– ————
    dc_rollback_segments 274,882,689

    Latch Name Where Sleeps Waiter Sleeps
    —————– —————– ———- ——–
    row cache objects kqrpre: find obj ########## ########
    row cache objects kqreqd: reget 9,674,182 ########
    row cache objects kqreqd 7,044,274 ########

    SQL> select count(*) OfUS from dba_rollback_segs where status = ‘OFFLINE’;

    OfUS
    —-
    1999

    The fix sets a better intial value of TUNED_UNDORETENTION by accessing
    historical data on wrh$_undostat after startup and makes more efficient
    the search for free space and making SMON try to shrink the undo segment
    before placing it offline avoiding the search for space on them.

    Workaround:
    “_smu_debug_mode”=33554432 see bug:5387030

  3. 你有没有 undo 分配extent的机制的文章?谢谢了。

Trackbacks

  1. […] Min/Max TR (mins)   最小和最大的tuned  undo retention ,单位为分钟; tuned undo retention 是自动undo调优特性,见undo自动调优介绍。 […]

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号