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;
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;
Free