A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation . It is the Oracle control file(s) that records information about the consistency of a database's physical structures and operational statuses . The database state changes through activities such as adding data files, altering the size or location of datafiles, redo being generated, archive logs being created, backups being taken, SCN numbers changing, or checkpoints being taken.
Why Control File Waits Occur ?
Control File Waits Occur due to the following reason .
1.) This wait occurs when a server process is updating all copies of the controlfile i.e, the session is writing physical blocks to all control files at same time .
2.) The session commits a transaction to a controlfile
3.) Changing a generic entry in the controlfile, the new value is being written to all controlfiles
4.) Controlfile resides on such a disk which is heavily used i.e, facing lots of i/o's .
We can check the wait experience by a session using v$session_wait views as
SQL>select event, wait_time, p1, p2, p3 frpm v$session_wait wher event like '%control%';
Here wait_time is the elapsed time for reads or writes.
Possible steps to reduce this wait :
1.) Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.
2.) Move the controlfile copies to less saturated storage locations.
3.) Reduce the frequent log switches . To find the optimal time and size for log switch check this post .
As far my experience , Control file access is governed by activities such as redo logfile switching and checkpointing . Therefore it can only be influenced indirectly by tuning . It rarely occurs in my report and it get solved automatically when i check the other wait metric especially "log sync wait" and others waits .
Enjoy :-)
