資料庫繁忙程度監控

資料庫繁忙程度監控有多種引數可以選擇，本文選擇其中之一的redolog切換頻率分佈圖

1. 獲取最近時間資料庫redolog切換頻率分佈資料
   

   SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0,
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
   SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg"
   FROM gv$log_history
   WHERE first_time >= trunc(SYSDATE) - 30
   and thread# = inst_id
   GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
   ORDER BY 1 DESC;
   

2. 獲取截至當前時刻，當天資料庫redolog已切換次數。

   SELECT TRUNC(first_time),COUNT(1) FROM gv$log_history WHERE first_time>=trunc(SYSDATE) GROUP BY TRUNC(first_time) ORDER BY 1 DESC
   

3. 如若發現當前時刻的切換次數遠大於歷史同期水平，可判斷為資料庫有異常繁忙現象，可人工跟進排查。