[20130904]等待事件wait for a undo record.txt

[20130904]等待事件wait for a undo record.txt

生產系統出現嚴重的等待事件，出現問題時我不在現場，這個事後的分析。

檢查慢的時段的awr報表，可以發現出現了wait for a undo record等待事件，這個問題以前沒有遇到。

Top 5 Timed Events                                         Avg %Total

~~~~~~~~~~~~~~~~~~                                        wait   Call

Event                                 Waits    Time (s)   (ms)   Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

db file sequential read           1,651,116      24,802     15   48.4   User I/O

CPU time                                         11,499          22.4           

wait for a undo record              191,327      11,153     58   21.7      Other

buffer busy waits                 2,637,172       6,101      2   11.9 Concurrenc

log buffer space                      3,931       3,551    903    6.9 Configurat

          -------------------------------------------------------------       

google發現相關連結：http://www.itpub.net/thread-1328358-1-1.html

SQL> select * from V$FAST_START_TRANSACTIONS;

 USN  SLT        SEQ STATE      UNDOBLOCKSDONE UNDOBLOCKSTOTAL     PID    CPUTIME  PARENTUSN  PARENTSLT  PARENTSEQ XID              PXID  RCVSERVERS

---- ---- ---------- ---------- -------------- --------------- ------- ---------- ---------- ---------- ---------- ---------------- ----- ----------

  35    6      87390 RECOVERED           29379           29379                184                                  230006005E550100               48

  92   39       6748 RECOVERED           14749           14749                241                                  5C0027005C1A0000                1

  49   38      70834 RECOVERED           36979           36979                173                                  31002600B2140100               48

  20   43     141597 RECOVERED            3444            3444                 45                                  14002B001D290200               48

select * from v$event_name where name like 'wait for a undo record';

    EVENT#   EVENT_ID NAME                   PARAMETER1  PARAMETER2  PARAMETER3  WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

---------- ---------- ---------------------- ----------- ----------- ----------- ------------- ----------- ----------

       587 3020078888 wait for a undo record                                        1893977003           0 Other

--沒有引數的說明。

從XID='230006005E550100'來找線索。

SQL> select * from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('230006005E550100');

XID               START_SCN START_TIMESTAMP     COMMIT_SCN COMMIT_TIMESTAMP    LOGON_USER  UNDO_CHANGE# OPERATION  TABLE_NAME TABLE_OWNER  ROW_ID   UNDO_SQL

---------------- ---------- ------------------- ---------- ------------------- ----------- ------------ ---------- ---------- ------------ ------  ---------

230006005E550100 9932260717 2013-09-03 16:54:08 9933165192 2013-09-03 17:08:58 XXXXXXXXXX              1 BEGIN

--說明：這個檢視檢視有一定時間，也許時間久了在這裡看不到，也許使用IMU的原因，看不到UNDO_SQL語句。時間2013-09-03 16:54:08到2013-09-03 17:08:58。

SQL> select * from V$ACTIVE_SESSION_HISTORY where xid=hextoraw('230006005E550100');

--輸出太多，忽略。從裡面可以知道sql_id.
--說明：這個檢視檢視有一定時間，也許時間久了在這裡看不到.可以使用DBA_HIST_ACTIVE_SESS_HISTORY代替。

SQL> select * from DBA_HIST_ACTIVE_SESS_HISTORY where xid=hextoraw('230006005E550100')

SQL> select distinct sql_id from V$ACTIVE_SESSION_HISTORY where xid=hextoraw('230006005E550100');

SQL_ID

-------------

ftm564rm196my

SQL> select * from v$sql where sql_id='ftm564rm196my';

no rows selected

--已經不在shared pool。

SQL> select * from DBA_HIST_SQLTEXT where sql_id='ftm564rm196my';

      DBID SQL_ID        SQL_TEXT                                                                         COMMAND_TYPE

---------- ------------- -------------------------------------------------------------------------------- ------------

 168324986 ftm564rm196my update ZY_FYMX SET YJRQ =:1 WHERE ZY_FYMX.JFRQ < :2 AND ZY_FYMX.YJRQ IS NULL                6

SQL> select * from DBA_HIST_SQLBIND where sql_id='ftm564rm196my';
no rows selected
--無法獲得繫結變數的值。

SQL> select * from DBA_HIST_SQL_PLAN where sql_id='ftm564rm196my';
no rows selected
--執行計劃也無法獲得？why？查詢awr報表也沒有記錄。有點奇怪!

直接看sql語句。
update ZY_FYMX SET YJRQ =:1 WHERE ZY_FYMX.JFRQ < :2 AND ZY_FYMX.YJRQ IS NULL;

-- 表ZY_FYMX的大小2.3G。
-- 猜測一下 YJRQ 表示月結日期 JFRQ表示什麼日期呢？直接輸入漢語拼音開頭jf，好像表示計費，問一下開發確實這樣。
-- [ 補充：我個人非常討厭這種欄位的命名方式，更加要命的是一些開發根本沒有文件說明,或者根本不全]。
-- 這樣就很明顯，正好是月頭，財務做扎帳處理，表僅僅在JFRQ欄位上有索引。ZY_FYMX.JFRQ < :2的區間範圍一定很大應該不會使用這個索引。
-- 而查詢ZY_FYMX.YJRQ 的條件是ZY_FYMX.YJRQ IS NULL，很明顯即使存在yjrq的索引也沒有用。因為查詢條件是NULL。
-- 正常如果每個月都做月結，YJRQ is NULL的資訊應該會很少,建立一個函式索引也許能解決這個問題。

-- 執行計劃應該是全部掃描,操作人員一定覺得慢，沒有等待結束就退出，導致大量事務rollback，出現wait for a undo record的情況。

SELECT distinct xid,session_id,sql_id

  FROM DBA_HIST_ACTIVE_SESS_HISTORY

 WHERE xid IN

          (HEXTORAW ('14002B001D290200'),

           HEXTORAW ('31002600B2140100'),

           HEXTORAW ('5C0027005C1A0000'),

           HEXTORAW ('230006005E550100'))

order by xid;

XID              SESSION_ID SQL_ID

---------------- ---------- -------------

14002B001D290200       2274 9a4dwk6bdus8m

14002B001D290200       2274 b5nzc0t42sjut

14002B001D290200       2274 ftm564rm196my

230006005E550100        466 ftm564rm196my

31002600B2140100       2393 b5nzc0t42sjut

31002600B2140100       2393 ftm564rm196my

5C0027005C1A0000       1052 ftm564rm196my

5C0027005C1A0000       1052

8 rows selected.

--xid相同涉及到sql_id還有9a4dwk6bdus8m，b5nzc0t42sjut，很奇怪這兩個sql_id事後無法查詢到對應的sql語句。不過基本可以確定問題。