Re: Rego generation while executing select statement

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Sat, 26 Feb 2005 02:27:23 +0000 (GMT)
Message-ID: <20050226022723.38642.qmail@web86901.mail.ukl.yahoo.com>

You have a "traditional" temporary tablespace (ie datafile not tempfile)?

If so, the sorting could generating redo

hth
connor

Sami Seerangan <dba.orcl_at_gmail.com> 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_at_yahoo.com

"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

Received on Fri Feb 25 2005 - 21:30:28 CST