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