undo表空间的利用率达到100%分析

最新推荐文章于 2024-01-19 16:11:42 发布

原创最新推荐文章于 2024-01-19 16:11:42 发布 · 2.1k 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

150 篇文章

订阅专栏

本文深入探讨了Oracle数据库管理中的undo变空间的作用、参数设置、表空间使用情况及性能优化策略，包括UNDO_RETENTION参数、GUARANTEE功能、undo空间使用率评估和警报日志检查。详细解析了undo段的分配与回收机制，提供了关键信息以帮助理解与维护数据库性能。

undo变空间的作用：
a.当使用ROLLBACK语句时回滚事务，撤销DML操作改变的数据
b.恢复数据库
c.提供读取的一致性
d.使用Oracle Flashback Query分析基于先前时间点的数据
e.使用Oracle Flashback特性从逻辑故障中恢复数据库

1.查看undo表空间参数设置
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

UNDO_RETENTION该初始化参数用于控制UNDO数据的最大保留时间,其默认值为900秒。
对于一个固定大小的undo表空间来说回滚段数据库UNDO_RETENTION设置的值，会尽可能保留这么长时间,如果变空间不足就会从新利用unexpired undo对于可以扩展的(AUTOEXTEND )undo表空间并GUARANTEE开启，undo段要保留UNDO_RETENTION的时间以后才可以被重用

2.查看GUARANTEE功能是否开启
SQL> select TABLESPACE_NAME,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces where CONTENTS='UNDO';
TABLESPACE_NAME                STATUS    CONTENTS SEGMEN RETENTION
------------------------------ --------- --------- ------ -----------
UNDOTBS1                       ONLINE    UNDO      MANUAL NOGUARANTEE
UNDOTBS2                       ONLINE    UNDO      MANUAL NOGUARANTEE

3.查看undo空间使用情况。expired为可重用undo空间（如果undo表空间100%，但是expired的空间还有，就不要紧）
SQL> select count(status) from dba_undo_extents where status = 'UNEXPIRED';
COUNT(STATUS)
-------------
463
SQL> select count(status) from dba_undo_extents where status = 'EXPIRED';
COUNT(STATUS)
-------------
20
SQL> select count(status) from dba_undo_extents where status = 'ACTIVE';
COUNT(STATUS)
-------------
21

综合上述undo表空间使用率很高时，但并不一定会影响到业务的select和insert、delete、update操作，因为rollback segment中的空间是可以重用的

3.最好到数据库alert日志检查有没有报ORA-30036 unable to extend segment by %s in undo tablespace '%s'和ora-01555的错误，如果有错误，真正意味undo表空间不足了需要扩大了

一下是oracle官方关于undo段的解释
1. If the current extent has more free blocks then the next free block is allocated.
2. Otherwise, if the next extent expired then wrap in the next extent and return the first block.
3. If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return

the first block in the new extent.
4. If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment

and add it to the undo segment. Return the first free block of the extent.
5. If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment.

Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.
7. Tune down retention in decrements of 10% and steal extents that are now unexpired with respect to the lower retention value.
8. Steal unexpired extents from any offline undo segments.
9. Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10.

Otherwise, wrap into the next extent.
10. Try to steal unexpired extents from any online undo segment.
11. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'