You have a "traditional" temporary tablespace (ie datafile not tempfile)? If so, the sorting could generating redo hth connor --- Sami Seerangan <dba.orcl@xxxxxxxxx> wrote: > 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 > -- > //www.freelists.org/webpage/oracle-l > ===== Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" Co-author: "Oracle Insight - Tales of the OakTable" web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald@xxxxxxxxx "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ------------------------------------------------------------ __________________________________ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- //www.freelists.org/webpage/oracle-l