DBA Tips Archive for Oracle

  


Redo Log Switch History

by Jeff Hunter, Sr. Database Administrator

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.

Below are two scripts that can be used to identify how often your system is performing a log switch during the day. Script #1 is for Oracle7 while Script #2 can be used for Oracle8. The time column in V$LOG_HISTORY was changed from a VARCHAR2(257) field in Oracle7 to a DATE field in Oracle8. Also below is an example output of the script.

SET VERIFY OFF
SET LINESIZE 136
SET PAGESIZE 66

ACCEPT startDate PROMPT 'Enter start date (DD-MON-YYYY): '
ACCEPT endDate   PROMPT 'Enter 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'

SPOOL log_switch_history.lst

SELECT
    SUBSTR(time,1,5)                        DAY
  , SUM(DECODE(SUBSTR(time,10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(time,10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(time,10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(time,10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(time,10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(time,10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(time,10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(time,10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(time,10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(time,10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(time,10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(time,10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(time,10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(time,10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(time,10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(time,10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(time,10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(time,10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(time,10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(time,10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(time,10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(time,10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(time,10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(time,10,2),'23',1,0)) H23
  , COUNT(*)                                TOTAL
FROM
    v$log_history  a
WHERE
    (TO_DATE(SUBSTR(time, 1,8), 'MM/DD/RR')
     >=
     TO_DATE('&startDate', 'DD-MON-YYYY')
     )
     AND
    (TO_DATE(SUBSTR(time, 1,8), 'MM/DD/RR')
     <=
     TO_DATE('&endDate', 'DD-MON-YYYY')
     )
GROUP BY SUBSTR(time,1,5)
/

SPOOL off
Script #1 : Log switch history script for Oracle7
SET VERIFY OFF
SET LINESIZE 136
SET PAGESIZE 66

ACCEPT startDate PROMPT 'Enter start date (DD-MON-YYYY): '
ACCEPT endDate   PROMPT 'Enter 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'

SPOOL log_switch_history.lst

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/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, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
     >=
     TO_DATE('&startDate', 'DD-MON-YYYY')
     )
     AND
    (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
     <=
     TO_DATE('&endDate', 'DD-MON-YYYY')
     )
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
/

SPOOL OFF
Script #2 : Log switch history script for Oracle8
SQL> @log_switch_history
Enter start date (DD-MON-YYYY): 01-DEC-1999
Enter end date   (DD-MON-YYYY): 31-DEC-1999

DAY     00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23    Total
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --------
12/01    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    3        3
12/02    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        1
12/03    0    0    0    0    0    0    0    0    0    0    2    7    0    0    0    0    0    0    1    0    0    0    0    0       10
12/04    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        1
12/06    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    1        2
12/07    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1        2
12/08    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    4        4
12/09    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1    0    0    0    0    0    0    0    1        3
12/10    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0        1
12/11    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        1
12/13    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        1
12/14    0    0    0    0    0    0    0    0    0    0    0    0    2    0    0    0    0    0    0    0    0    0    0    0        2
12/15    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0        2
12/16    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0        1
12/17    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0        1
12/19    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        1
12/21    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        1
12/22    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0   32    0    0    0    0    0    0    0    0       32
12/23    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        1
12/25    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        1
12/27    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0        2
12/28    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0        1

22 rows selected.
Example Output


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 01-Mar-2000 00:00:00 EST
Page Count: 38148