Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: increase in amount of redo comparing oracle 7 and 9

Re: increase in amount of redo comparing oracle 7 and 9

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 30 Dec 2003 07:39:29 -0800
Message-ID: <F001.005DB445.20031230073929@fatcity.com>










No, you insert 10000 rows to your table in 9i, but only 6319 in 7.3.
 
Also, obj$ has probably more (filled) columns in 9i compared to 7.3.
Redo structure has changed between these versions, undo most likely as well. There are several other issues which might affect redo size such is supplemental logging, etc.
 
Tanel.
 
----- Original Message -----
From: Jeroen van Sluisdam
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, December 30, 2003 4:59 PM
Subject: increase in amount of redo comparing oracle 7 and 9

 

 

I have recently migrated our oracle 7.3.4 environment to oracle 9.2.0.4

I noticed some batches eating up all my archive space. I have a 5 Gb filesystem solely

for archiving available where I used to have 4Gb available for oracle 7 which was quite enough for years.

 

A small test:

 

Oracle 9.2.0.4

 

create table t6 (i int) ;

SQL>  select value from v$mystat m, v$statname s

  2   where m.statistic#=s.statistic# and s.name = 'redo size';

 

     VALUE

----------

     14224

 

SQL> insert into t6 select obj# from sys.obj$ where rownum <= 10000;

 

10000 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select value from v$mystat m, v$statname s

2        where m.statistic#=s.statistic# and s.name = 'redo size';

3             VALUE

4        ----------

5           1625216

 

Amount of redo used 160744

On Oracle 7 :

JVU_2>create table t6 (i int) ;

 

Table created.

 

JVU_2>select value from v$mystat m, v$statname s

  2  where m.statistic#=s.statistic# and s.name = 'redo size';

 

     VALUE

----------

      7510

 

JVU_2>desc v$mystat

 Name                            Null?    Type

 ------------------------------- -------- ----

 SID                                      NUMBER

 STATISTIC#                               NUMBER

 VALUE                                    NUMBER

 

JVU_2> insert into t6 select obj# from sys.obj$ where rownum <= 10000;

 

6319 rows created.

 

JVU_2>commit;

 

Commit complete.

 

JVU_2>select value from v$mystat m, v$statname s

  2  where m.statistic#=s.statistic# and s.name = 'redo size';

 

     VALUE

----------

    101438

 

Amount of redo used in oracle 7.3.4: 93928

When dividing this: 160744 / 93928 = 1.7

Leads me to an increase of 70%

 

Is this regular behaviour for an oracle 9 environment??

 

I saw in post in this list mentioned bug 2874489 for use on global

temporary tables, these cannot be the case here because all code is still strictly

on oracle 7 base.

 

Details: oracle 9.2.0.4 on HPUX11.11

 

Regards,

 

Jeroen

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 30 2003 - 09:39:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US