Oracle 参数之_undo_autotune

Oracle 10g及后续版本较以前版本有一新特性即自动调整undo retention时间，大大简便了管理，对于自动扩展（autoextend on）的undo表空间，参数undo_retention设置成为Oracle自动调节undo retention的最低阀值。对于非自动扩展（autoextend off），非guarantee 的undo 表空间，Oracle会根据undo表空间大小和v$undostat的历史信息（是否统计undo信息是由隐含参数_collect_undo_stats决定的，默认情况为TRUE）最大可能性保留undo信息。
查看undo数据文件是否自动扩展，undo表空间是否处于gurantee状态。

引用

SQL> select AUTOEXTENSIBLE,RETENTION
  2  from dba_tablespaces,dba_data_files
  3  where dba_data_files.TABLESPACE_NAME=dba_tablespaces.TABLESPACE_NAME
  4  and dba_data_files.TABLESPACE_NAME='UNDOTBS1'
  5  ;

AUT RETENTION
--- -----------
YES NOGUARANTEE

当然这一特性是由隐含参数_undo_autotune控制的，默认情况下设置为TRUE，部分特殊情况下会将其设为FALSE，如startup upgrade。

引用

SQL> set linesize 120
SQL> col name for a40 trunc
SQL> col value for a20
SQL> col pdesc for a50 trunc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: undo_autotune
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%undo_autotune%'

NAME                                     VALUE                PDESC
---------------------------------------- -------------------- --------------------------------------------------
_undo_autotune                           TRUE                 enable auto tuning of undo_retention

以下为startup upgrade alert启动日志：

引用

ALTER SYSTEM enable restricted session;
Thu Jan  7 10:17:06 2010
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
Thu Jan  7 10:17:06 2010
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Thu Jan  7 10:17:06 2010
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
Thu Jan  7 10:17:06 2010
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
Thu Jan  7 10:17:06 2010
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

以下为最小化参数启动Oracle日志：

引用

Sat Jan  9 13:58:08 2010
Adjusting the default value of parameter parallel_max_servers
from 40 to 25 due to the value of parameter processes (40)
Sat Jan  9 13:58:08 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Sat Jan  9 13:58:19 2010
Adjusting the default value of parameter parallel_max_servers
from 40 to 25 due to the value of parameter processes (40)
Sat Jan  9 13:58:19 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Sat Jan  9 13:59:52 2010
Adjusting the default value of parameter parallel_max_servers
from 40 to 25 due to the value of parameter processes (40)
Sat Jan  9 13:59:52 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Sat Jan  9 14:01:25 2010
Adjusting the default value of parameter parallel_max_servers
from 40 to 25 due to the value of parameter processes (40)
Sat Jan  9 14:01:25 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /ora10g/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
  db_name                  = zhoul
PMON started with pid=2, OS id=32133
PSP0 started with pid=3, OS id=32135
MMAN started with pid=4, OS id=32137
DBW0 started with pid=5, OS id=32139
LGWR started with pid=6, OS id=32141
CKPT started with pid=7, OS id=32143
SMON started with pid=8, OS id=32145
RECO started with pid=9, OS id=32147
MMON started with pid=10, OS id=32149
MMNL started with pid=11, OS id=32151