Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

 

Oracle log switch SQL reports

Oracle Database Tips by Donald Burleson

January 15,  2015

Oracle redo log sizing is an important task, and you also need scripts to monitor redo log switches and other redo statistics.

For complete details on monitoring log switches, see these notes from the book, which shows several important redo log switch monitoring scripts.

Oracle log switch SQL reports

Here are some sample scripts to display the log switch frequency:

set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
see code depot for full script
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD');

This log switch script is handy because it displays the log switch activity as a two-dimensional table, showing log switches by hours of the day and log switches by date:

                              LOG SWITCH FREQUENCY REPORT

DAY   00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18
----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --
01/04   0   0   0   0   0   0   0   1   2   0   0   0   5  11   1   0   0   1  0
01/05   0   0   0  15   0   0   0   0   4   1   6   5   5  32   0   1   0   3  3
01/06   1   0   0   0   0   0   0   0   1   4   6   1   3   9  10   5   0   1  1
01/07   1   0   0   0   0   0   0   0   2   1   2   0   7  14   0   1   1   0  0
01/08   1   0   0   0   0   0   0   0   0   0   0   0   0  21   0   0   0   0  0
01/09   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0  0
01/10   0   0   0   0   0   0   0   0   0   1   9   4   1  60   1   2   0   0  0
01/11   1   0   0  14   0   0   0   0   2   1   1   1   1   1   0   0   1   0  0
12/12   0   0   0   0   2   0   0   2   1   0  35   1   1  37  31  10  17   0  1

 

rem
rem Name:     log_stat.sql
rem
rem FUNCTION: Provide a current status for redo logs
rem
rem
COLUMN first_change# FORMAT 99999999  HEADING Change#
COLUMN group#        FORMAT 9,999     HEADING Grp#
COLUMN thread#       FORMAT 999       HEADING Th#
COLUMN sequence#     FORMAT 999,999   HEADING Seq#
COLUMN members       FORMAT 999       HEADING Mem
COLUMN archived      FORMAT a4        HEADING Arc?

COLUMN first_time    FORMAT a21       HEADING 'Switch|Time'

BREAK ON thread#
SET PAGES 60 LINES 131 FEEDBACK OFF

START title132 'Current Redo
Log Status'

SPOOL rep_out\&db\log_stat
SELECT thread#,group#,sequence#,bytes,
       members,archived,
       status,first_change#,
       TO_CHAR(first_time, 'DD-MM-YYYY HH24:MI:SS') first_time
  FROM

               see code depot for full script
       sys.v_$
log

  ORDER BY
       thread#,
       group#;
SPOOL OFF
PAUSE Press Enter to continue
SET PAGES 22 LINES 80 FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS
TTILE OFF

LISTING 11.22 Example output of script to monitor redo log status.



Date: 06/15/97                                               Page:   1

Time: 01:39 PM           Current Redo
Log Status                SYSTEM 

                             ORTEST1 database

Switch                                                                          

Th# Grp#  Seq#     BYTES Mem Arc? STATUS   Change# Time

--- ---- -----   ------- -------- -------- ------- ------------------
 1    1 4,489   1048576   2 NO   INACTIVE  719114 15-JUN-97 16:54:23
      2 4,490   1048576   2 NO   INACTIVE  719117 15-JUN-97 16:56:10
      3 4,491   1048576   2 NO   CURRENT   719120 15-JUN-97 17:02:22

Monitoring Redo Log Switches

In addition to the alert logs, the frequency of log switches can also be monitored via the v$log_history and v$archived_log views. This script shows an example of a script to monitor archive log switches:

REM NAME         :log_hist.sql
REM PURPOSE      :Provide info on logs for last 24 hours since last

REM PURPOSE      :
log switch

REM USE          :From SQLPLUS
REM Limitations  : None
REM MRA 10/14/01 Updated for Oracle9i
REM
COLUMN thread#             FORMAT 999      HEADING 'Thrd#'
COLUMN sequence#           FORMAT 99999    HEADING 'Seq#'
COLUMN first_change#                       HEADING 'SCN Low#'
COLUMN next_change#                        HEADING 'SCN High#'

COLUMN archive_name        FORMAT a50      HEADING '
Log File'
COLUMN first_time          FORMAT a20      HEADING '
Switch Time'
COLUMN name                FORMAT a30      HEADING 'Archive
Log'

SET LINES 132 FEEDBACK OFF VERIFY OFF
START title132 "
Log History Report"

SPOOL rep_out\&db\log_hist
REM
SELECT
     X.recid,a.thread#,
     a.sequence#,a.first_change#,
     a.switch_change#,
     TO_CHAR(a.first_time,'DD-MON-YYYY HH24:MI:SS') first_time,x.name
FROM
see code depot for full script
 v$loghist a, v$archived_log x
WHERE
  a.first_time>
   (SELECT b.first_time-1
   FROM v$loghist b WHERE b.switch_change# =
    (SELECT MAX(c.switch_change#) FROM v$loghist c)) AND
     x.recid(+)=a.sequence#;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue

Monitoring Redo Statistics

There are no views in Oracle that allow the user to look directly at a log file's statistical data. Instead, we must look at statistics based on redo log and log writer process statistics. These statistics are in the views V$STATNAME, V$SESSION, V$PROCESS, V$SESSTAT, V$LATCH, and V$LATCHNAME. An example of a report that uses these views is shown in Source 11.29; an example of the script's output is shown in Listing 11.24.

Script to generate reports on redo statistics.

REM
REM NAME          : rdo_stat.sql
REM PURPOSE       : Show REDO latch statistics
REM USE           : from SQLPlus
REM Limitations   : Must have access to v$_ views
REM
SET PAGES 56 LINES 78 VERIFY OFF FEERemote DBACK OFF
START title80 "Redo Latch Statistics"
SPOOL rep_out/&&db/rdo_stat
rem
COLUMN name      FORMAT a30          HEADING Name
COLUMN percent   FORMAT 999.999      HEADING Percent
COLUMN total                         HEADING Total
rem
SELECT
     l2.name,
     immediate_gets+gets Total,
     immediate_gets "Immediates",
     misses+immediate_misses "Total Misses",
     DECODE (100.*(GREATEST(misses+immediate_misses,1)/
     GREATEST(immediate_gets+gets,1)),100,0) Percent
FROM
see code depot for full script
     v$latch l1,
     v$latchname l2
WHERE
     l2.name like '%redo%'
     and l1.latch#=l2.latch# ;
rem
PAUSE Press Enter to continue
rem
rem Name: Redo_stat.sql
rem
rem Function: Select redo statistics from v$sysstat

COLUMN name    FORMAT a30         HEADING 'Redo|Statistic|Name'
COLUMN value   FORMAT 999,999,999 HEADING 'Redo|Statistic|Value'
SET PAGES 80 LINES 60 FEERemote DBACK OFF VERIFY OFF
START title80 'Redo Log Statistics'
SPOOL rep_out/&&db/redo_stat
SELECT
     name,
     value
FROM
     v$sysstat
WHERE
     name LIKE '%redo%'
ORDER BY statistic#;
SPOOL OFF
SET LINES 24 FEERemote DBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS

 Also see these related notes on redo log switch frequency:


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.