Are I/O slaves needed?

[KenEwald]

According to oracle support io slaves (db_writer_processes) are not needed or desireable with async io enabled.

Does anyone know why?

[Telco_DBA]

Hi Ken,

I don't know it, but during last years I was reading many articles very often ... somewhere I found even the hint that there are operating systems which have the "async IO" kernelized and some which just "simulate" it ...

The Oracle Docu "rewrites" that as:

Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently.

The Metalink-Article quoted below contains the sentence:

There is NOT a definite answer to this question but here are some considerations ....

That's my understanding:

(1) In case that "async IO" is kernelized the DB_WRITER-process issues a write-call to the Operating System and without waiting for the write-call to complete the DB_WRITER process scans for new dirty blocks on the LRU-list and issues the next write-call again. The write-call is completed by the OS asynchronously.

(2) In case that "async IO" is not kernelized then the DB_WRITER process has two options:
(2.1) wait until the synchronous write call is completed or
(2.2) pass the write-call to an "IO slave process" and continue as in (1)

Therefore the "IO slaves" are just the "workaround" to simulate asynchronous IO.

Note following options:

(1) "kernelized async IO" -> one or multiple DB_WRITER-processes but NO IO_SLAVES
(parameter "DB_WRITER_PROCESSES" Default value 1, static, rante 1 to 20)

(2) no async-IO: DBWR_IO_SLAVES but only 1 (one) DB_WRITER
==> but

Here Description for DBWR_IO_SLAVES from 9.2 docu:
===================================================

DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default,
the value is 0 and I/O server processes are not used.

If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server
processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false.

Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently.

However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.

I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.

And here a FAQ from Metalink:
==============================
http://metalink.oracle.com/metalink/...T&p_id=97291.1

Regards,

Telco_DBA
http://www.Mercury-Consulting-Ltd.com

[KenEwald]

Telco_DBA,

Thank you very much for all the information you posted.

So, setting db_writer_processes > 1 when async io is enabled should only be done when there's a proven need for more processes to pull data from data buffers and write them to disk.

How do I measure the capacity of a dbwr process?

Or, how do I tell dbwr is io bound?

Would I add a second and see what happens to performance?

Thanks,

Ken

[Telco_DBA]

Hi Ken,

in my understanding (there are some other senior gurus in this forum whom I ask to correct me) the best indication for a DB_WRITER problem is the wait event "FREE BUFFER WAITS"

When Oracle reads a block from disk, it puts this block in the DB Block Buffer Pool in following cases:
*) db_file_sequential_read (via Index and rowid reading data block)
*) db_file_scattered_read (full table scan with hint "/*+ CACHE() */ or if table smaller than "small_table_threshold" (in some old releases documented, now undocumented, I did once read that this about 1% of DB_BLOCK_BUFFER)

(The buffer pool is bypassed in case of "direct path read")

Therefore Oracle looks on the LRU for a free (not dirty) block. If it cannot find a free block (after scanning a defined number of blocks from the cold end of the LRU), then it "wakes up the DB_WRITER) and waits until DB_WRITER wrote dirty blocks to disks so that these buffers become available.

If your sessions cannot get free buffers and show "FREE BUFFER WAIT" that means that your DB_WRITER is too slow, because it did not free up enough dirty buffers in advance.

If you get that event I rather suggest before tuning the DB_WRITER to identify
*) WHY there is so much work for the DB_WRITER or
*) WHY there is such a high request for free buffers (e.g. a wrong execution plan or interactive SQL causing HUGE index-range-scans...). Check V$BH (or in older releases X$BH) to check what's in the block buffer.

The database writer wakes up every 3 seconds (or in case that free buffers are needed or in case of "truncate table or "checkpoint") and writes asynchronous, delayed and searches from the "cold" end of the LRU for dirty block - but it does NOT write all dirty blocks, it stops after checking a defined number of blocks. It is GOOD that the DB_WRITER does not try to write all dirty blocks, because the more often a block is changed before it's written the less is the total work for the DB_WRITER! (OK, an instance recovery would be slower, but that you can manage using the parameter FAST_START_MTTR_TARGET)

In Statspack-Reports you can check not only the DB_Writer writes/sec but also those written just because of checkpoints.

If you post a statspack-report I can highlight the values you need to look at.

Good Luck,

Telco_DBA
http://www.Mercury-Consulting-Ltd.com

[KenEwald]

I really appreciate your time and all the detailed information in your posts.

I'm going to scale down from 4 dbwr processors and watch for free buffer waits on the statspack report.

Thank you!

[Telco_DBA]

Hi Ken,

do you run hourly statspack-reports ?

If not, then it would be interesting if you take a few reports with 4 DB_WRITERS now and after reducing to 1 DB_WRITER to compare following values

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
summed dirty queue length 0 0.0 0.0
dirty buffers inspected 53 0.1 0.0
DBWR free buffers found 54,888 89.1 1.0
DBWR lru scans 64 0.1 0.0
DBWR make free requests 64 0.1 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 54,999 89.3 1.0
physical writes 43,024 69.8 0.8
physical writes direct 0 0.0 0.0
physical writes non checkpoint 33,072 53.7 0.6
free buffer inspected 124 0.2 0.0
free buffer requested 1,747,646 2,837.1 31.0
hot buffers moved to head of LRU 392,544 637.3 7.0

Buffer Pool Statistics for DB: instance0 Instance: instance0 Snaps: 7575 -7576
-> Pools D: default pool, K: keep pool, R: recycle pool

Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 1,748,033 6,484,420 1,731,611 43,045 0 0 103
-------------------------------------------------------------

Good Luck!

Telco_DBA
http://www.Mercury-Consulting-Ltd.co...itepapers.html

[KenEwald]

Excellent idea. I'll do it.

At least we'll know how good or (hopefully) how unnecessary it was.

I know a bunch of these databases won't need more than 1 because of light load.

Many thanks