透過shell指令碼來檢視Undo中資源消耗高的sql

在檢視undo的使用率的時候,在Undo_management為auto的時候，經常會看到undo自己在不斷的伸縮擴充套件，自我調節。
有時候看到Undo收縮的很緊,就想知道哪些sql語句在執行，可能有哪些潛在的問題。對於線上業務系統而言，如果某一條sql語句執行時間較長,而且消耗的undo資源極高的情況下,sql語句很可能是有問題的。
可以透過如下的sql語句來簡單定位，找到一個sql_id列表，可以看到每個sql_id消耗的Undo資源情況。


sqlplus -s <<EOF
set pages 53
select sum(undoblks)*8/1024 total_size_MB from v\$undostat  ;
select *from (
  select maxqueryid,
round(sum(undoblks )*8/1024) consumed_size_MB
from v\$undostat    group by maxqueryid order by  consumed_size_MB desc
) where rownum<50;
EOF
Exit

指令碼執行結果如下：

TOTAL_SIZE_MB
-------------
   70299.2188

MAXQUERYID    CONSUMED_SIZE_MB
------------- ----------------
7wx3cgjqsmnn4            39990
210ndtcx5fwgs            20738
648600hq1s1s8             5795
cjqdgd14xjwjm             1116
4ad8ypr3nf6vm              869
0my2xfpqrk6gw              597
f3pq3mdycwcd2              455
cwp9zk1y7cthy              312
ddtx15a9nzmjt              139
csrj5pnpx4wtr               72
6tshctswzutbk               49
3a4vsqkf8yaxs               49
gpzkq2kv9vhan               27
fa311gg43yjyf               21
cysbbg2h86xc6               19
fjzknc02f7019               18
aty7a3bvqfxxx               17
ftmvqxfzq1fv0               16

可以看到sql_id為7wx3cgjqsmnn4 的sql 消耗資源情況最嚴重,很有可能存在一定的效能問題。在檢視執行計劃後發現,確實如此。
具體的細則就不羅列了,此處略去幾百字。

總之透過undo的使用情況來檢視可能存在的效能sql也是一種方式。當然了undo的使用情況是頻繁變更的，可以根據自己的情況來對undo進行一定範圍內的監控，相信會有一定的收穫。