Oracle | Redo and Redo Logs

Oracle Redo Version 21c

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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#; Percentage ---------- 27.15

Redo generated set linesize 181 set pagesize 99 col MMDD format a4 col 00 format 999 col 01 format 999 col 02 format 999 col 03 format 999 col 04 format 999 col 05 format 999 col 06 format 999 col 07 format 999 col 08 format 999 col 09 format 999 col 10 format 999 col 11 format 999 col 12 format 999 col 13 format 999 col 14 format 999 col 15 format 999 col 16 format 999 col 17 format 999 col 18 format 999 col 19 format 999 col 20 format 999 col 21 format 999 col 22 format 999 col 23 format 999 SELECT TO_CHAR(first_time,'MMDD') MMDD, TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') "21", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23" FROM v$log_history GROUP BY TO_CHAR(first_time,'MMDD') ORDER BY 1; MMDD 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 0609 16 11 9 8 8 10 12 8 8 10 8 10 14 10 11 15 15 8 12 8 7 6 9 7 0610 13 12 8 9 7 6 11 9 6 8 7 8 12 6 7 6 8 7 10 7 4 4 4 5 0611 12 8 5 9 9 7 11 7 6 7 8 5 12 9 10 8 9 12 12 10 6 6 9 8 0612 13 12 7 9 7 9 10 10 7 7 9 8 11 7 7 8 7 7 11 9 5 6 8 7 0613 12 11 7 8 8 7 13 7 9 7 8 7 13 10 9 8 8 8 11 8 7 5 7 6 0614 15 10 9 9 8 9 13 9 9 7 11 13 11 9 8 9 13 9 12 9 7 9 7 7 0615 15 10 10 8 10 9 12 8 9 8 9 7 13 6 8 7 7 7 15 10 7 7 7 5 0616 13 8 8 7 7 6 10 8 11 7 8 6 11 7 12 13 13 14 13 9 9 9 7 8 0617 15 13 10 9 8 9 16 8 8 10 9 10 16 11 10 10 8 11 13 8 9 9 7 9 0618 12 13 15 15 13 13 15 13 9 12 8 11 14 9 10 9 9 8 14 9 8 8 9 8 0619 16 11 10 11 9 9 13 12 10 9 12 12 17 8 9 9 11 11 14 9 9 11 10 12 0620 19 15 11 10 10 10 19 11 9 9 9 9 13 7 15 10 11 11 12 10 9 11 11 10 0621 13 16 11 9 10 13 16 8 14 9 11 12 17 10 10 11 8 11 14 8 11 14 8 11 0622 16 13 13 11 11 9 16 9 9 11 10 11 17 10 9 10 10 10 13 14 9 10 10 8 0623 19 13 12 13 13 11 16 12 11 11 11 11 16 9 10 13 2 14 14 8 9 8 8 8 0624 14 9 9 9 7 9 11 8 8 7 8 8 14 7 8 7 9 3 6 0 0 0 0 0 0625 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0626 0 1 0 0 0 0 0 0 0 4 0 0 0 2 2 3 2 7 5 6 1 0 0 0 0627 3 10 0 0 0 5 0 1 10 0 0 0 0 0 1 0 1 0 2 5 3 7 1 0 0629 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 4 0 0 6 7 6 0630 7 4 23 19 9 10 5 6 7 17 19 17 15 17 15 43 40 32 17 15 14 20 13 15 0701 15 12 14 12 13 12 13 17 15 17 20 20 18 18 17 15 14 13 10 10 15 15 13 19 0702 21 22 20 18 14 14 12 13 11 11 14 14 14 10 9 10 9 10 11 9 11 9 10 12 0703 9 13 10 17 14 17 15 17 23 20 19 20 17 19 16 17 15 17 15 15 15 16 16 18 0704 22 19 19 18 16 15 13 13 14 11 13 10 12 14 10 12 14 11 9 11 12 13 12 9 0705 14 13 9 11 10 12 13 11 11 8 10 10 11 11 11 12 10 10 9 10 8 9 12 7 0706 14 15 11 12 9 15 13 12 12 9 12 14 12 12 12 12 13 11 8 9 12 13 2 0 0707 0 0 1 0 3 15 10 10 7 8 10 11 12 8 6 9 13 12 9 8 9 8 10 10 0708 16 9 8 15 10 11 9 8 8 14 9 10 10 8 8 14 15 10 9 9 8 9 10 10 0709 13 12 9 10 10 9 9 10 11 11 8 9 9 8 9 13 8 9 6 9 9 11 10 9 0710 12 10 9 10 9 12 9 8 8 11 7 10 11 9 9 13 10 9 8 9 11 12 10 10 0711 15 12 9 13 9 12 8 10 11 13 9 8 10 9 8 12 11 12 9 9 10 11 10 8 0712 13 12 10 13 10 10 9 7 10 11 9 10 12 12 12 15 12 9 8 9 11 12 12 12 0713 14 12 12 11 10 10 12 12 12 15 10 11 11 10 4 5 15 14 10 9 8 8 13 6 0714 12 12 9 9 11 10 10 9 10 9 14 7 7 8 8 9 14 9 9 10 12 8 13 10 0715 10 10 9 14 12 15 12 14 13 15 10 11 9 4 8 6 8 7 6 7 8 8 8 8 0716 10 11 9 8 8 9 9 6 6 7 7 12 7 9 15 14 13 16 12 14 11 9 6 7 0717 10 10 9 9 9 10 12 14 11 10 12 9 8 12 7 3 0 0 0 0 0 0 0 0

Measuring Redo

Measures Generate 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; / exec redo_diff.diff_it; redo diff: 0

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 a35 col osuser format a20 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' AND s.module IS NOT NULL ORDER BY redo; MODULE OSUSER SQL_HASH_VALUE REDO ----------------------------------- ------- --------------- ------ KTSJ oracle 0 0 MMON_SLAVE oracle 0 0 Streams oracle 0 0 KTSJ oracle 0 0 Streams oracle 2864266898 0 oraagent.bin@test21 (TNS V1-V3) oracle 0 0 MMON_SLAVE oracle 0 0 KTSJ oracle 0 0 MMON_SLAVE oracle 0 0 Streams oracle 0 0 KTSJ oracle 0 0 MMON_SLAVE oracle 0 0 KTSJ oracle 0 0 oraagent.bin@test21 (TNS V1-V3) oracle 222495440 728 KTSJ oracle 0 1440 KTSJ oracle 0 3900 oraagent.bin@test21 (TNS V1-V3) oracle 0 4012 KTSJ oracle 0 4844 KTSJ oracle 0 5412 oraagent.bin@test21 (TNS V1-V3) oracle 3099625463 5700 oraagent.bin@test21 (TNS V1-V3) oracle 2338274268 5892 sqlplus@test21 (TNS V1-V3) oracle 2625839219 116968

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' AND s.sql_hash_value <> 0 ORDER BY redo; MODULE OSUSER SQL_HASH_VALUE REDO ----------------------------------- ------- -------------- ---------- Streams oracle 2864266898 0 oraagent.bin@test21 (TNS V1-V3) oracle 222495440 4.55198919 oraagent.bin@test21 (TNS V1-V3) oracle 3099625463 35.6406057 oraagent.bin@test21 (TNS V1-V3) oracle 2338274268 36.8411153 sqlplus@test21 (TNS V1-V3) oracle 426530774 5501380.08

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# AND s.value <> 0 ORDER BY value; SID NAME VALUE ---- -------------------- -------- 135 redo blocks written 125132 130 redo blocks written 3532752 384 redo blocks written 4790949

Redo Generated by the current SID col sid form 9999 col program form a30 col logtime head "Logon Time" form a15 SELECT st.sid, se.logon_time, 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 2,3; SID LOGON_TIME PROGRAM REDO_GEN_MB ---- -------------------- ------------------------------ ----------- 168 17-JAN-2021 02:21:44 oracle@test21 (OFSD) 0 10 17-JAN-2021 02:21:45 oracle@test21 (DBW0) 87 172 17-JAN-2021 02:21:45 oracle@test21 (LGWR) 884 30 17-JAN-2021 02:22:00 oracle@test21 (TT01) 0 199 17-JAN-2021 02:22:07 oracle@test21 (AQPC) 0 212 17-JAN-2021 02:22:21 oraagent.bin@test21 (TNS V1-V3) 0 215 17-JAN-2021 02:22:27 oraagent.bin@test21 (TNS V1-V3) 0 217 17-JAN-2021 02:22:33 oraagent.bin@test21 (TNS V1-V3) 0 51 17-JAN-2021 02:22:34 oraagent.bin@test21 (TNS V1-V3) 0 187 17-JAN-2021 02:32:09 oracle@test21 (W006) 0 56 26-JUN-2021 00:11:03 sqlplus@test21 (TNS V1-V3) 0