CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Redo
Version 11.1
 
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 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_ 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 module, osuser, sql_hash_value, value redo
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name = 'redo size'
ORDER BY redo;
Total Redo By Session Per Time Period SELECT module, osuser, sql_hash_value, value/(sysdate-logon_time) redo
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND 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
Cast
Multiset
DBMS_OUTPUT
Table Function
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [274 users online]    © 2010 psoug.org