Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!newsfeed.stueberl.de!feed.news.nacamar.de!uninett.no!newsfeed1.eu.ignite.net!newsfeed.pacific.net.au!spool01.syd.optusnet.com.au!spool.optusnet.com.au!not-for-mail
Message-ID: <3f866825$0$29372$afc38c87@news.optusnet.com.au>
From: "Howard J. Rogers" <hjr@dizwell.com>
Subject: Re: Frequency of log switches
Newsgroups: comp.databases.oracle.server
Date: Fri, 10 Oct 2003 18:02:32 +1000
References: <5208be2f.0310072327.14f5c0d9@posting.google.com> <3F853A74.5099@yahoo.com> <5208be2f.0310092327.449dab5a@posting.google.com>
Organization: Dizwell Informatics
User-Agent: KNode/0.7.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
Lines: 120
NNTP-Posting-Host: 203.164.4.156
X-Trace: 1065773094  29372 203.164.4.156
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:244977

Kurt-Erich Finger wrote:

> Connor McDonald <connor_mcdonald@yahoo.com> wrote in message
> news:<3F853A74.5099@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!!
--------------------------------------------