Rego generation while executing select statement

Does anybody have an idea why the select statement is generating huge
amount of redo(even after multiple run). There is not much DML
activities going on against this table.

I am going to generate 10046 trace with level 12. Meantime trying to
get experts advise here.

Thanks in advance
 
SQL> l
  1  select * from (
  2  SELECT     Bank_Id,
  3     Bank_Type_Id,
  4     Country_Id,
  5     Bank_Name,
  6     Bank_Address1,
  7     Bank_Address2,
  8     Bank_Address3,
  9     Branch_Name,
 10     language_code
 11  FROM       MY_TABLE
 12  WHERE      COUNTRY_ID  = 'ABC'
 13  AND        BANK_TYPE_ID  = 'CDE'
 14  and        upd_phase='C'
 15  AND     language_code = 'en'
 16  ORDER BY BANK_ID ASC
 17  )
 18* where rownum < 500
SQL> 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=154 Card=45 Bytes=58860) 
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=154 Card=45 Bytes=58860)
   3    2       SORT (ORDER BY STOPKEY) (Cost=154 Card=45 Bytes=3915)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MY_TABLE'
(Cost=142 Card=45 Bytes=3915)
    5    4           INDEX (RANGE SCAN) OF 'MY_TABLE_IDX2'
(NON-UNIQUE)  (Cost=20 Card=45)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     241503  consistent gets
      28951  physical reads
     313092  redo size
      64583  bytes sent via SQL*Net to client
       4094  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        499  rows processed
--
http://www.freelists.org/webpage/oracle-l

Other related posts: