Re: Massive Archive Logging

Could it have something to do with the changed rowid format?

As far as I know Oracle converts the rowids from the version 7 format to the version 8 format.

Jonathan Lewis wrote:

> Interestingly the last person with this
> problem was also running Peoplesoft.
>
> Increasing the log file size would, as
> you point out, reduce wait times, because
> checkpoints due to log switches occur
> less frequently,
>
> Because the increase is redo is so
> huge, (and nothing has changed to
> cause extra data change to occur)
> I am fairly convinced that the problem
> must be that some activity that used
> to run in UNRECOVERABLE mode
> is now generaing redo. (e.g. index
> creation on very large tables).
>
> In the absence of any inspiration at this
> point, I would examine the log switch times
> (v$log_history or alert.log) and try to identify
> the time of day when redo generation peaked:
>
> Narrow this down perhaps by
> select name,value from v$sysstat
> where name like 'redo%';
> every 30 minutes.
>
> You might also consider switching
> auditing on, and issuing AUDIT SESSION,
> then run a report daily to see which userid
> was doing most work (view dba_audit_session)
>
> This may give you clues to follow up.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Mitchell Loren wrote in message <37697519.7CDC4C34_at_home.com>...
> >I am running a PeopleSoft financials application and no one but
> myself
> >can create tables, indexes or do SQL loads. I have not done any of
> >these in production. Since its a packaged application and I have not
> >added any users, the transaction levels are basiclly the same.
> >
> >Redo wastage is 11%. Same as it was on the old box.
> >
> >Increasing the redo log size did away with a good amount of waits.
> >
> >Any other thoughts. I still think there is a bug in 7.3.4 for IBM
> Sp2.
> >
> >Jonathan Lewis wrote:
> >>
> >> Some one else recently complained about the
> >> amount of extra redo generated after changing
> >> Oracle versions (in that case 7 to 8 I think).
> >>
> >> Changing the size of the online redo log will
> >> not affect the amount of archive generated -
> >> the archive is just a copy of the online, and
> >> it doesn't matter whether you copy it 1M
> >> at a time or 100M at a time - the total copied
> >> will be the same (the performance of the database
> >> may be affected by checkpoints of course).
> >>
> >> Possibilities -
> >>
> >> Are you absolutely sure that the work done is
> >> still the same ? Are any users suddenly
> >> creating large table and indexes from time
> >> to time ?
> >>
> >> Do you have any code to 'create table unrecoverable'
> >> or 'create index unrecoverable' that is suddenly not
> >> working unrecoverably ?
> >>
> >> Do you have any SQL*Load in direct mode which
> >> are suddenly not loading unrecoverably, but are
> >> loading with redo generation.
> >>
> >> Do you have any SQL*Load which are supposed to
> >> load with TRUNCATE, but now appear to be loading
> >> with DELETE ?
> >>
> >> Check the redo wastage (v$sysstat) - is most of the
> >> increase redo wastage, or is it really redo ? Do you
> >> have stats pre-upgrade.
> >>
> >> --
> >>
> >> Jonathan Lewis
> >> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
> >>
> >> Mitchell Loren wrote in message <376841FB.A96E3110_at_home.com>...
> >> >I recently moved my db from 7.3.2 (AIX 4.1:RS6000) to 7.3.4 (AIX
> 4.3:IBM
> >> >SP2). Before I moved the db I had about 6 GIG of archive logs
> produced
> >> >in a 24 hour period ( 5 groups of redo logs, 2 members each, 5M
> each).
> >> >After I moved to 7.3.4 archiving went up to 18 GIG per 24 hours.
> The
> >> >amount of transactions stayed the same.
> >> >
> >> >To remedy the situation I added 2 more groups and resized the redo
> logs
> >> >to 30M each. I also set the log_checkpoint_interval > redo log
> size.
> >> >Unfortunately, this did not help. Actually the total amount of
> archive
> >> >logs went up to over 25 GIG in a 24 hour period.
> >> >
> >> >Has anyone experienced this type of massive archiving. I don't
> think
> >> >further increasing the redo size will help. Rather it is some
> process
> >> >of 7.3.4 which functions differently than 7.3.2. Any suggestions
> ?
Received on Mon Jun 21 1999 - 02:29:32 CDT