Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: RE: RE: controlfile schema global enqueue lock

Re:RE: RE: RE: controlfile schema global enqueue lock

From: <>
Date: Thu, 01 Feb 2001 10:08:37 -0800
Message-ID: <>


    What do I suggest? Well for one I have no idea is async io is enabled or not on our HP's. When I last discussed it with my SA he was not too encouraging & after a pile of reading, neither was I. ASYNC IO only allows DBWR to pass a block or set thereof to the OS and move on. Yeah it speeds up that process, but you've still got one process competing for CPU and IO time along with everyone else. DBWR_IO_SLAVES kind of does the same thing, except you've got more than one process so collectively you get more CPU attention & IO time mainly because each slave will go after a different disk drive. Now I did not ask before, but what are you using for a disk farm? HP, EMC, and raw devices are suppose to be a bad mix according to EMC & HP, which is what we're using. Better here is to fill out the cache buffer in the EMC.

    Now, will increasing the size of the redo logs cut down on checkpoints? Answer, maybe. When a checkpoint occurs is controlled by several things, log switches, log_checkpoint_interval, and log_checkpoint_timeout. The "ideal" is to have redo log sizes that are the same as log_checkpoint_interval so the interval does not trigger a checkpoint between log switches. Also log_checkpoint_timeout should be long enough that it too does not trigger a checkpoint between log switches, in your case a 0 should be just fine.

    Therefore in your case:

  1. set dbwr_io_slaves = 4
  2. change log_checkpoint_interval to 512000
  3. set lgwr_io_slaves = 2

and see what happens.

Dick Goulet

____________________Reply Separator____________________
Author: "Joan Hsieh" <>
Date:       2/1/2001 12:29 PM

Thanks, so what you suggest? That's why the other day I asked the list about the asyn i/o on os. If asyn is enabled, is it ok to set dbwr_io_slaves? I am not sure for that part. For checkpoint, You see, before I made the change, we have 8 log switch per hr plus every 30 min and every 3200 block (raw disk is 512b /block) do the checkpoint. I think that's too much. I forgot to set log_checkpoint_to_alert to true. Right now, after the change. the checkpoint just occur at log switch time. I am wondering maybe we still hold the lock too long to finished the checkpoint. Should I increased the redo log size to 500m? What's the impact if we have very large size redo log. I remember Steve said 500m is a good number.(please correct me if I am wrong) The principle dba just won't buy it. he think it might cause other problems.

Thanks again


-----Original Message-----
Sent: Thursday, February 01, 2001 11:44 AM To: Joan Hsieh; Multiple recipients of list ORACLE-L


    I doubt this will be the "silver bullet", but it did reduce this particular
problem on our Operational Data Store where testers are constantly writing data.
 Set DBWR_IO_SLAVES = number_of_drives_with_datafiles/2. This ups the number of
processes that are busy writing data to the datafiles thereby reducing the amount of time it takes to complete a checkpoint & thereby releasing the lock
sooner. You may also want to set LGWR_IO_SLAVES in a similar manner.

    Your basic problem is that there is one and only one enqueue lock for the
control files. Let's face it we really don't want two processes trying to change data therein at the same time. What a mess that would be. And when a
checkpoint does occur we need to update the datafiles and control files as a set. This is where the enqueue lock comes into play. If you can speed up the
time it takes to flush out all of the dirty blocks from the sga & log_buffers to
the redo log files then the checkpoint gets completed faster. Right now it looks like you've got a checkpoint happening only at log switches since log_checkpoint_interval is set larger than your logfile size.

Dick Goulet


Please see the official ORACLE-L FAQ:


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 01 2001 - 12:08:37 CST

Original text of this message