ORA-1628：max # extents 32765 reached for rollback segment _SYSSMU7724

本文主要是介绍ORA-1628：max # extents 32765 reached for rollback segment _SYSSMU7724_3385743266$，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

问题简述	ORA-1628：max # extents 32765 reached for rollback segment _SYSSMU7724_3385743266$
处理人员	xxx	系统名称	xxx报表
系统版本		处理地址	xxx
数据库版本	11.2.0.4	数据库模模式	RAC
数据库patch	11.2.0.4.160719,
发生时间	Tue Dec 26 05:33:27 2017
问题描述
12月26日Alert日志报错ORA-1628：max # extents 32765 reached for rollback segment _SYSSMU7724_3385743266$
报错信息
Tue Dec 26 05:33:27 2017 ORA-1628: max # extents 32765 reached for rollback segment _SYSSMU7724_3385743266$

问题分析

SQL> SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME; STATUS TABLESPACE_NAME SUM(BYTES) COUNT(*) 
--------- ------------------------------ ---------- ---------- 
ACTIVE UNDOTBS2 78643200 12 
EXPIRED UNDOTBS1 2.6120E+11 109955 
EXPIRED UNDOTBS2 1.8133E+11 137387 
EXPIRED UNDOTBS3 714670080 9600 
UNEXPIRED UNDOTBS1 1.5412E+11 12373 
UNEXPIRED UNDOTBS2 8.7668E+11 638209 
UNEXPIRED UNDOTBS3 662700032 9077

根据目前的记录来看，回滚段使用的不多，而且expired 空间也不少，虽然unexpired undo 空间也很多，这个和undo retention 的保留的时间有关。目前undo表空间是1.4T，已经足够大。

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat; 
MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION) 
---------------- ------------------------ 
0 0
show parameter undo 
_gc_undo_affinity false 
_highthreshold_undoretention 70000 
_undo_autotune false 
undo_managenment auto 
undo_retention 10800 
undo_tablespace undotbs2
根据tune_undoretention 值为0，参数_undo_autotune 为false，可以判断数据库把自动调整undo给关了。

查看报错时间段的AWR：
Snap Id Snap Time Sessions Cursors/Session Instances 
Begin Snap: 6981 26-12? -17 05:00:11 135 1.6 4 
End Snap: 6983 26-12? -17 06:00:21 122 1.7 4 
Elapsed: 60.16 (mins) 
DB Time: 307.56 (mins) SQL ordered by Elapsed Time Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text 
3,605.60 0 19.54 99.99 0 8pp9tn0p8qurq JDBC Thin Client UPDATE TBL_CS_OVER_SUM A --±??... ---> 在5点到6点这个小时 update 语句运行1个小时没有完成。 
3,605.60 0 19.54 99.56 0.37 94q8yd5frsck6 ociuldr@sjcqpt3 (TNS V1-V3) SELECT INCT_01.POST_DATE AS JY...SQL Id: 8pp9tn0p8qurq 
SQL Text: UPDATE TBL_CS_OVER_SUM A SET PURE_GROW_BAD3_AMT = (SELECT SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbBR_CD = B.jbBR_CD AND A.MT_FAC_CD = B.MT_FAC_CD AND EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.LAST_RATING IN ('A', 'B')AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ) AND B.LAST_RATING IN ('C', 'D', 'E') AND B.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ),PURE_BACK_BAD3_AMT = (SELECT SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.LAST_RATING IN ('C', 'D', 'E') AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ) AND B.LAST_RATING IN ('A', 'B') AND B.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ), (PURE_GROW_UNPD_PRIN, PURE_GROW_UNPD_AMT) = (SELECT SUM(B.UNPD_PRIN_BAL), SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND NOT EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.UNPD_PRIN_BAL > 0 AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ) AND B.UNPD_PRIN_BAL > 0 AND B.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ), PART_GROW_UNPD_PRIN = (SELECT SUM(B.UNPD_PRIN_BAL - X.UNPD_PRIN_BAL) FROM TBL_CS_OVER_DETAIL B, TBL_CS_OVER_DETAIL X WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND B.ACCT_NO = X.ACCT_NO AND B.UNPD_PRIN_BAL > X.UNPD_PRIN_BAL AND X.UNPD_PRIN_BAL > 0 AND X.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 AND B.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ), (PURE_BACK_UNPD_PRIN, PURE_BACK_UNPD_AMT) = (SELECT SUM(B.UNPD_PRIN_BAL), SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND NOT EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.UNPD_PRIN_BAL > 0 AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ) AND B.UNPD_PRIN_BAL > 0 AND B.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ), PART_BACK_UNPD_PRIN = (SELECT SUM(B.UNPD_PRIN_BAL - X.UNPD_PRIN_BAL) FROM TBL_CS_OVER_DETAIL B, TBL_CS_OVER_DETAIL X WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND B.ACCT_NO = X.ACCT_NO AND B.UNPD_PRIN_BAL > X.UNPD_PRIN_BAL AND X.UNPD_PRIN_BAL > 0 AND X.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') AND B.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ),  PURE_GROW_QX_AMT = (SELECT SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND NOT EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.DEBT_FLAG = 'Y' AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ) AND B.DEBT_FLAG = 'Y' AND B.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ), PURE_BACK_QX_AMT = (SELECT SUM(B.OUTSTD_AMT) FROM TBL_CS_OVER_DETAIL B WHERE A.jbbr_cd = B.jbbr_cd AND A.MT_FAC_CD = B.MT_FAC_CD AND NOT EXISTS(SELECT 1 FROM TBL_CS_OVER_DETAIL X WHERE X.DEBT_FLAG = 'Y' AND B.ACCT_NO = X.ACCT_NO AND X.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD') ) AND B.DEBT_FLAG = 'Y' AND B.DT_COMMIT = TRUNC(B.DT_COMMIT, 'MM') - 1 ) WHERE A.DT_COMMIT = TO_DATE('2017-12-25', 'YYYY-MM-DD')

从AWR 看，在5点到6点这个小时有个update 语句运行了1个小时没有完成。这个可能需要大量undo 表空间。请分析一下这个大事务是否可以优化调整，加入commit。另外，目前系统undo autotune是关闭的，这样无论什么时候， undo 段都需要保留3个小时（10800s），当有偶然的大事务时，expired 空间无法满足需要，回滚段无法灵活适应。如果启用autotune，那么，对于平时很快完成的事务，它也会缩短undo保留时间，这样对一些偶然的大事务就会有更多的可用空间。所以建议可以启用undo autotune，把_highthreshold_undoretention 调整到目前undo retention的水平或文档给出的建议max(maxquerylen)+1 。对于参数_rollback_segment_count设置成12000，这个值看起来设置的有点大，一般它是按照undo max(maxconcurrency)来设置的，
目前max(maxconcurrency)值为716，建议将_rollback_segment_count设置为1000即可，没必要设置为12000，设置太大反而会占用空间。

解决方案
1、优化该update语句
2、开启undo autotune

这篇关于ORA-1628：max # extents 32765 reached for rollback segment _SYSSMU7724_3385743266$的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！