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
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
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
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
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
|
|
