LLI Wiki — My Digital Knowledge Base

Oracle : nombre de switchs de redo logs par heure

Requêtes pour compter les switchs de redo logs par heure et par jour avec V$LOG_HISTORY et GV$LOG_HISTORY.

Cet article présente plusieurs requêtes Oracle pour compter les changements de fichier redo log (log switches) par heure et par jour, à partir de V$LOG_HISTORY et GV$LOG_HISTORY. Ces requêtes aident à vérifier si la taille et le nombre des groupes de redo logs sont adaptés à l'activité de la base.

Taille et nombre des groupes de redo logs

select group#,MEMBERS,BYTES/1024/1024 "SIZE MB" from v$log;

Nombre de switchs par heure sur les dernières 24 heures

select to_char(first_time,'YYYY-MM-DD HH24'), count(*) 
from v$log_history 
where first_time between (sysdate - 1) and sysdate
group by to_char(first_time,'YYYY-MM-DD HH24')
order by to_char(first_time,'YYYY-MM-DD HH24');

Nombre de switchs par heure et par jour

Script SQL*Plus avec pause d'affichage. La ligne WHERE thread#=&EnterThreadId est commentée : à activer pour filtrer sur un thread (instance RAC).

-- switchLogPerHour.sql
-- Show the Number of Redo Log Switches Per Hour
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT to_char(first_time, 'yyyy - mm - dd') aday,
           to_char(first_time, 'hh24') hour,
           count(*) total
FROM   v$log_history
--WHERE  thread#=&EnterThreadId
GROUP BY to_char(first_time, 'yyyy - mm - dd'),
              to_char(first_time, 'hh24')
ORDER BY to_char(first_time, 'yyyy - mm - dd'),
              to_char(first_time, 'hh24') asc
/

Carte horaire des switchs (RAC, 7 derniers jours)

Une ligne par jour et par instance, une colonne par heure (h0 à h23), avec le total et la moyenne horaire. Cette version utilise GV$LOG_HISTORY.

set pages 999 lines 400
col h0 format 999
col h1 format 999
col h2 format 999
col h3 format 999
col h4 format 999
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
SELECT TRUNC (first_time) "Date", inst_id, 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",
 ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;

Carte horaire des switchs (14 derniers jours)

Même présentation avec V$LOG_HISTORY (instance courante). La condition trunc (first_time) >= (trunc(sysdate) - 14) fixe le nombre de jours : 0 pour aujourd'hui seulement, 1 pour aujourd'hui et hier.

set linesize 300
set pagesize 5000
set trimspool on
column 00 format 99 heading "00:00"
column 01 format 99 heading "1am"
column 02 format 99 heading "2am"
column 03 format 99 heading "3am"
column 04 format 99 heading "4am"
column 05 format 99 heading "5am"
column 06 format 99 heading "6am"
column 07 format 99 heading "7am"
column 08 format 99 heading "8am"
column 09 format 99 heading "9am"
column 10 format 99 heading "10am"
column 11 format 99 heading "11am"
column 12 format 99 heading "12:00"
column 13 format 99 heading "1pm"
column 14 format 99 heading "2pm"
column 15 format 99 heading "3pm"
column 16 format 99 heading "4pm"
column 17 format 99 heading "5pm"
column 18 format 99 heading "6pm"
column 19 format 99 heading "7pm"
column 20 format 99 heading "8pm"
column 21 format 99 heading "9pm"
column 22 format 99 heading "10pm"
column 23 format 99 heading "11pm"
column 24 format 99 heading "12pm"
column "Day" format a3

prompt
prompt Redo Log Switches
prompt

SELECT trunc (first_time) "Date",
to_char (trunc (first_time),'Dy') "Day",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23"
from v$log_history
where trunc (first_time) >= (trunc(sysdate) - 14) -- last X days. 0 = today only. 1 = today and yesterday
group by trunc (first_time)
order by trunc (first_time) DESc;

Remarques

Mastodon LLIMastodon SocialFosstodon