Oracle Redo
Version 18.1.0.0

Library Note Morgan's Library Page Header
"We don't live in a dictatorship or a monarchy. I swore an oath in the military and in the Senate to preserve, protect and defend the Constitution of the United States, not to mindlessly cater to the whims of Cadet Bone Spurs and clap when he demands I clap,"
~ Sen. Tammy Duckworth
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 col value format 999999999999

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 / sys@pdbdev as sysdba

GRANT select ON gv_$sysstat to uwclass;

conn uwclass/uwclass@pdbdev

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 the current 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 6;

Related Topics
Archive Log Mode
Built-in Functions
Built-in Packages
Log Files
What's New In 12cR2
What's New In 18cR1

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-2017 Daniel A. Morgan All Rights Reserved