Фоновый процесс: DBWR

dbstalker, 15 мая

Database Writer (DBWR) - основные задачи, которые этот процесс решает:

  • Выполняет запись буферов в файлы данных.
  • Отвечает за управление буферным кешем (ведение LRU списка).
  • Обновляет заголовки файлов данных информацией о контрольной точке (до 8.0 версии).

Управление буферным кэшем.

Модифицированный буфер в буферном кэше помечается как «грязный». DBWR пытается поддерживать буферный кеш в "чистоте", записывая грязные буфера на диск. Так как буфера постоянно заполняются и модифицируются пользовательскими процессами, то количество свободных буферов все время уменьшается. Может случиться, так что число свободных буферов станет настолько мало, что пользовательский процесс, который должен прочитать блок с диска и записать его в кеш, не сможет найти свободный буфер. DBWR управляет буферным кешем так, чтобы пользовательские процессы всегда смогли найти свободные буфера. О том, как это ораклом реализовано, можно прочитать в статье «Немного об LRU списке» . В дополнение к информации, изложенной на приведенной ссылке, хочу сделать ударение на следующем моменте: Dirty-список очень важен еще и потому, что DBWR с его помощью, может оптимизировать процесс сброса данных на диск, записывая смежные блоки за одну операцию.

Запись буферов на диск.

DBWR записывает содержимое буферов на диск по определенным событиям. Об этом почитайте в статье «Как с буферным кэшем и между собой взаимодействуют серверный процесс пользователя и DBWn?» . А так как невозможно работу DBWR рассматривать изолировано от LGWR, то еще можно ознакомиться с постом ”Как же формируются данные для журналов повторного выполнения?”. Хочется обратить внимание на двух маленьких, но существенных деталях:

  • в файлы данных могут записываться изменения незафиксированных транзакций
  • изменения, выполненные зафиксированными транзакциями, не обязательно сразу же будут записаны в файлы данных

Производительность

Производительность процесса DBWR имеет принципиальное значение для повышения производительности всей системы. В первую очередь это связано с вопросами взаимодействия с процессом LGWR. Подробнее в посте «Сообщение в alert.log: Checkpoint not complete»

Экземпляр может иметь несколько процессов DBWR. Можно сконфигурировать несколько (до десяти) процессов DBWn (DBW0 ... DBW9). В большинстве систем работает только один процесс записи блоков базы данных, но в больших, многопроцессорных системах имеет смысл использовать несколько. Если сконфигурировано более одного процесса DBWn, не забудьте также увеличить значение параметра инициализации DB_BLOCK_LRU_LATCHES(начиная с 9 параметр не используется). Он определяет количество защелок списков по давности использования LRU lists (в версиях выше 8i называются списками количества обращений — touch lists). Каждый процесс DBWn должен иметь собственный список. Если несколько процессов DBWn совместно используют один список блоков для записи на диск, они будут конфликтовать друг с другом при доступе к списку.

Обычно процесс DBWn использует асинхронный ввод-вывод для записи блоков на диск. При использовании асинхронного ввода-вывода процесс DBWn собирает пакет блоков для записи и передает его операционной системе. Процесс DBWn не ждет, пока ОС запишет блоки, — он собирает следующий пакет для записи. Завершив асинхронную запись, ОС уведомляет об этом процесс DBWn. Это позволяет процессу DBWn работать намного быстрее, чем при последовательном выполнении действий.

Литература

http://www.firststeps.ru/sql/oracle/r.php?79

http://doc.novsu.ac.ru/oracle/conceps/7013scm.09.html

http://wbt.neo.rr.com/dpec/courses/or3101/or3101ac.htm

http://www.dbspecialists.com

А теперь для тех, кому скучно читать вышеизложенную бредятину, статья настоящего профессионала в нашем безнадежном деле:

The Mysteries of DBWR Tuning
Steve Adams

Independent Oracle Consultant
 


 
Introduction
The Oracle Database Writer (DBWR) process is the only process that writes modified database blocks from the SGA to the datafiles. Today's high-end applications modify blocks in the SGA at amazing rates, and DBWR needs to be carefully tuned to keep up — otherwise it will impose a severe bottleneck on overall system performance.
However, DBWR tuning is a mysterious art. The Oracle Server Tuning Guide and the independent Oracle tuning books offer scant advice on how to tune this critical aspect of database operation. Hence, to most senior DBAs, DBWR bottlenecks are an intractable problem. Beyond ensuring that asynchronous I/O is available, and that there are no hot disks, they are have no ways of addressing the problem.
This paper solves the problem. It explains, in detail, how DBWR works and interacts with other database processes. It shows how to monitor DBWR performance using the v$ views, and how to take an even deeper look at some seldom used x$ tables to get a complete understanding of DBWR’s behaviour. More than that, this paper explains how to then set the parameters that affect DBWR performance, and exactly when and why it might sometimes be necessary to use some of the “undocumented” parameters.
What’s the Problem?
If a database instance has a system performance problem then it will be manifested somewhere in the V$SYSTEM_EVENT view. This view details how often, and for how long, database processes have had to wait for system resources. If DBWR is under performing, then V$SYSTEM_EVENT will show a large number of free buffer waits as illustrated in Figure 1. Free buffer waits are evidence of a DBWR problem, because it is DBWR’s responsibility to make free buffers available by writing modified blocks to the datafiles.
select
  event,
  total_waits,
  time_waited,
  average_wait
from
  sys.v_$system_event
where
  event like 'db file %' or
  event = 'free buffer waits' or
  event = 'write complete waits'
order by
  time_waited desc
/

                         TOTAL    TIME   AVG
EVENT                    WAITS  WAITED  WAIT
----------------------- ------ ------- -----
free buffer waits       194278 4488038 23.10
db file sequential read 588805 2900049  4.93
db file parallel write   34667  119035  3.43
db file scattered read   19283   10242  0.53
write complete waits       175    5481 31.32
db file single write       378    1261  3.34
Figure 1 - A DBWR problem, as seen from V$SYSTEM_EVENT
The first thing to do when faced with a DBWR bottleneck is to ensure that the maximum possible write bandwidth is available to DBWR. Firstly, the operating system and database instance must be configured to make non-blocking I/O (asynchronous I/O) available to DBWR. Secondly, the datafiles must be spread over enough disks, controllers and I/O busses to ensure that there are no hot spots in the I/O subsystem. This I/O tuning typically involves the use of striping, and commonly involves the use of raw datafiles also.
Figure 2 shows the dramatic improvement made to the performance of the system depicted in figure 1 merely by converting the datafiles to raw, and enabling asynchronous I/O. No alteration to the datafile striping or layout was necessary to achieve this improvement, nor was there any evidence of hot spots in the I/O subsystem following this change. Indeed, the average random read time of 13 milli seconds shown in the db file sequential read line is quite good.
                         TOTAL    TIME   AVG
EVENT                    WAITS  WAITED  WAIT
----------------------- ------ ------- -----
free buffer waits       149536 1207806  8.08
db file sequential read 793378 1033994  1.30
db file parallel write   62241   64377  1.03
db file scattered read   24194   55562  2.30
db file single write       542    1517  2.80
write complete waits       152    1308  8.59
Figure 2 - The same DBWR problem, ameliorated by I/O tuning
There is much more that can, should and has been said about these important aspects of DBWR tuning. Yet, important and primary as these things are, they fall outside the scope of this paper. What this paper is concerned with is what to do next — what to do when these well known tuning opportunities have been fully exploited, and yet the problem persists.
In figure 2, for example, although the I/O performance is now good, this instance is still spending a great deal of time waiting for free buffers. This is typical of the high end Oracle database applications that I have seen — I/O tuning is critical, but not enough by itself to eliminate free buffer waits. Further specific DBWR tuning is necessary. But such tuning needs to be directed by a clear understanding of how DBWR works, and how its performance can be analysed and then tuned. It is the objective of this paper to build that understanding.
How Bad is it?
Before attempting to resolve a performance problem like free buffer waits, it is vital to be able to measure the relative severity of the problem, so that the effectiveness of each tuning attempt can be assessed. There are two simple measures of the relative severity of system resource waits like free buffer waits — the first is how often the wait has occurred as a proportion of the number of times that the resource was required; and the second is how long the wait lasted when it did occur. 
The number of times that free buffers were required is reported in the free buffer requests statistic in V$SYSSTAT. In the cases above, there were 1096334 and 1548954 free buffer requests respectively. So, taking the number of free buffer waits from V$SYSTEM_EVENT, it can be seen that the ratio of free buffer waits to free buffer requests was improved from about 1:5 to about 1:10. The average duration of free buffer waits was also improved from about 23 to about 8 hundredths of a second, as can be seen directly in V$SYSTEM_EVENT.
Tuning the Load on DBWR
If you still have free buffer waits after tuning the I/O subsystem, you should check DBWR’s workload, to see whether it can be reduced. There are two major opportunities for reducing the load on DBWR.
Firstly, Oracle versions prior to 7.3, did not have the delayed logging block cleanouts feature. If this feature is disabled, or if you are using an older version of Oracle, then delayed block cleanouts result in the cleaned out blocks being marked as dirty, and this can increase the load on DBWR significantly. From version 7.1.5 onwards, this can be avoided by using the parallel query option to force blocks to be read into direct read buffers, rather than into the database buffer cache. In this case, cleaned out blocks are not written unless copied into the buffer cache for another block change. Alternatively, cleanouts can be forced and written during off peak periods by forcing buffered full table scans.
Secondly, free buffer waits may also stem from high demand for free buffers. There are many reasons for free buffer requests, but normally the majority of free buffer requests are associated with reading blocks from disk into the database buffer cache. This demand for free buffers is highly tuneable, as is well known. For example, you can use the parallel query option to force direct reads and hence reduce demand for cache buffers, or you can enlarge the cache to improve the hit rate, or you can concentrate on optimising segment data density to reduce the number of blocks being read, or in version 8 you can use multiple buffer pools to control the placement and retention of blocks in the cache.
Tuning the LGWR / DBWR Interaction
The next thing to check is the interaction between LGWR and DBWR. The concern is that DBWR may be spending a lot of time waiting for LGWR, instead of cleaning dirty buffers. DBWR must sometimes wait for LGWR, so as to ensure that data blocks are not written to disk before the corresponding redo has been written. Otherwise it might not be possible to rollback uncommitted changes during instance recovery.
So, before DBWR writes a block, it checks the buffer header data structure to see whether there is any associated redo that still needs to be written. If so, it sends a message to LGWR requesting that it write the redo to disk. DBWR does this by allocating a message buffer in the shared pool and constructing a message for LGWR. It then posts LGWR’s semaphore which acts as a signal to the operating system that there is now work for LGWR to do, and that it should be scheduled to run on a CPU as soon as possible. Meanwhile, DBWR enters a log file sync wait and waits on its semaphore. This acts as a signal to the operating system to not schedule DBWR to run on a CPU until further notice, or until the wait times out. When LGWR has done its work, it posts DBWR’s semaphore to indicate to the operating system that DBWR can now be scheduled to run.
How often, and for how long, DBWR has had to wait for LGWR in this way, can be seen in V$SESSION_EVENT as illustrated in figure 3. When tuning LGWR to reduce the DBWR waiting time, the number of DBWR log file sync waits should be expressed as a proportion of the write requests shown in V$SYSSTAT. However, when tuning DBWR itself, the number of DBWR log file sync waits should be expressed as a proportion of the db block changes shown in V$SYSSTAT.
select
  event,
  total_waits,
  time_waited,
  average_wait
from
  sys.v_$session_event
where
  event = 'log file sync' and
  sid = 2
/

                         TOTAL    TIME   AVG
EVENT                    WAITS  WAITED  WAIT
----------------------- ------ ------- -----
log file sync             6164    9165  1.49
Figure 3 - DBWR waiting for LGWR
There are two cases when DBWR may have to write blocks for which the corresponding redo has not yet been written to disk, and therefore have to wait for LGWR. The first is when the unwritten redo relates to a block change that DBWR itself has just performed. As a DBA, you have relatively little control over DBWR block changes, although careful application design can help. The second case is when the redo has not yet been written because the log buffer is large enough to accommodate all the redo generated since the change was made, and there has been no commit or other reason for the redo to be flushed to disk. In this case, the need for DBWR to wait for LGWR can be significantly reduced by keeping LGWR active.
The best way to keep LGWR active is to have a small log buffer. LGWR will start to write asynchronously each time the log buffer becomes one third full. This will reduce the probability that DBWR will need to wait for LGWR. It will also reduce the waiting time when necessary, because there will be relatively little redo to flush.
However, some applications cannot afford to have a small log buffer, because they generate redo in large bursts, and would suffer log buffer space waits if the log buffer were too small. In such cases, you can  have a large log buffer and still keep LGWR active using the _LOG_IO_SIZE parameter. This parameter sets the threshold at which LGWR starts to write, which defaults to one third of the log buffer. If set explicitly, it must be specified in log blocks, the size of which can be found in X$KCCLE.LEBSZ. It is best to keep _LOG_IO_SIZE at least one log block less than the maximum I/O size for your operating system, which for Unix operating systems is typically 64K. Then when DBWR does have to wait for LGWR to flush the log buffer, it will always be able to be accomplished in just a single I/O operation.
Of course, you should not set any parameters beginning with an underscore, like _LOG_IO_SIZE, without prior consultation with Oracle Support.
The second aspect of the DBWR/LGWR interaction to tune is their inter process communication. Despite warnings to the contrary, it does help to raise the operating system priority of both the LGWR and DBWR processes. Also, many operating systems support two types of semaphores. The older type, known as System V semaphores, are implemented in the operating system kernel and are thus costly to use. The newer Posix 1b semaphores, or post wait semaphores, are much more efficient because they are implemented in shared user memory. If the Oracle port for your operating system gives you a choice on this matter, you should choose the post wait semaphores for best performance. This affects all inter process communication within Oracle, but is particularly important for LGWR and DBWR, because at times these processes need to post many other processes.

Tuning the Write Batch Size Limit
We now come to the biggest hammer with which you can hit free buffer waits, namely the DBWR batch size limit. When DBWR writes dirty blocks to disk, it works in batches. Each time its services are required, DBWR compiles a batch of blocks, and then writes them. The size of these batches varies, up to a limit, depending on how many dirty blocks are available for writing at the time.
If asynchronous I/O is being used, an asynchronous write system call is issued to the operating system for each block in the batch. It is then left to the operating system and hardware to service this set of requests as efficiently as possible. DBWR monitors the progress of the requests and checks their status as they complete. If necessary, DBWR will attempt to re write blocks for which the first write attempt appears to have failed. On the other hand, if multiple slave DBWR processes are used, as is possible on the Oracle ports for most Unix operating systems, the master DBWR does not write any blocks itself. It allocates one block from its batch to each slave DBWR in turn. Each slave DBWR process then issues an ordinary synchronous write system call for its block, and waits until the disk operation has successfully completed before getting a new block to write from the master DBWR process.
This explains why using true asynchronous I/O is preferable to using multiple slave DBWR processes. Apart from the significant overhead of inter process communication between the master and slave DBWR processes for each block written, the master/slaves configuration also clearly reduces the degree of I/O parallelism that can be achieved at the operating system and hardware level.
This also explains why the DBWR batch size limit, or the number of slave DBWR processes respectively, can have a significant impact on DBWR performance. For example, if this write bandwidth is small, then I/O operations will have to be done in series that might otherwise have been done in parallel. Even in the worst case, when all the dirty blocks need to be written to the same disk, there is still benefit in queuing a batch of writes concurrently, because the operating system and hardware will be able to service the requests in the optimal order.
The DBWR batch size limit is controlled by the _DB_BLOCK_WRITE_BATCH parameter. The value of this parameter is derived from other parameters, and can be seen in X$KVII as illustrated in figure 4. The value normally defaults to DB_FILES * DB_FILE_SIMULTANEOUS_WRITES / 2 up to a limit of one quarter of the cache (DB_BLOCK_BUFFERS / 4) or 512 blocks, whichever is the lesser. However, both the formula and the limits vary between different Oracle ports and versions, and so it is best to check in X$KVII.
select
  kviidsc,
  kviival
from
  sys.x$kvii
where
  kviitag in (’kcbswc’, ’kcbscc’)
/

KVIIDSC                    KVIIVAL
-------------------------- -------
DB writer IO clump             100
DB writer checkpoint clump       8
Figure 4 - The DBWR batch size limits
You need to know what your batch size limit is, to determine how full the batches that DBWR is writing are. If they are full, or close to full, then there will clearly be benefit in raising the limit. To determine the size of the batches being written, you should divide the physical writes statistic, which is the number of blocks written, by the write requests statistic, which is the number of batches written. These statistics are found in V$SYSSTAT. Even when the actual batches written are well short of the limit on average, experience shows that increasing the batch size limit does increase the average batch size and help to reduce free buffer waits.
To set the DBWR batch size limit implicitly, you should set DB_FILES slightly higher than the number of datafiles in use, and adjust DB_FILE_SIMULTANEOUS_WRITES as required.
Tuning the Checkpoint Batch Size Limit
You may also have read about the DB_BLOCK_CHECKPOINT_BATCH parameter, which sets the maximum number of blocks in a batch that can be devoted to checkpointing. This parameter defaults to just 8 blocks, despite a documentation error that persisted through to version 7.2, saying that it was a quarter of the write batch size limit.
It is sometimes recommended to set this parameter equal to the write batch size limit. The objective is to make checkpoints as fast as possible, but the speed of checkpoints is really not that important. Foreground processes only wait for checkpoints if a log switch checkpoint has not yet been completed by the time it is necessary to switch back into that online log file, or if the checkpoint is needed to write any cached, dirty blocks from a segment that has been dropped or truncated. The first case is evidence of a gross tuning error that should be corrected by other means, and in the second case a fast checkpoint is performed, and so the checkpoint batch size limit does not apply.
Also, if an entire batch or more is devoted to checkpointing from time to time, then DBWR will not be cleaning buffers in a way that will enable them to be found by sessions requiring a free buffer, and free buffer waits are likely to result.  Therefore, it is important that the checkpoint batch size limit be small enough to allow DBWR to continue its normal work for the duration of each checkpoint, with little or no backlog developing.
However, a small checkpoint batch size limit does have one drawback. Typically, during a checkpoint, batches are written more frequently than at other times, with the result that the actual batch size quickly falls well below the normal size, sometimes right down to the checkpoint batch size limit, or even below it. This means that the checkpoint batch size limit can actually afford to be raised, as long as free buffer waits are not introduced at the beginning of each checkpoint. The objective of doing so, is merely to reduce the number of batches and thus the DBWR CPU usage. 
Avoiding Write Complete Waits
Now that I have told you about the write batch size limit, I must warn you against setting it too high. If you do, you may eliminate free buffer waits, but you will introduce write complete waits instead. Write complete waits occur because Oracle cannot allow blocks that are about to be written to be modified, lest an inconsistent block image be written to disk.
Unlike free buffer waits, write complete waits cannot be prevented entirely. There is always the chance that a session may need to modify a block that is part of DBWR’s batch. This applies particularly to hot blocks like rollback segment header blocks. Of course, hot blocks do not normally get written, except during checkpoints or idle writes — so, that is when the risk of getting write complete waits is highest.
The write batch size limit affects write complete waits in two ways. Firstly, the larger the batch size, the longer the batch will take to write, and the higher the risk of needing to modify one of the blocks.  This implies that if you need to use multiple slave DBWR processes instead of true asynchronous I/O, then your batch size limit should be of the same order of magnitude as the number of slaves configured. Secondly, the larger the batch size limit, the greater the risk of choosing a block that will need to be modified soon, such as blocks needed by a transaction that has been caught for a while in a resource wait.
So before you begin tuning the write batch size limit, you should determine how bad your write complete waits are as a proportion of db block changes, and what the average waiting time is. If, as you increase the write batch size limit, you find that your write complete waits get worse, then stop — do not increase the write batch size limit further. There is another way to combat free buffer waits, that should be used, rather than risking a deterioration in write complete waits.
Another reason for not setting the DBWR batch size limit too high, even implicitly, is that on some Oracle ports and versions it is possible to generate batches larger than the maximum number of simultaneous asynchronous writes supported by the operating system. If you make this mistake, you will learn about it soon enough, because writes will fail, KCF error lines will appear in your ALERT.LOG file and the datafiles affected will be automatically taken offline.
Tuning the Batch Frequency
The last, and most complex, aspect of the DBWR tuning mystery is how to tune the frequency with which DBWR writes. However, before we can get into the details, I need to explain a few things about the database buffer cache control structures. I have already made mention of the buffer header array. This is made visible to DBAs via the X$BH virtual table. There is a one-to-one relationship between buffers and buffer headers. The buffer headers contain a host of information. Most importantly, they record which database block is currently cached in the corresponding buffer, and whether it is in current mode or consistent read mode. Current mode blocks represent the current disk image of the block, whereas consistent read mode blocks represent an earlier transaction consistent version of the block. Of course, only current mode blocks can be dirty — that is, they may contain modifications that have yet to be written to disk.
The second important buffer cache control structure to mention is the working set header array, which is made partially visible to DBAs via the X$KCBWDS virtual table. This table contains one row per working set. It did not exist prior to version 7.3, because the entire cache was just one working set. Each working set is comprised of several linked lists of buffers — the LRU, or replacement list; the write list; and in version 8 there is also a separate ping list. The working set data structure itself contains pointers to the buffer headers of the buffers at the head and tail of each of these lists. Each buffer header then contains pointers to the next and previous buffers in its list, and an indicator of which list it is on. Thus all these lists can be traversed in either direction from any point.
When an Oracle session requires a free buffer, it firstly determines which buffer pool and which working set it will look in. It then begins searching up the replacement list, starting with the last buffer, for a block that is neither dirty, nor pinned for use by another session. If a reusable buffer is found, it is used and moved up to the top of the replacement list. In future versions, this behaviour will vary slightly when the alternative replacement policies for the KEEP and RECYCLE buffer pools have been implemented. If during its scan of the replacement list, a session inspects a block that is dirty and not pinned, then it unlinks that buffer from the replacement list, and adds it to the head of the write list. The number of blocks moved to the write list in this way is reported in the free buffer inspected statistic in V$SYSSTAT.
The frequency with which DBWR writes, depends largely on messages called DBWR make free requests that are triggered by either of two events associated with the procedure of looking for a reusable buffer. When either of these events occurs, the session searching for a reusable buffer pauses to check whether a make free request is already pending for that working set. If not, it creates a message in the shared pool for DBWR and posts DBWR’s semaphore, before continuing its search for a reusable buffer.
The first threshold that can trigger a make free request is when the search for a reusable buffer has inspected a certain number of buffers without finding a reusable buffer. This threshold is surprisingly called the DBWR scan depth. The reason for that name, is that when DBWR receives the make free request, it will scan the replacement list from that buffer back down to the end of the list, looking for additional buffers to write, other than those already on the write list. The number of buffers actually scanned by DBWR is commonly in fact less than its scan depth, because some buffers may already have been moved to the write list by the session that issued the make free request, or by a subsequent scan of the same replacement list by another session. This difference can be seen in the statistics DBWR summed scan depth and DBWR buffers scanned which can both be found in V$SYSSTAT.
The interesting thing about the DBWR scan depth, is that it is self tuning. It actually changes from moment to moment while the instance is active, to compensate for fluctuations in the load on DBWR. The DBWR scan depth ranges between a lower and an upper bound, which are derived from either the write batch limit, or the working set size, depending on your Oracle version. At instance startup, it is set to the lower bound. Then at any time when DBWR is found to be falling behind, the scan depth is dynamically increased. The _DB_WRITER_SCAN_DEPTH_INCREMENT parameter controls the rate of increase, and defaults to one eighth of the difference between the upper and lower bounds for the DBWR scan depth, rounded. On the other hand, if DBWR is ever found to be working too hard, then the scan depth is automatically reduced by the value of the _DB_WRITER_SCAN_DEPTH_DECREMENT parameter, which defaults to 1. The effect of these defaults is that DBWR will respond to bursts of load quickly, but will take a long time to adjust to reduced activity. The current DBWR scan depth, its maximum and minimum bounds, and the increment and decrement parameter settings, can be seen in X$KVIT as illustrated in figure 5.
select
  kvitdsc,
  kvitval
from
  sys.x$kvit
where
  kvittag in (’kcbldq’, ’kcbsfs’) or
  kvittag like ’kcbsd_’
/

KVITDSC                              KVITVAL
------------------------------------ -------
large dirty queue if kcbclw reaches        6
this
DBWR blocks to scan looking for          400
dirty
DBWR blocks to scan lowest value          25
DBWR blocks to scan highest value        400
DBWR scan depth increment                 47
DBWR scan depth decrement                  1
foreground blocks to scan looking      16000
for free
Figure 5 - Some parameters affecting DBWR
The second event that can trigger a make free request is when a dirty buffer is moved to the write list during a search for a reusable buffer, and the length of the write list thereby becomes equal to its threshold, which is set by the _DB_LARGE_DIRTY_QUEUE parameter, and defaults to 1/16th of the write batch size limit, rounded. This is the parameter that you should set to control the frequency at which DBWR writes. It should be reduced gradually from its default, one block at a time, until free buffer waits are eliminated. The cost of each reduction in this parameter setting is an increase in DBWR CPU usage, so it should not be brought down without good reason.
Spotting a Potential Problem
When a session searching for a reusable buffer has issued a make free request to DBWR, it resumes its scan of the replacement list, up to the number of blocks specified by the parameter _DB_BLOCK_MAX_SCAN_CNT, which defaults to one quarter of the working set, rounded down. If this limit is reached without finding a free buffer, then the dirty buffers inspected statistic in V$SYSSTAT is incremented by the number of dirty and pinned blocks inspected during the scan, and the free buffer request fails.
Now, you may think that a failed free buffer request would translate immediately into a free buffer wait. Not necessarily so. Oracle often knows in advance that a certain resource will soon be needed. In some cases, it attempts to get that resource immediately in no wait mode. If the attempt succeeds, it holds the resource until it is needed. If the attempt fails, it continues with other work. When that work is done, it attempts to get the resource again, this time in willing to wait mode, and if necessary, waits until the resource is available. This applies to certain latches, and it also appears to apply to free buffer requests for physical reads. Before a physical read into the buffer cache, Oracle attempts to get a free buffer in no wait mode. If it succeeds, the block is read into that buffer. If it fails, Oracle goes ahead with the physical read anyway, using a temporary buffer. Then when the read has completed, it issues a second free buffer request, waiting if necessary, and then copies the block from its temporary buffer into the buffer cache.
The genius of this design is that even if a no wait free buffer request fails, it is highly likely that DBWR will have been able to clean some buffers by the time the session attempts to find a free buffer again. This is because the session would have paused to issue a make free request long before its initial scan failed. Then while the session was busy with its other work, DBWR had enough time to make some free buffers available. 
Because of this, it is possible for some free buffer requests to fail, as shown by the dirty buffers inspected statistic in V$SYSSTAT, yet for there to be no free buffer waits. This should be taken as a warning of a potential problem, and tuned accordingly. If you are using the version 8 feature of multiple buffer pools, this and all the related statistics should be taken from X$KCBWDS, where the different performance of each buffer pool can be seen.
Conclusion
The Oracle developers have clearly put a lot of careful thought into optimising the performance of DBWR, maintaining statistics by which it can be monitored, and providing parameters and techniques by which it can be tuned.
Unfortunately, the tight scope of this brief paper has not allowed me to treat a number of other aspects of DBWR operation such as idle writes, pings, and cache latch tuning, nor to explain a number of the other statistics that can give you an even deeper insight into specific situations. However, I trust that I have established for you a framework upon which you can build your own experiences of The Mysteries of DBWR Tuning.
 
 

Steve Adams began work as an Oracle DBA/Developer in 1984. Since then, he has worked in a number of roles, from youth work to management. Over the last two years, he has returned to working on Oracle, this time with a concentration of performance issues. He lives in Sydney with his wife and three children, and can be contacted at steveadams@acslink.net.au.

Картинки посмотрите в первоисточнике.

 

Новый комментарий

Я не спамер: введите суму 6+6



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужна гадалка
20 июля, 1 ответа
Бутель для воды
20 июля, 1 ответа