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

[Tanel Poder]

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