control file parallel write

The control file parallel write wait event has three parameters: files, blocks, and requests . In Oracle Database 10 g , this wait event falls under the System I/O wait class. Keep the following key thought in mind when dealing with the control file parallel write wait event.

The control file parallel write waits usually are symptomatic of high log switches.

Common Causes, Diagnosis, and Actions

A session with the control file parallel write wait event indicates that it has performed control file transactions. Operations such as log switching and adding or removing data files require the control file to be updated. Control file writes are also performed for some LOB operations.

Both the foreground and background processes can write to the control file. Every three seconds the CKPT background process updates the control files with the checkpoint position that is in the online redo logs. In normal circumstances, the CKPT process should have the highest time waited on the control file parallel write event. The ARCH background process updates the control file with information related to archive logs, and the LGWR background process updates the control file each time a log switch occurs. The following query reveals the sessions that performed control file transactions:

 select /*+ ordered */ 
 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; 
 
 SID TYPE              TIME_WAITED AVERAGE_WAIT HOURS_CONNECTED 
 --- ----------------- ----------- ------------ --------------- 
 10 BACKGROUND-(ARC0)         525        .3431             117 
 11 BACKGROUND-(ARC1)         519        .3390             117 
 7 BACKGROUND-(CKPT)       64147        .3431             117 
 6 BACKGROUND-(LGWR)        1832        .3011             117 
 517 FOREGROUND                  2        .5120               1

If the LGWR process shows a high TIME_WAITED on this event, it means there are too many log switches, and you should check the redo log size by querying the V$LOG view. The logs may be too small for the amount of transactions that come in to the database. Check how often the logs are switching with the following query:

 select thread#, 
 to_char(first_time,'DD-MON-YYYY') creation_date, 
 to_char(first_time,'HH24:MI')     time, 
 sequence#, 
 first_change# lowest_SCN_in_log, 
 next_change#  highest_SCN_in_log, 
 recid         controlfile_record_id, 
 stamp         controlfile_record_stamp 
 from   v$log_history 
 order by first_time;

If a foreground process shows a high TIME_WAITED on the control file parallel write event, check to see if the application is making changes to NOLOGGING LOBs. When a data file is changed by a NOLOGGING operation, its unrecoverable SCN that is in the control file must be updated for RMAN purpose. If the time waited is high, you may consider turning off updates to the control file with event 10359. The following excerpt is from the $ORACLE_HOME/rdbms/mesg/ oraus.msg :

 10359, 00000, "turn off updates to control file for direct writes" 
 // *Cause: 
 // *Action:  Control files won't get updated for direct writes for LOBs 
 //           when NOCACHE NOLOGGING is set. The only bad impact that it 
 //           can have is that if you are using the recovery manager, 
 //           it may affect a warning that says that the user should 
 //           back everything up. Now the recovery manager won't know 
 //           to tell you that the files that were updated with 
 //           unrecoverable events should be backed up.