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

Jeroen,

In your test, your obj$ table in 9i inserted 10,000 rows while your obj$ table in v7 only had 6319 rows. Test with another table or change your rownum filter.

Mike

Jeroen van Sluisdam wrote:
>
>
>
>
> 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: Mike Spalinger
  INET: Michael.Spalinger_at_Sun.COM

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).