undo表空间的使用率深入理解

共计 7305 个字符，预计需要花费 19 分钟才能阅读完成。

就在前几天，又有一个客户向我咨询 undo 表空间使用率的问题。这让我想起几年前曾经有个省份的案例，客户的实际运维人员是一位刚毕业不久的女孩，几乎不懂 Oracle 原理，项目经理交给她的任务也是基础运维工作，比如其中一项就是监测数据库各个表空间的使用率，并对使用率超过 95% 的表空间进行扩展，他们的 Oracle 版本是 10gR2。
由于该客户业务是运营商话单相关的，业务数据量很大（几十 T 的规模），所以预留存储的空间也很充足。
有一次该客户有其他问题找到我远程处理的时候，我惊奇的发现他们的 undo 表空间居然有 2 个多 T 大小。进而询问运维人员是怎么回事，想必结果大家已经猜到了，这女孩说她日常巡检经常发现 undo 表空间使用率超过 95%，所以她就不停地扩展，直到如今已经加到 2 个多 T 规模的大小。她甚至认为 undo 表空间也是某一个业务的表空间，这就尴尬了。
那么，究竟什么是 undo？undo 都有哪些实际作用呢？Oracle 10g 的官方文档是这样描述的：

What Is Undo?

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued

Recover the database

Provide read consistency

Analyze data as of an earlier point in time by using Oracle Flashback Query

Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

具体来看下我 10.2.0.5 实验环境下 undo 相关参数的默认设置：

SQL> show parameter undo

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

可以看到 undo_management 默认设置为 AUTO，关于这个值，官档这样描述：

Automatic undo management uses an undo tablespace.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database.

而对于 undo_retention 这个值，默认是 900，单位是 s，也就是 15 分钟。很多实际的环境，也会考虑将其设置的大一些，比如 10800, 即 3 小时。
来看下官档对于 undo retention 和与之相关的 retention guarantee 的具体描述：

Undo Retention

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention:

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low.

For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

WARNING:
Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

看到这里，我们已经可以明白，对于本文开头我说到的那个案例，为什么 undo 明明是可以循环利用的，却不断增长最终使得那个女孩不断扩展 undo 表空间。
之前看到 Maclean 在群里答复一个网友的相关提问，给出了如下语句来查询 undo 真实的使用率：

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

select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )) "PCT_INUSE" 
from dual;

可以看到，这个语句实际上就是将状态为 ACTIVE 和 UNEXPIRED 的，计算为已使用。如果 retention guarantee 并没有设置的话，那么这个使用率高也不一定会有问题，因为 Oracle 会将 unexpired 状态的也拿来重用。
另外需要注意，如果是 RAC，上述的查询会将两个实例的结果平均，而实际上我们希望是各自统计各自的。所以可以直接指定我们要查询的 undo 表空间名称：

select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name = '&TABLESPACE_NAME'
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name = '&TABLESPACE_NAME') "PCT_INUSE" 
from dual;

也可以通过 dba_undo_extents 监控 undo 表空间的使用情况，按状态分组：

select tablespace_name, status, sum(bytes/1024/1024) "MB"
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;

根据上面的知识，我们只需关注结果中状态为 ACTIVE 的占用多少，如果设置了 retention guarantee，那么还要同时关注 UNEXPIRED 的占用多少。

此外，从 Maclean 的博客中找到两条实用的 UNDO 表空间监控的查询 SQL：

-- 在 Oracle 10g 版本中可以使用 V$UNDOSTAT 视图用于监控实例中当前事务使用 UNDO 表空间的情况。视图中的每行列出了每隔十分钟从实例中收集到的统计信息。
-- 每行都表示了在过去 7 *24 小时里每隔十分钟 UNDO 表空间的使用情况，事务量和查询长度等信息的统计快照。
--UNDO 表空间的使用情况会因事务量变化而变化，一般我们在计算时同时参考 UNDO 表空间的平均使用情况和峰值使用情况 

-- 以下 SQL 语句用于计算过去 7 *24 小时中 UNDO 表空间的平均使用量 
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs from v$parameter where name = 'db_block_size');
       
-- 以下 SQL 语句则按峰值情况计算 UNDO 表空间所需空间：
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (undoblks / ((end_time - begin_time) * 86400)) ups
          from v$undostat
         where undoblks in (select max(undoblks) from v$undostat)),
       (select value as dbs from v$parameter where name = 'db_block_size');