From time to time, we would notice Oracle generates a significant amount of redo logs than usual volumes. It might causes the system down if the flash recovey area is full due to the huge archive logs created in a short period. So the basic thing to do is to find out which program/session is causing it.

There are different dynamic views within Oracle which provide the related information: V$SESSION, V$SESS_IO, V$TRANSACTION, V$SESSSTAT, V$STATNAME

1 Using V$SESSION and V$SESS_IO. The column BLOCK_CHANGES from V$SESS_IO shows I/O activities.

Note: you need to run the query for a couple of times to see which one from the top list changes because there might be idle session which has generated large I/O activties, but not active any more.

select * from (
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) 
where rownum<10;

2. Use V$SESSION, V$SESSTAT, and V$STATNAME. The column NAME in V$STATNAME has decoded statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT tables. And you can find the statistic names from Oracle Database Reference. We are using “redo size” (class 2) which represents total amount of redo generated in bytes.

Again, run the query for a couple of times to see which one from the top list changes.

select * from (
select s.sid, ss.value, s.username, s.program, s.type, s.module
from v$sesstat ss, v$statname sn, v$session s
where ss.statistic# = ss.statistic# and s.sid = ss.sid and name ='redo size' and ss.value > 0
order by ss.value desc)
where rownum<20;

3. Use V$SESSION and V$TRANSACTION. The columns USED_UBLK and USED_UREC from V$TRANSACTION contain the usage of UNDO blocks and records. By checking UNDO usage from each tranaction, we check REDO usage indirectly. Remember Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments.

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
ORDER BY 5 desc, 6 desc;

Checking the transaction view may also reveal any transacation/query exhausting the UNDO tablespace. See the following two MOS articles:

• Detecting a blocking session on a rollback segment (Doc ID 118035.1)
• What is the transacation/query exhausting the UNDO tablespace? (Doc ID 304513.1)

We could also cross check other dynamic views to see what is the normal situation.

Checking archivelog generation on a hourly and daily basis

select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated
from v$archived_log
group by trunc(COMPLETION_TIME,'DD'), thread# order by 1;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives
from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Checking the number of Redo Log switches per hour for past 7 days

break on Day
SELECT trunc(first_time, 'DD') Day, trunc(first_time, 'HH') Hour, count(*) total 
FROM v$log_history
where first_time>sysdate-7
GROUP BY trunc(first_time, 'DD'), trunc(first_time, 'HH')
ORDER BY trunc(first_time, 'DD'), trunc(first_time, 'HH');

Once you identify which session is causing the issue, you can check V$SESSION.COMMAND to see what the last parsed statment is. Oracle Database Reference guide has a table listing all commands. And since 11.2, you can also query V$SQLCOMMAND:

SELECT command_name FROM v$sqlcommand WHERE command_type = n;

• Oracle