latch: row cache objects等待dc_rollback_segments的处理
原创
©著作权归作者所有：来自51CTO博客作者wb94a78wq170rt0的原创作品，请联系作者获取转载授权，否则将追究法律责任
上午有客户遇到UNDO空间问题导致的latch: row cache objects (dc_rollback_segments )，经排查为节点2的UNDO表空间使用率100%，大部分为UNEXPIRED状态的UNDO段。虽然UNDO表空间的数据文件支持自动扩展，还是出现严重的等待，DML操作均很缓慢，通过人工扩大UNDO表空间后恢复正常；整个排查思路总体为：
1.反映数据库慢时，对当前活动会话、等待事件的排查，找出锁或异常等待事件对应的进程，再进一步分析（此步骤可以同步收集AWR，或通过ASH来确定问题开始时间）。
2.根据等待事件latch: row cache objects (dc_rollback_segments )进一步分析UNDO的配置，如undo表空间的不同状态段的大小分布、undo_retention及其它UNDO_TUNE相关参数的检查。
3.确认UNDO问题后扩容，问题恢复。
4.之后分析UND中存在大量UNEXPIRED状态段的原因。如何分析？DML肯定产生REDO日志或BLOCK CHANGE，因此可以通过查看REDO日志产生量(归档日志)，BLOCK CHANGE来定位出某个时间点事务量大；再分析此时间段的AWR/ASH来排查大的事务操作(如查AWR的Segments by DB Blocks Changes中表)是人为或业务的等思路。
1.查询等待事件
SQL> select inst_id,event,count(1) from gv$session where wait_class#<> 6 group by inst_id,event order by 1,3;
INST_ID EVENT COUNT(1)
---------- -------------------------------------------------- ----------
1 enq: SV - contention 1
1 DFS lock handle 1
1 enq: TX - row lock contention 2
1 gc cr request 2
1 SQL*Net break/reset to client 3
1 SQL*Net message to client 6
2 SQL*Net message from dblink 1
2 DFS lock handle 1
2 CGS wait for IPC msg 1
2 enq: TX - row lock contention 1
2 enq: US - contention 1
2 SQL*Net break/reset to client 1
2 gc cr request 1
2 enq: SV - contention 1
2 latch free 2
2 SQL*Net message to client 2
2 latch: row cache objects 104
17 rows selected.
2.查询latch: row cache objects的P1值找出对应的LATCH NAME：dc_rollback_segments
SQL> select count(*),P1RAW from gv$session where event = 'latch: row cache objects' group by P1RAW;
COUNT(*) P1RAW
---------- ----------------
106 0000003A542DFA10
SQL> SELECT
2 kqrsttxt PARAMETER,
3 -- kqrstcid CACHE#,
4 kqrstcln "Lchild#",
5 kqrstgrq "DCGets",
6 l.gets "LGets",
7 l.misses "Misses"
8 FROM X$KQRST, V$LATCH_CHILDREN l
9 WHERE l.addr='&P1RAW'
10 and l.child#=KQRSTCLN
11 ORDER BY 1,2
12 ;
Enter value for p1raw: 0000003A542DFA10
old 9: WHERE l.addr='&P1RAW'
new 9: WHERE l.addr='0000003A542DFA10'
PARAMETER Lchild# DCGets LGets Misses
-------------------------------- ---------- ---------- ---------- ----------
dc_rollback_segments 1 765040834 4272819637 2783160470
SQL> select * from V$LATCHHOLDER;
PID SID LADDR NAME GETS
---------- ---------- ---------------- ---------------------------------------------------------------- ----------
39 7333 0000003A542DFA10 row cache objects 4293097299
1974 10193 0000003B3E90ACC8 cache buffers chains 70589673
2186 4908 0000003B3DD03800 cache buffers chains 36715147
2318 14665 0000003ACB82E6C8 cache buffers chains 97856295
2567 1372 0000003A542DFA10 row cache objects 4293097807
3.查UNDO配置
SQL> SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_gc_undo_affinity boolean FALSE
_highthreshold_undoretention integer 259200
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 259200
undo_tablespace string UNDOTBS2
Tablespace_Name Size(GB) Status Used Extents US_SIZE(GB) Used R
--------------- --------- ---------- ------------ ----------- ------
UNDOTBS1 111.00 ACTIVE 211 .22 .19
UNDOTBS1 111.00 EXPIRED 50830 36.44 32.75
UNDOTBS1 111.00 UNEXPIRED 35975 31.1 27.96
UNDOTBS2 58.00 ACTIVE 239 .19 .32
UNDOTBS2 58.00 EXPIRED 12200 2 3.46
UNDOTBS2 58.00 UNEXPIRED 141196 52.18 90.11
4.查undo_retention 时间范围内具体哪个时间点的事务量大，从而找出对应的问题程序或SQL语句/表名等~~，排查思路见开头，SQL就省略了。
