FAQ
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

Search Discussions

  • Connor McDonald at Feb 25, 2005 at 9:30 pm
    You have a "traditional" temporary tablespace (ie datafile not tempfile)?

    If so, the sorting could generating redo

    hth
    connor

    Sami Seerangan 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!?
  • Alexander Gorbachev at Feb 26, 2005 at 10:52 am
    I thought that it might be the case, but the output shows that sorts
    are in memory.
    3 sorts (memory)
    0 sorts (disk)
    Maybe SQL plus doesn't repors disk sorts correctly.

    Regards,
    Alex

    On Sat, 26 Feb 2005 02:27:23 +0000 (GMT), Connor McDonald
    wrote:
    You have a "traditional" temporary tablespace (ie datafile not tempfile)?

    If so, the sorting could generating redo

    hth
    connor
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Põder at Feb 26, 2005 at 12:48 pm
    Hi,

    You could get additional information on sorting by setting event 10032 at
    level 1. Nevertheless, your user's temporary tablespace should point to a
    nologging LMT temporary tablespace anyway.

    If sorting and delayed block cleanout is ruled out (if executing the same
    query generates redo again and again, while there is no other transactions
    on the table), then maybe the redo is generated by fine grained auditing?
    Check from dba_audit_policies...

    Tanel.

    Original Message -----
    From: "Alexander Gorbachev"
    To:
    Cc: "oracle-l"
    Sent: Saturday, February 26, 2005 3:49 PM
    Subject: Re: Rego generation while executing select statement
    I thought that it might be the case, but the output shows that sorts
    are in memory.
    3 sorts (memory)
    0 sorts (disk)
    Maybe SQL plus doesn't repors disk sorts correctly.

    Regards,
    Alex

    On Sat, 26 Feb 2005 02:27:23 +0000 (GMT), Connor McDonald
    wrote:
    You have a "traditional" temporary tablespace (ie datafile not tempfile)?

    If so, the sorting could generating redo

    hth
    connor
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • K Gopalakrishnan at Feb 26, 2005 at 1:00 am
    Sami:

    Never trust the autotrace numbers. It may not be correct. Can you
    please run your test in an isolated environment and verify the the redo
    generation.

    You can use the script at
    http://www.jlcomp.demon.co.uk/faq/redologuse.html to find out how much
    redo is filled in the current redolog. You can run your SELECT and
    then run the script again to get the status of the current redolog. If
    the delta is really big, (greater than or equal to the redo size
    reported in autotrace), then you can dump the redolog contents or use
    logminer to get the acutal redo records and that will tell the truth.

    Have a nice day !!

    Best Regards,
    K Gopalakrishnan,
    Co-Author: Oracle Wait Interface: Oracle Press 2004.
    http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
  • Egor Starostin at Feb 26, 2005 at 4:38 am
    Never trust the autotrace numbers. It may not be correct.
    Why? sqlplus just read from v$sesstat. It may adjust results from
    v$sesstat a little but not so much (more than 300k).

    Connor's suggestion seems to be right. At least it's reproducible.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 25, '05 at 10:35a
activeFeb 26, '05 at 12:48p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase