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