Re: Rego generation while executing select statement

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
> --
> http://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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: