log buffer problem. please help

[helenzhang]

log buffer problem, please help.
oracle8.1.7
aix4.3
log_checkpoint_interval = 300000
log_archive_max_processes = 2
log_buffer 1M
redo log file: 20M


this is problem about the redo log. the switch log frequency is 1 mn, but normaly is 30 mn. Follwing is the result from the views. oracle is restart yesterday night.


thanks.

select name, value
from v$sysstat
where name='redo log space requests';

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 93



select event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch completion%'

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file switch completion 93
6339 68.1612903


select sid, event, seconds_in_wait, state
from v$session_wait
where event ='log buffer space%';

no rows selected

[ales]

Not very experienced in PT, but if earlier log switch occured twice an hour and now it's switching every minute, I would try to find a nasty job or session which generates most redo.
I'm assuming you did not change any parameters recently ...

To find a session with biggest redo you can use this:

select s.sid, n.name, s.value
from V$SESSTAT s, V$STATNAME n
where s.STATISTIC# = n.STATISTIC#
and n.name = ('redo size')
order by 3

and then see v$session and v$sql to find the culprit.

It helped me sometimes, I hope it helps you as well.

Ales

[helenzhang]

thanks,Ales

I check again, the log switch is also less than 1 mn.