Rego generation while executing select statement

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Fri, 25 Feb 2005 10:32:26 -0500
Message-ID: <f09dd628050225073254df8308@mail.gmail.com>

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

Received on Fri Feb 25 2005 - 10:35:44 CST