在巡檢時發現大量UNDO相關的等待事件,enq: US - contention,latch: undo global data

latch: undo global data一般發生在UNDO表空間不足時,應急處理為增加UNDO表空間,或者把UNDO RETENTION調?。ㄈ鏤NEXPIRED EXTENTS較多時有效)。
接下來分析一下產生這個問題的原因,由于查看了當前沒有大事務,死事務排除活動事務占用的情況,這時推測為過去某個時間點大量UNDO占用,還未超過UNDO RETENTION,導致UNDO EXTENT不能重用??梢酝ㄟ^v$undostat分析這個情況。
select begin_time,(END_TIME-begin_time)360024 s,undoblks,txncount,MAXCONCURRENCY, EXPSTEALCNT,EXPBLKRELCNT,EXPBLKREUCNT,EXPIREDBLKS from v$undostat where begin_time>sysdate-1 order by begin_time;
可以看到在問題發生前確實有UNDOBLKS分配量,事務量突增的情況,這時可以看一下UNDO RETENTION,根據這個產生速率算一下是不是有大量UNDO BLOCKS還未過期。

接下來查找哪些事務導致的UNDO占用突增。一般可以通過下面兩種途徑分析
1、ASH查找大事務,按XID分組,如果單個XID活動時間過長,或過濾SQL_OPNAME IN(‘INSERT’,‘DELETE’,‘UPDATE’),按SQL分組,查找是不是有執行量突增的DML。
2、通過分析AWR中的SEG STATS,查詢BLOCK CHANGE突增的對象。

通過上面的分析也沒找到異常點,接下來就比較難找了,這里運氣比較好,通過對比SQL的執行量、執行情況,找到了如下PLSQL匿名塊在UNDO BLOCKS產生速率高的時間點執行,執行量也與UNDO BLOCKS的波動一致。以下為偽代碼。
declare
cnt number;
begin
for i in 1..100000 loop
select count(*) into cnt from test.tobj@test;<<<<
if cnt>10 then
insert test.t values (cnt);<<<<<后面統計這個INSERT不怎么執行,cnt>10這個條件不成立。
end if;
commit;
end loop;
end;
一個使用DBLINK的查詢執行量大,INSERT幾平不怎么執行。
這里的問題點
1、每次循環DBLINK查詢會生一個事務(DBLINK為分布式事務)。
2、每次循環都會提交。
根據之前看到的UNDOSTATS的統計,UNDO BLOCKS使用量,事務量的增長,推測為循環DBLINK查詢,每次產生新的分布式事務分配新ROLLBACK SEGMENT導致UNDO使用突增。以下在虛擬機模擬驗證這一猜測。
可以看到這個循環執行前UNDO FREE 270M,執行這個PL/SQL塊,很快就用完了.(里面的IF判斷不會成立,INSERT不會執行)

XIDUSN XIDSLOT XIDSQN 一直在變化,說明頻繁產生新事務
USED_UBLK 為1,每個事務使用1個UNDO BLOCK
SQL> @trans
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ------------------------------ ---------------- ---------------- ---------- ---------- --------- ----------------------------- ----------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
29 6967 SYS 0000000081B40EC8 0000000084693E40 1 1 421603 ACTIVE 20220211 22:55:55 273 7 682 11010700AA020000 0000000000000000 0000000000000000
中間可以觀察到這個session一直在分配新的rollback segment,每次使用一個UNDO BLOCK
SQL> @trans
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ------------------------------ ---------------- ---------------- ---------- ---------- --------- ----------------------------- ----------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
29 6967 SYS 0000000081B06B68 0000000084693E40 1 1 421603 ACTIVE 20220211 22:56:04 273 6 758 11010600F6020000 0000000000000000 0000000000000000
SQL> @trans
no rows selected
SQL> @trans
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ------------------------------ ---------------- ---------------- ---------- ---------- --------- ----------------------------- ----------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
29 6967 SYS 0000000081B9AC18 0000000084693E40 1 1 421603 ACTIVE 20220211 22:56:09 275 27 760 13011B00F8020000 0000000000000000 0000000000000000
還可以看到這個DBLINK查詢導致了REDO的增長。這也是除了延遲塊清除之外的另一個查詢產生REDO的例子。





