ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于oracle的undo_retention

关于oracle的undo_retention

原创 Linux操作系统 作者:aaqwsh 时间:2012-10-30 20:21:13 0 删除 编辑

最近升级了两个数据库,从10.2.0.5到11.2.0。遇到了一个比较囧的问题,undo_retention的设置在两个版本都一样,表空间大小差不多,10.2.0.5里能闪回的时间可以超过1天,使用率比较高。
但是11.2.0.3的使用率很少,闪回的时间控制在大概3小时。

SQL> show parameter undo

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


11.2.0.3 undo 使用情况:
Run Time
-----------------
25-Oct-2012 10:41


############## IN USE Undo Data ##############


PCT_INUSE
----------
7.77824668


TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN RETENTION
------------------------------ ---------- --------- ------ -----------
UNDOTBS1 LOCAL SYSTEM MANUAL NOGUARANTEE


Sum of Free
----------------
19,211,091,968


Total Bytes
----------------
23,294,115,840


############## UNDO SEGMENTS ##############


Status Total Extents
--------- -------------
EXPIRED 3,574
UNEXPIRED 1,590
ACTIVE 3
-------------
sum 5,167


Status Undo Segments
---------------- -------------
ONLINE 49
OFFLINE 736
-------------
sum 785


后来发现原因如下,只是没想到会是这样的原因:
在原来10.2.0.5下,undo的数据文件都是autoextend off的;
在新的11.2.0.3下,undo的数据文件有一个autoextend on的(后来增加了一个autoextend off的)。

参考:
10g NEW FEATURE on AUTOMATIC UNDO RETENTION [ID 240746.1]

Automatic tuning of undo retention in 10g.
============================================
Up to 9i we don’t have that much control over ORA-01555 errors.  But from 10g onward we have this
new feature, automatic tuning of undo_retention, which help control ORA-01555 situation better.

Mandatory setting:
=================
UNDO_MANAGEMENT=AUTO   /* Defaults to MANUAL in 10gR2*/


How it works (Step to Test):
=============================
If UNDO_MANAGEMENT=AUTO (no matter the setting for UNDO_RETENTION) automatic undo tuning will be used.
MMON will calculate the MAXQUERYLEN every 30 sec interval.  Based upon the MAXQUERYLEN, MMON decides the
TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

Note: For a fixed size undo tablespace, the database calculates the maximum undo retention period based
on database statistics and on the size of the undo tablespace. For optimal undo management, rather than
tuning based on 100% of the tablespace size, the database tunes the undo retention period based
on 85% of the tablespace size.

General calculation is
----------------------

TUNED_UNDORETENTION =  MAXQUERYLEN + 300 Sec.

If UNDO_RETENTION is set > 0, the value specified acts as a minimum setting.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-747951/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    261645