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;