Find a session which are generating more archive logs

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

i.block_changes

FROM v$session s, v$sess_io i

WHERE s.sid = i.sid

ORDER BY 5 desc, 1, 2, 3, 4;

SID SERIAL# USERNAME PROGRAM BLOCK_CHANGES

———- ———- ———— ———————–

158 6 SCOTT sqlplus.exe 630295

159 3 SYS sqlplus.exe 97

161 1 ORACLE.EXE (MMON) 58

164 1 ORACLE.EXE (SMON) 34

148 5 ORACLE.EXE (q001) 0

……..

19 rows selected.

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session. Like Scott user with Sid 158 is having high value for Block_changes and is the main session for generating more archive logs.

2) Query V$TRANSACTION. This view contains information about the amount of

undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

t.used_ublk, t.used_urec

FROM v$session s, v$transaction t

WHERE s.taddr = t.addr

ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

SID SERIAL# USERNAME PROGRAM USED_UBLK USED_UREC

———- ———- ———— —————————

158 6 SCOTT sqlplus.exe 4929 157526

SQL>

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by

the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

From the above example we can see that user Scott is generating more archive logs. To know which SQL statment Scott user is executed

SQL> select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks,

(t.USED_UBLK*8192/1024) KBytes from v$transaction t,

v$session s,

v$sql sql

where t.addr = s.taddr

and s.sql_id = sql.sql_id

and s.username ='&USERNAME';

Enter value for username: SCOTT

old 7: and s.username ='&USERNAME'

new 7: and s.username ='SCOTT'

SQL_TEXT RECORDS BLOCKS KBYTES

———- ———- ———-

insert into emp_c select * from emp_c 157526 4929 39432

SQL>