Oracle Redo and Redo Generation

Oracle Redo Version 12.1.0

Library Note

Morgan's Library Page Footer

This demo can be run in any 12c Database whether CDB or Traditional now let's see what happens when I keep typing to the point that this all runs into multiple lines

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;