你的Oracle是不是這個時間發生的故障？

ITPUB社群發表於2023-03-06
Oracle

背景

在我的運維Oracle工作中，經常有客戶遇到這個問題，而且一直都有，也可以說基本在每個客戶那裡都遇到過。所以希望這篇文章可以幫助到一些朋友。

描述

如果你的系統發生問題了，請注意這兩個特殊時間：

  1. 週一至週五工作日每天晚上22:00之後持續4小時。
  2. 週六日從凌晨06:00一直持續20個小時。

有經驗的朋友看到這裡就知道我想說什麼了，是的我是想說Oracle auto task帶來的問題。雖然是老生常談，不過剛剛又遇到了這個問題，就寫出來給大家提個醒！

分析

Oracle auto task 包含三個作業：

  • 「auto optimizer stats collection：」(自動最佳化器統計資訊蒐集)

一條SQL要在資料庫中執行，必須先有執行計劃，執行計劃的準確與否取決於統計資訊的準確度。經常遇到的效能問題就是執行計劃不對。Oracle為了保證表及相關物件統計資訊的準確性，安排了這個作業，在固定的「視窗」定期執行。當一個表的資料變化量超過10%便會被記錄，在自動採集統計資訊時被「自動」收集。

會帶來的問題：新收集的統計資訊會導致執行計劃改變，經常遇到第二天剛上班，系統變得特別慢，分析後就是SQL執行計劃改變，最好的解決做法是繫結歷史執行計劃。

  • 「sql tuning advisor：」

Oracle每天會將佔資料庫資源較多的一些SQL放在一個最佳化集中，在固定的「視窗」中進行分析，「自動」對其生成最佳化指導，但是並不做任何實際的變更。不過生成的指導是可以被使用的，需要人工確認並實施。

會帶來的問題：因為是每天執行的作業，所以每天都可能再分析相同的SQL，導致資源浪費，更可能佔用大量的I/O資源，影響正常的業務SQL。

  • 「auto space advisor ：」

如果一個表被使用的久了就會有碎片，碎片就是一個表在物理磁碟的儲存中會有很多空洞、行連結等，碎片越多就會導致資料庫效能下降的越厲害，所以Oracle設定了再固定「視窗」內「自動」分析碎片的任務，讓我們可以快速的定位哪些碎片大的物件可以被回收。僅生成指導，不實際變更。

會帶來的問題：通常如果遇到被分析的物件特別大，分析過程中會透過建立臨時表的形式對比可節省空間，將會帶來TEMP表空間使用率100%報警，也會生成大量的REDO導致歸檔空間報警，也會帶來大量的I/O資源使用，影響正常業務。

任務關鍵字：dbms_space.auto_space_advisor_job_proc

「綜上所述」

  • auto optimizer stats collection：自動統計資訊蒐集作業，發生實際操作。
  • auto space advisor ：空間最佳化指導，只生成指導建議，不做實際操作。
  • sql tuning advisor：SQL最佳化指導，只生成指導建議，不做實際操作。

「有兩個關鍵資訊：」

1.視窗：

Oracle設定了兩個型別時間視窗執行auto task作業，這個視窗內會執行上面提到的三個作業。這兩類時間視窗為，週一至週五工作日每天晚上22:00~次日02:00 持續4小時，週六日從凌晨06:00~次日凌晨02:00一直持續20個小時。如果遇到情況複雜、體量較大的系統時，通常會遇到任務執行失敗的情況，這也是為什麼Oracle在週末設計20個小時的視窗的原因，希望用更長的視窗完成任務。但是一些週六、日有交易的系統，其實會受到很大的效能影響，甚至我遇到過在週六、週日20個小時的視窗內以每4小時的頻率重複執行以上作業的情況。

2.自動：

Oracle在很早就開始佈局自動化、從10g 、11g auto task，到18c auto index，再到19c Machine Learning，Oracle在自動化的道路上一直不懈努力。自動化的後面其實是資料庫在後臺自動的分析了很多資料，有時候接受自動化的代價可能需要接受風險。比如自動化的統計資訊收集，會遇到SQL執行計劃的突然改變，影響實時交易。自動化的SQL調優建議、段空間建議會帶來資料庫資源的爭用，帶來空間告警，甚至影響業務。有得有舍。

建議

  • 常見的建議是關閉auto space advisor 、sql tuning advisor。僅保留auto optimizer stats collection這樣帶來的風險最低。
  • 如果你的系統週六、日也有線上業務，比如工廠加工流水線、實時交易類系統，也建議將週六、週日的視窗修改時間短一些，畢竟20個小時的視窗期的隱患還是很大的。我比較喜歡將週六、週日的視窗修改為和工作日一樣的22點開始4個小時視窗。
  • 如果系統體量特別大，我也會額外注意超大表的統計資訊收集，有時需要遮蔽掉較大的表的作業，使用手動的方式處理。
  • 也有的客戶為追求絕對的穩定，會關閉auto space advisor 、sql tuning advisor、auto optimizer stats collection三個作業，這種情況下維護統計資訊作業變得比較麻煩，強力建議在系統上線之初進行一次統計資訊收集，後期在表有重大變化時進行統計資訊維護。

舉個例子

一個朋友找過來的問題，windows機器，資料庫異常關機，一般異常當機都會看看報錯程式碼，還沒來得及看程式碼，先看到了cdmp目錄生成時間，工作日22:00，立刻引起警覺，很可能是auto task導致的問題。你的Oracle是不是這個時間發生的故障？

然後查一查日誌驗證了我的猜想你的Oracle是不是這個時間發生的故障？SYS_AUTO_SQL_TUNING_TASK 帶來了記憶體溢位的問題，導致系統當機。破案以後我關閉了sql tuning advisor，順帶手最佳化了auto space advisor 。不過也發現這個系統使用了AMM管理，我給改成了ASMM管理，這是另外的一概念，有機會寫一篇文章解釋解釋。

附件

分享一些和auto task相關的常用SQL 自動收集作業狀態：







SQL> select client_name,status,WINDOW_GROUP from dba_autotask_client;CLIENT_NAME                                                      STATUS   WINDOW_GROUP---------------------------------------------------------------- -------- --------------------auto optimizer stats collection                          DISABLED ORA$AT_WGRP_OSauto space advisor                                              ENABLED  ORA$AT_WGRP_SAsql tuning advisor                                               ENABLED  ORA$AT_WGRP_SQ

關閉某個任務








BEGINdbms_auto_task_admin.disable(client_name => 'auto space advisor',operation   => NULL,window_name => NULL);END;/

檢視視窗資訊
















select t1.window_name, t1.repeat_interval, t1.durationfrom dba_scheduler_windows t1, dba_scheduler_wingroup_members t2where t1.window_name = t2.window_nameand t2.window_group_name in('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME          REPEAT_INTERVAL                                                                                      DURATION-------------------- ---------------------------------------------------------------------------------------------------- -------------------------MONDAY_WINDOW            freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00TUESDAY_WINDOW            freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00THURSDAY_WINDOW        freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00FRIDAY_WINDOW              freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00SATURDAY_WINDOW       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00SUNDAY_WINDOW           freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00

修改視窗屬性：

















修改持續時間BEGINdbms_scheduler.set_attribute(name      => '"SYS"."THURSDAY_WINDOW"',attribute => 'DURATION',value     => numtodsinterval(12, 'hour'));END;/       
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."FRIDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0');END;/



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024420/viewspace-2938250/，如需轉載，請註明出處，否則將追究法律責任。

相關文章