老生常谈：关于undo表空间的使用率

就在前几天，又有一个客户向我咨询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');

最后，实际我们透过这个简单的案例来看，实际很多项目上，也的确真实存在一些运维人员，他们并不具备相应的知识储备，就直接去做相应工作了，其结果就是让本不复杂的系统布满了各种各样的坑。
所以，无论是学什么做什么，对于基础知识还是要深入的去学习和思考的，不积跬步无以至千里。