Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Oracle Monitoring Redo Statistics
 

Chapter 6 Oracle Tablespace and File Internals Scripts

Monitoring Redo Statistics

There are no views in Oracle that allow the user to look directly at a log file’s statistical data. It is necessary

instead to derive statistics from redo log and log writer process statistics. These statistics can be found 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 below. An example of the script’s output is also shown.

rdo_stat.sql

SET PAGES 56 LINES 78 VERIFY OFF FEEDBACK OFF
ttitle "Redo Latch Statistics"
SPOOL rdo_stat

COLUMN name      FORMAT a30          HEADING Name
COLUMN percent   FORMAT 999.999      HEADING Percent
COLUMN total                         HEADING Total
 
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
SEE CODE DEPOT FOR FULL SCRIPT
 
COLUMN name    FORMAT a30         HEADING 'Redo|Statistic|Name'
COLUMN value   FORMAT 999,999,999 HEADING 'Redo|Statistic|Value'
SET PAGES 80 LINES 60 FEEDBACK OFF VERIFY OFF
ttitle 'Redo Log Statistics'
SPOOL redo_stat
 
SELECT
   name,
   value
FROM
   v$sysstat
SEE CODE DEPOT FOR FULL SCRIPT
ORDER BY
   statistic#;
 
SPOOL OFF
SET LINES 24 FEEDBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
 

CLEAR BREAKS

Here is a sample listing:

Name                     Total  Immediates Total Misses Percent
------------------------ ------ ---------- ------------ -----
redo allocation          172438  0         0
redo copy                6259    6231      0
redo writing             672470  0         0
Press Enter to continue


Redo                                   Redo
Statistic                         Statistic
Name                                  Value
------------------------------ ------------
redo synch writes                       250
redo synch time                          72
redo entries                          6,231
redo size                         1,569,816
redo buffer allocation retries            0
redo wastage                      1,200,696
redo writer latching time                 0
redo writes                           3,635
redo blocks written                   5,586
redo write time                         151
redo log space requests                   0
redo log space wait time                  0
redo log switch interrupts                0
redo ordering marks                       0

These numbers require a little interpretation.  Let’s look at what they mean and how they can be used. The first

section of the report should be self-explanatory. The redo logs use two latches, REDO ALLOCATION and REDO COPY.

As a general rule, if the percent statistic (actually, the ratio of total misses to total gets) is greater than 10 percent, contention is occurring, and the way the redo logs are done should be examined (more about this in a second).

The initial latch granted for redo is the redo_allocation latch. The redo_copy latch is granted to a user when the size of the entry is greater than the log_small_entry_max_size parameter in the initialization file. If there is redo_allocation latch contention, decrease the value of _log_small_entry_max_size.

A system with a single CPU will show contention if there is more than one user that requires the redo_copy latch. The number of redo_copy latches is limited to twice the number of CPUs on the system. If there is only one CPU, only one latch is allowed. It is normal to see high contention for this latch on single-CPU systems, but there is nothing the DBA can do to increase the number of redo_copy latches.

However, even on single-CPU systems, Oracle can be forced to prebuild redo entries, thereby reducing the number of latches required. Raising the value of the _log_entry_prebuild_threshold entry in the initialization file does this. On multiple-CPU systems, increase the number of redo_copy latches to twice the number of CPUs.


 



 

The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:
 


 

   

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks