Re: Frequency of log switches

From: Kurt-Erich Finger <kurt-erich.finger_at_hte-company.de>
Date: 10 Oct 2003 00:27:31 -0700
Message-ID: <5208be2f.0310092327.449dab5a@posting.google.com>

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 Received on Fri Oct 10 2003 - 02:27:31 CDT