Re: Massive Archive Logging

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 Fri Jun 18 1999 - 02:53:21 CDT