線上重定義造成某核心OLAP系統資料庫無規律間歇性重啟

愜意的週末

今天週六，睡到自然醒，西安難得的古都藍，溫暖的陽光，一個愜意的週末，star童鞋正在焦急的等待最喜歡的西安美食之一”肉丸胡辣湯”。。。。突然電話響了，某核心BOSS系統資料庫異常，需要緊急支援，star童鞋火速趕往現場ing……….

詭異的回憶

與現場工程師溝通了解現場情況，

場景一：昨天週五15：50，業務監控系統報警，某核心BOSS系統短暫異常，現場工程師檢視時系統各項指標均正常(包括主機CPU、記憶體、資料庫叢集情況、日誌等)。

場景二：昨天週五22:45,業務監控系統報警，某核心BOSS系統短暫異常，敬業高尚的現場工程師於23：50分叫車趕到現場，經確認一切，系統各項指標均正常(包括主機CPU、記憶體、資料庫叢集情況、日誌等)。–涇渭湖邊的水，平靜的像一面鏡子，沒有一絲波瀾。

場景三：今天週六 6:20，業務監控系統再次報警，某核心BOSS系統短暫異常

是監控系統調皮(誤報),還是資料庫任性(異常)，這是個問題，大家都很迷茫？

清澈的分析

在這個萬分緊急的關頭，一道白光閃過，star童鞋登場了，大家都看到了希望，下來上乾貨。。。。

檢視所有異常時間點

資料庫歷史會話

select * from dba_hist_active_sess_history y where y.sample_time >= trunc(sysdate)+13/24

and y.sample_time <= trunc(sysdate)+16/24;

發現15：50資料庫存在大量活動重建索引程式

sql_1:

begin

  dbms_redefinition.start_redef_table('ht', 'my_home', 'family');

end;

注：該語句為線上重定義指令碼。

sql_2:

透過檢視歷史會話表中，MACHINE、PROGRAM欄位，鎖定異常起因。

MACHINE        PROGRAM

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

B工程師的電腦   plsqldev.exe

經與BI工程師溝通其在昨天15點左右有做過線上重定義操作，昨天22點及今天早上6點沒有任何操作。

繼續檢視SQL_3:

注：批次重建索引語句，由於top_level_sql_id相同，所以為sql_1產生的子程式。

WORD天這條重建索引的語句在幹嘛！！！！

該語句有以下兩個隱患：

• 正常重建索引語句：alter index “ht”.”PK _ht _LOG”rebuild parallel N(N為需要並行執行的程式數)，該語句未設定並行度N，此時的並行度是：伺服器CPU數*每個CPU啟用的執行緒數,該伺服器210個CPU執行緒(2結點RAC，210*2個執行緒)因此以上條語句同時啟動420個程式,造成伺服器CPU使用率100%，資料庫服務異常。

• 該索引重建完成後，索引並行度為”default”

週五 15點的問題已經分析清楚了，但週五22點和週六6點又是什麼原因呢？star童鞋繼續分析ing…..

 

檢視週五22點和週六6點資料庫AWR報告

由上看出，引起並行查詢等待的sql為

select /*+ parallel_index(t, "PK_ht_LOG", 999) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad no_expand index_ffs(t, "PK_ht_LOG") */ count(*) as nrw, count(distinct sys_op_lbid(1262391, 'L', t.rowid)) as nlb, null as ndk, sys_op_countchg(substrb(t.rowid, 1, 15), 1) as clf from "ht"." ht_LOG " sample block ( .6116619100, 1) t where "READ_ID" is not null。

該sql使用hint強制並行操作,WORD天999。

 

由上可以看出DBMS_SCHECULER中的job(ORA$AT_OS_OPT_SY_2476)佔據了97.25%的服務。而該scheculer呼叫的就是上面的並行查詢語句。

原因已經很明顯。資料庫統計分析任務，週五晚上10點、週六早上6點，該排程程式呼叫的sql使用並行查詢(索引預設並行度999)，耗盡了cpu,導致資料庫異常。

問題回溯：

場景1： 15：30分業務人員執行線上重定義，ORACLE線上重定義(業務表1億資料量),重建索引alter index index_name rebuildparallel使用系統預設並行度，並行度較高，造 成資料庫異常，此時該索引degree為default。

場景2：週五晚上10點資料庫定時統計任務啟動，使用索引degree(default)資料庫服務異常。

場景3：週六早上6點資料庫定時統計任務啟動，使用索引degree(default)資料庫服務異常。

場景4：star童鞋經過深入分析後，執行一條命令問題解決。

簡潔的方案

 Alter index index_name noparallel;

–你要的乾貨。

忠誠的建議

• 排查表和索引degree並行度，將並行度不為1的改為並行度為1(noparallel)。

• 將度行度列入自動化監控列表，及時發現問題。

• 修改引數parallel_max_servers在可控範圍。

愜意的週末已過去大半，我最喜歡的古都美食之一”肉丸胡辣湯”還熱氣騰騰的等待star同學歸來。。。。。。。