undo tablespace 为什么一直增大

yobyin

三节点rac 环境

两个节点的rac undo 达到了170G ,一个节点80 G ,并持续增大。


SELECT DISTINCT STATUS "状态",
                COUNT(*) "EXTENT数量",
                SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
  FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;

         状态
EXTENT数量
UNDO大小
1
UNEXPIRED
8357
265.000061035156
2
EXPIRED
3736
83.5272827148438
3
ACTIVE
169
4.7069091796875


undo retetion 900s

运行的业务里有一些执行进间长达10000多s 以上的sql



我的疑问 为undo 持续变大， 这么大了，还会报ora-01555 错误。 已经过期的undo 空间不会释放出来用吗，
有人提到了undo 的贪婪算法，这个算法是个什么样的，在网上没找到。
如何收缩这些undo

我在网路及他人的帮助下找了些原因,  没有限制undo file 的大小，让其自动扩展了. 现在发出这个贴子来请大家瞧一下。undo block 的使用情况在附件。谢谢.

[ 本帖最后由 yobyin 于 2012-6-19 09:41 编辑 ]

yobyin

目前 关了undo datafile autoextend . 建议研发优化， 还请大神说说undo  贪婪算法。了解一下其算法

Maclean Liu(刘相兵

undo tablespace 的datafile 均是Auto Extended

SQL> select distinct status st, count(*) "HOW MANY", sum(bytes) "SIZE"
  2  from dba_undo_extents
  3  group by status
  4  /

                                                                Undo Extents Statistics

Status        How Many?              Size
---------  ------------  ----------------
UNEXPIRED         8,852   293,000,445,952
EXPIRED           3,647    90,080,804,864
ACTIVE               12       570,556,416


UNEXPIRED          undo extent 站了绝大多数 占了 272GB

ACTIVE 的仅占用 544MB

建议你取消 undo datafile的autoextend ， 考虑重建一个undo tablespace


SQL> col i format 999,999 head "1555 Errors"
SQL> col j format 999,999 head "Undo Space Errors"
SQL>
SQL> select sum(ssolderrcnt) i from v$undostat
  2  where end_time > sysdate-2
  3  /

1555 Errors
-----------
         18

发生 1555 不仅局限于undo 表空间不够大， 也可能由于 查询时间过久



从v$undostat 可以发现 你有几个SQL的执行时间一直很长

1.                       Query
   
2.                     Maximum                                Undo         # of                             Tuned Retention
   
3. Date/Time           Minutes SqlID              TBS       Blocks        Trans # of Unexpired # of Expired       (Minutes)
   
4. ------------------ -------- ------------- -------- ------------ ------------ -------------- ------------ ---------------
   
5. 17-Jun-2012 08:30        88 6uvg6bv1g0v28        2    1,388,711      624,084     20,962,608    1,478,984              93
   
6. 17-Jun-2012 08:31     6,744 94s1h2c01j3f7        4    1,537,437      655,616     20,449,480    1,311,368             167
   
7. 17-Jun-2012 08:40        98 6uvg6bv1g0v28        2    1,481,415      630,270     21,116,496    1,388,592              95
   
8. 17-Jun-2012 08:41     6,754 94s1h2c01j3f7        4    1,772,993      754,482     21,089,264      604,824             164

复制代码

94s1h2c01j3f7  长达 9,585  分钟  这个SQL 需要调优  运行 9,585 分钟的查询没有意义。

yobyin

发了 undo_diag,请 帮分析，谢谢

Maclean Liu(刘相兵

请运行 http://www.oracledatabase12g.com ... nostic-scripts.html 中提供的脚本 ，并上传运行结果