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

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');