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

Ouch stupid mistake (time to take a few days off)

Redo the exercise with the correct number of rows lead me to an increase of about 9 percent

which looks reasonable with your arguments in consideration as well.

 

Thanks,

 

Jeroen

 

 

-----Oorspronkelijk bericht-----
Van: Tanel Poder [mailto:tanel.poder.003@mail.ee]
Verzonden: Tuesday, December 30, 2003 16:39
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: increase in amount of redo comparing oracle 7 and 9

 

----- 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: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

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