Re: Frequency of log switches

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 10 Oct 2003 18:02:32 +1000
Message-ID: <3f866825$0$29372$afc38c87@news.optusnet.com.au>

Kurt-Erich Finger wrote:

> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
> news:<3F853A74.5099_at_yahoo.com>...

>> Kurt-Erich Finger wrote:
>> > 
>> > Oracle 8.1.7 on Linux 2.4
>> > 
>> > Hello,
>> > 
>> > I am trying to implement a back-up/recovery procedure in my company.
>> > To test it, I took a copy of the production database, added
>> > 
>> > log_checkpoint_interval = 10000
>> > log_archive_dest=/data/arc
>> > log_archive_format='arc%s.log'
>> > log_archive_start=True
>> > 
>> > to the init-file and started the instance with
>> > ALTER DATABASE ARCHIVELOG
>> > 
>> > The archived logs are generated, however, at a high frequency.
>> > Before I switched on archiving there were only some log switches
>> > per day. Switching on archivelog I got new logs (and archived logs)
>> > every minute when the the size of the log-files was 10mb and log
>> > switches every 20 minutes when the size of the log-files was 50mb.
>> > The frequencies seem to be very high taking into account that there is
>> > no (user) activity on the instance.
>> > 
>> > How can I reduce the log switches to reasonable numbers?
>> > (increasing log_checkpoint_interval did not help)
>> > 
>> > Cheers,
>> > Kurt-Erich
>> 
>> Take a look at v$sesstat joined to v$statname, and you get how much redo
>> each session is generating
>> 
>> hth
>> connor

>
> hello,
>
> 1. select * from v$backup tells me "Not Active" for all tablespaces.
> The database is not in hot backup mode.
> (If I do ALTER TABLESPACE BEGIN BACKUP I see "Active" for that
> tablespace)
>
> 2. select v$sesstat.sid,v$sesstat.value,v$statname.name
> from v$sesstat,v$statname
> where v$sesstat.statistic#=v$statname.statistic#
>
> =>
> SID VALUE NAME
> 3 30714744 redo wastage
> 3 6146710 redo blocks written
> 11 5358628 session pga memory
> 11 5358628 session pga memory max
> 25 5180596 session pga memory
> 25 5180596 session pga memory max
> 14 5154688 session pga memory
> 14 5154688 session pga memory max
> 21 5138084 session pga memory
> 21 5138084 session pga memory max
> 12 4900076 session pga memory
> 12 4900076 session pga memory max
> 28 4880948 session pga memory
> etc
> I get 4000 records with 200 having a value larger than 10
>
>
> After archive log is switched off I also get 4000 records
> with 190 having a value larger than 10
>
> SID VALUE NAME
> 15 3945964 session pga memory max
> 15 3159360 session pga memory
> 14 3122232 session pga memory
> 14 3122232 session pga memory max
> 2 619068 session pga memory
> 2 619068 session pga memory max
> 22 579404 session pga memory
> 22 579404 session pga memory max
> 22 447392 session uga memory max
> 17 267944 session pga memory max
> 17 253624 session pga memory
> 3 231552 session pga memory
> 3 231552 session pga memory max
> 9 217628 session pga memory
>
>
> Any further ideas?
>
> best regards
>
> Kurt-Erich

Just a wild stab in the dark here... but is your temporary tablespace actually temporary? And nologging?

Select * from dba_tablespaces and have a look.

If you have a brand-new tempfile style of temporary tablespace, that's created as CONTENTS=TEMPORARY and NOLOGGING. But if you have an old-fashioned datafile-style temporary tablespace, then it might have CONTENTS=PERMANENT (yuk) and/or LOGGING enabled.

If so, alter tablespace temp temporary to fix the first issue. And alter tablespace temp nologging to fix the second. And then see how your log switches go.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Fri Oct 10 2003 - 03:02:32 CDT