Re: Frequency of log switches

From: Kurt-Erich Finger <kurt-erich.finger_at_hte-company.de>
Date: 13 Oct 2003 01:01:25 -0700
Message-ID: <5208be2f.0310122309.1fde23f9@posting.google.com>

kurt-erich.finger_at_hte-company.de (Kurt-Erich Finger) wrote in message

..
..
..


> > 

> > 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
>
> Howard,
>
> 1. the temporary tablespace is CONTENTS=TEMPORARY and LOGGING
> I will fix this.
>
> 2. In one schema I have 6 jobs that refresh views and materialized views.
> I found that the frequency of log switches decreases when I increase (delete)
> some jobs. With no jobs I see no log switches (the instance is idle)
>
> Have I overlooked something in the manuals concerning archived redo logs
> and active jobs?
>
> Regards
>
> Kurt-Erich

Hi,
the bad guys were two jobs that refrehed materialize views. The manual Server Concepts says:
....LOGGING is the default, except when the database operates in NOARCHIVELOG mode in which case NOLOGGING is the default. DDL and utility statements that do not support the LOGGING/NOLOGGING clause always run in recoverable mode (LOGGING).'

When added the NOLOGGING clause to the Create Materialized View statement the frequency of log switsches was one per hour instead of 3 per hour. However, since no (user) data is modified in this instance is there a chance to reduce the log switches further?

When I look into v$logmnr_contents I find a lot of

update SYS.JOB$ set LAST_DATE = TO_DATE('....
update SYS.JOB$ set NEXT_DATE = TO_DATE('....
update SYS.OBJ$ set OBJ# = 45266, "DATAOBJ#" = ...
update SYS.SNAP$ set SNAPSHOT = TO_DATE('...
update SYS.SNAP_REFTIME$ set SNAPTIME = TO_DATE(..

update UNKNOWN.Objn:57 set Col[2] = ..
objn 57 is TSQ$

The views are refreshed using dbms_refresh.refresh. I found no 'NOLOGGING' parameter.

Is it possible to switch logging off for jobs?

cheers

Kurt-Erich Received on Mon Oct 13 2003 - 03:01:25 CDT