Oracle Redo
Version 12.1.0

Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Measure Redo
Detecting Contention for Space in the Redo Log Buffer (should be less than 1%) SELECT ROUND(GREATEST((SUM(DECODE (ln.name, 'redo copy', misses,0)) / GREATEST(SUM(DECODE(ln.name, 'redo copy', gets,0)),1)), (SUM(DECODE(ln.name, 'redo allocation', misses,0)) / GREATEST(SUM(DECODE(ln.name, 'redo allocation', gets,0)),1)), (SUM(DECODE(ln.name, 'redo copy', immediate_misses,0)) / GREATEST(SUM(DECODE(ln.name, 'redo copy', immediate_gets,0)) + SUM(DECODE(ln.name, 'redo copy', immediate_misses,0)),1)),
(SUM(DECODE(ln.name, 'redo allocation', immediate_misses,0)) / GREATEST(SUM(DECODE(ln.name, 'redo allocation', immediate_gets,0)) + SUM(DECODE(ln.name, 'redo allocation', immediate_misses,0)),1))) * 100,2) AS "Percentage"
FROM gv$latch l, gv$latchname ln
WHERE l.latch# = ln.latch#;
Redo generated SELECT n.name, t.value
FROM v$mystat t, v$statname n
WHERE t.statistic# = n.statistic#
AND n.name = 'redo size';
 
Measuring Redo
Package That Measures Generated Redo conn / as sysdba

grant select on gv_$sysstat to uwclass;

conn uwclass/uwclass

CREATE OR REPLACE PACKAGE redo_diff AUTHID DEFINER IS
  PROCEDURE diff_it;
END redo_diff;
/

CREATE OR REPLACE PACKAGE BODY redo_diff IS
 s NUMBER;
 --=========================================
 FUNCTION get_size RETURN NUMBER IS
  s_  NUMBER;
 BEGIN
   SELECT value
   INTO s_
   FROM sys.v_$sysstat
   WHERE name = 'redo size';

   RETURN s_;
 END get_size;
 --=========================================
PROCEDURE diff_it IS
 s_new NUMBER;
BEGIN
  s_new := get_size;
  dbms_output.put_line('redo diff: ' || TO_CHAR(s_new - s));
  s := s_new;
END diff_it;
--=========================================
-- intialization section

BEGIN
   s := get_size;
END redo_diff;
/
Run The Test CREATE OR REPLACE TYPE subst_ AUTHID DEFINER AS OBJECT (
rn NUMBER,
ob VARCHAR2(128));
/

CREATE OR REPLACE TYPE subst_t_ AS TABLE OF subst_;
/

set serveroutput on

DECLARE
 t       subst_t_;
 time1_  NUMBER;
 time2_  NUMBER;
 sz1_    NUMBER;
 sz2_    NUMBER;
BEGIN
  redo_diff.diff_it;

  time1_ := dbms_utility.get_time;

  -- fill 50000 records
  SELECT CAST(MULTISET(SELECT rownum, a.object_name
                        FROM all_objects a, all_objects b,
                        all_objects c, all_objects d
                        WHERE SUBSTR(a.object_name,1,1) BETWEEN 'A' AND 'W'
                        AND SUBSTR(b.object_name,1,1) BETWEEN 'A' AND 'W'
                        AND SUBSTR(c.object_name,1,1) BETWEEN 'A' AND 'W'
                        AND SUBSTR(d.object_name,1,1) BETWEEN 'A' AND 'W'
                        AND rownum <= 50000) AS subst_t_)
  INTO t
  FROM dual;

  sz1_ := t.count;
  time2_ := dbms_utility.get_time;

  dbms_output.put_line('filled ' || sz1_ || ' records, time used: ' ||
                       TO_CHAR((time2_ - time1_)/100, '99999.00') || ' secs');

  redo_diff.diff_it;

  -- delete approx 50%

  SELECT CAST(MULTISET(SELECT rn, ob
                       FROM TABLE(CAST(t as subst_t_))
  WHERE SUBSTR(ob,1,2) > 'DB') AS subst_t_)
  INTO t
  FROM dual;

  sz2_ := t.count;
  time1_ := dbms_utility.get_time;

  dbms_output.put_line('deleted ' || to_char(sz1_ - sz2_) || ' records, time used: ' ||
                       TO_CHAR((time1_-time2_)/100, '99999.00') || ' secs');

  redo_diff.diff_it;
END;
/
Total Redo By Session set linesize 121
col module format a30

SELECT s.module, s.osuser, s.sql_hash_value, ss.value redo
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name = 'redo size'
ORDER BY redo;
Total Redo By Session Per Time Period col module format a30
col osuser format a20

SELECT s.module, s.osuser, s.sql_hash_value, ss.value/(SYSDATE-s.logon_time) redo
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name = 'redo size'
ORDER BY redo;
Redo Generation by SID col value format 9999999

SELECT s.sid, n.name, s.value
FROM gv$sesstat s, gv$statname n
WHERE n.name = 'redo blocks written'
AND s.statistic# = n.statistic#
ORDER BY value;
Redo Generated by SID col sid form 9999
col username form a10
col value Head "Redo|Generated|in MB" form 9999999999.999
col program form a30
col logtime head "Logon Time" form a15

SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') LOGTIME, se.program, (value/1048576) VALUE
FROM gv$sesstat st, gv$statname sn, gv$session se
WHERE sn.name = 'redo size'
AND sn.statistic# = st.statistic#
AND st.sid = se.sid
AND value <> 0
ORDER BY 5;

Related Topics
Archive Log Mode
Log Files

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved