Redo Log History

The transaction log of the Oracle database is a group of operating system files on the database server. An Oracle database must have at least two log groups. A log group consists of one or more identical operating system files that records the log entries of committed transactions. The log group being written to by LGWR at anytime is the current group. Each log group has a static size and eventually fills with information. Once LGWR fills the current log group, Oracle performs a log switch. During a log switch, Oracle closes the current redo log group, opens the next log group, and begins writing log entries to the new current log group.

At the time of a log switch, LGWR declares a checkpoint as well. When a checkpoint occurs, LGWR will tell DBWR to write all blocks in the dirty buffer write queue to disk. Having an excessive amount of log switches during the day, can degrade system performance. If your redo log groups are too small, LGWR will spend excessive periods of time switching from one log group to another as well as DBWR spending too much time writing dirty buffers to disk.

To view how often log switches occur in your database, you can query the dynamic performance view V$LOG_HISTORY. This view records the time of each log switch in the database for the most recent n log switches, where n is the value of the database parameter MAXLOGHISTORY. MAXLOGHISTORY is a permanent database parameter and is set in the CREATE DATABASE statement. I typically set this value to 1000 on my production systems. You can change the value of MAXLOGHISTORY by either re-creating the database or simply re-creating the controlfile.

 

 

REM
REM || Title : show_log_history.sql
REM ||
REM ||

SET SERVEROUTPUT ON
SET ECHO OFF
SET PAGES 80
SET LINES 200
SET TRIMSPOOL ON

UNDEFINE start_date
UNDEFINE end_date

ACCEPT start_date PROMPT 'Enter the start date (DD-MON-YYYY): '
ACCEPT end_date PROMPT 'Enter the end date (DD-MON-YYYY): '

COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'

 

SELECT SUBSTR (TO_CHAR (first_time, 'DD/MM/RR HH:MI:SS'), 1, 5) day,
            SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '00', 1, 0)) h00,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '01', 1, 0)) h01,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '02', 1, 0)) h02,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '03', 1, 0)) h03,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '04', 1, 0)) h04,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '05', 1, 0)) h05,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '06', 1, 0)) h06,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '07', 1, 0)) h07,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '08', 1, 0)) h08,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '09', 1, 0)) h09,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '10', 1, 0)) h10,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '11', 1, 0)) h11,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '12', 1, 0)) h12,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '13', 1, 0)) h13,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '14', 1, 0)) h14,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '15', 1, 0)) h15,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '16', 1, 0)) h16,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '17', 1, 0)) h17,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '18', 1, 0)) h18,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '19', 1, 0)) h19,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '20', 1, 0)) h20,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '21', 1, 0)) h21,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '22', 1, 0)) h22,
SUM (DECODE (SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '23', 1, 0)) h23,
       COUNT (*) total
FROM v$log_history a
WHERE (TO_DATE (SUBSTR (TO_CHAR (first_time, 'DD/MM/RR HH:MI:SS'), 1, 8), 'DD/MM/RR') >= TO_DATE ('&start_date', 'DD-MON-YYYY'))
AND (TO_DATE (SUBSTR (TO_CHAR (first_time, 'DD/MM/RR HH:MI:SS'), 1, 8), 'DD/MM/RR') <= TO_DATE ('&end_date', 'DD-MON-YYYY'))
GROUP BY SUBSTR (TO_CHAR (first_time, 'DD/MM/RR HH:MI:SS'), 1, 5)

SPOOL log_history.lis
/
SPOOL OFF

 

SET ECHO ON
SET FEEDBACK ON
SET PAGES 80
SET LINES 132
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.