01 | ------------------------------------------------------- |
02 | -- name : show_redo |
03 | -- author : Kim, jongbum aka proud |
04 | -- description : redo size by process |
05 | -- usage : @show_redo |
06 | -- tested version : oracle 9i , 10g |
07 | ------------------------------------------------------- |
08 |
09 | column osuser format a20 |
10 | column username format a20 |
11 | column module format a50 |
12 |
13 | select max(decode(a.process,b.spid,to_number(null),a.sid)) sid |
14 | ,max(decode(a.process,b.spid,to_number(null),a.serial#)) serial# |
15 | ,max(decode(a.process,b.spid,null,a.process)) process |
16 | ,a.osuser |
17 | ,a.username |
18 | ,a.command |
19 | ,a.pdml_enabled |
20 | ,a.module |
21 | ,a.status |
22 | ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value) sql_hash_value |
23 | ,sum(c.value) redo_size |
24 | from v$session a |
25 | ,v$process b |
26 | ,v$sesstat c |
27 | ,v$statname d |
28 | where a.paddr = b.addr |
29 | and a.sid = c.sid |
30 | and c.statistic# = d.statistic# |
31 | and d.name = 'redo size' |
32 | group by a.osuser |
33 | ,a.username |
34 | ,a.command |
35 | ,a.pdml_enabled |
36 | ,a.module |
37 | ,a.status |
38 | ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value) |
39 | / |
