Log Miner

Log Miner

To properly investigate the problem:


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

BEGIN
   DBMS_LOGMNR.
    START_LOGMNR (STARTTIME   => :l_start,
                  ENDTIME     => :l_end,
                  OPTIONS     => DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/



1. select operation,count(*) from v$logmnr_contents group by operation;


2. select username, operation, count(*) from v$logmnr_contents where operation in ('DELETE','INSERT','UPDATE','DDL')  group by operation, username order by 3 desc,2,1;

3. select count(*) as hits, seg_owner||'.'||seg_name "SEG_OWNER.SEG_NAME"  from v$logmnr_contents group by seg_owner, seg_name order by 1 desc ;

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

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

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

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.

Please run these queries a couple of times while the excessive logs are being generated and let us know the results (in a readable format like csv, for example).

6. alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select trunc(first_time, 'HH') , count(*)
from v$loghist
group by trunc(first_time, 'HH')
order by trunc(first_time, 'HH');