How to Find Sessions Generating Lots of Redo or Archive logs

There  are  two methods   to  find  sessions generating lots of redo

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

Note: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.

Index Skip Scan

If you have an composite index  (Index consist of more  than one  column) and you use the non-prefix column alone in your SQL, it may still use index  dependening  of the  cost  (CBO will calculate the cost of using the index and compare it with full table scan)

Index skip scan works differently from a normal index (range) scan.A normal range scan works from top to bottom first and then move horizontal.But a Skip scan includes several range scans in it. Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan

Example

SQL>create table SERDAR.skiptest(a number,b number,c number);
SQL>create index SERDAR.ix1 on SERDAR.skiptest (a,b);
SQL>
declare

begin
for i in 1 .. 100000
loop
  insert into skiptest values(mod(i, 5), i, 100);
end loop;
commit;
end;
/

SQL>exec dbms_stats.gather_table_stats(ownname => 'SERDAR', tabname => 'skiptest', cascade => true);

SQL>set autotrace traceonly
SQL>select * from skiptest where b=88888;

Execution Plan


----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=10)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SKIPTEST' (TABLE) (Cost=22 Card=1 Bytes=10)
2 1 INDEX (SKIP SCAN) OF 'IX1' (INDEX) (Cost=21 Card=1)


In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column.Above example, “select * from test where b=88888” was broken down to several small range scan queries. It was effectively equivalent to following

Select * from skiptest where a=0 and b=88888

Union
Select * from skiptest where a=1 and b=88888
Union
Select * from skiptest where a=2 and b=88888
Union
Select * from skiptest where a=3 and b=88888
Union
Select * from skiptest where a=4 and b=88888;

Conclusion 
Skip scan is not as efficient as normal “single range scan”