Control File Parallel Write Wait Event
Usually, you will find that high wait times on the control file parallel write wait event will be indicative of high log switching. The query below will show you which session performed control file transactions.
select a.sid, decode(a.type, ‘BACKGROUND’, ‘BACKGROUND-‘||substr(a.program, instr(a.program,'(‘,1,1)), ‘FOREGROUND’) type, b.time_waited, round(b.time_waited/b.total_waits,4) average_wait, round((sysdate – a.logon_time)*24) hours_connected from v$session_event b, v$session a where a.sid = b.sid and b.event = ‘control file parallel write’ order by type, time_waited;
Normally, the checkpoint process should have the highest wait time on the control file, as it will write every three seconds.
If the LGWR process comes up as the one with lots of transactions against the control file, it is most likely that your online redo log files are too small. Check the size of them with the following query:
SELECT bytes/1024/1024 MB, first_time FROM v$log ORDER BY first_time;
You can then increase the size of the online redo logs so that log switching is not so high. Depending upon the usage of your database, log switching anywhere between 10-20 minutes should be fine.
You can also read my article on seeing CPU time in the top 5 Timed Events.
Like it, share it...
Category: Database Monitoring