 |
|
Tools for Tuning I/O
Contention
Oracle Tips by Burleson
|
Once the shared SQL areas and buffer caches
have been tuned, the DBA must turn his or her eyes to the I/O
performance of the disks and files associated with the Oracle system
to realize further performance gains.
Tuning I/O to Avoid Bottlenecks
Once the application and memory areas have
been tuned, the next potential performance bottleneck may be the disk
subsystem. This system is addressed by tuning the input and output
processes that Oracle uses, reducing contention for disk resources,
and reducing or eliminating dynamic space allocation within database
data files.
Tuning the DBWR Process
The DBWR process manages the buffer cache. In
this capacity, it writes filled buffers from the buffer cache in the
SGA to the disks. Obviously, a properly tuned DBWR process will be the
first step in tuning I/O for the Oracle system. The DBWR process, as
described in the section on UTLBSTAT and UTLESTAT, uses the hidden
INIT.ORA parameters _DB_BLOCK_WRITE_BATCH and _DB_BLOCK_MAX_SCAN_CNT
in Oracle8 to determine when it should write used, or dirty, buffers
to the disk, thus freeing them for further use. DBWR triggers on the
following conditions:
1. A user process writes a used buffer to the
dirty buffer list and finds it is _DB_BLOCK_WRITE_BATCH / 2 long.
2. A user process searches _DB_BLOCK_MAX_SCAN_CNT
buffers without finding a clean one.
3. The DBWR has been inactive for three
seconds.
4. When a checkpoint occurs, LGWR signals DBWR
to trigger it to write.
The DBWR writes out _DB_BLOCK_WRITE_BATCH
buffers each time it is triggered. If there aren't that many buffers
in the dirty buffer list, the buffers on the LRU list are written
until _DB_BLOCK_WRITE_BATCH buffers are written.
Note: In Oracle8i and Oracle9i, you can
no longer tune the parameters mentioned above because they have been
deprecated. In Oracle8i and Oracle9i, the parameter _DB_WRITER_MAX_WRITES
controls the maximum number of outstanding I/Os that a database writer
can issue, but you should not touch this parameter unless instructed
to by Oracle Support.
An example report that pulls the DBWR related
statistics from the V$SYSSTAT, V$WAITSTAT, and
V$BUFFER_POOL_STATISTICS tables is shown in Source 13.16. The report
generated from Source 13.16 is shown in Listing 13.17.
SOURCE 13.16 Report to pull DBWR statistics.
rem
dbwr_stat.sql
rem Mike Ault - 11/09/01 Created
rem
col name format a46 heading 'DBWR Statistic'
col value format 9,999,999,999 heading 'Statistic Value'
set pages 40
@title80 'DBWR Statistic Report'
spool rep_out\&db\dbwr_stat
select a.name,a.value
from (select name, value from v$sysstat
where name not like '%redo%' and name not like '%remote%') a
where (a.name like 'DBWR%' or a.name like '%buffer%'
or a.name like '%write%' or a.name like '%summed%)
union
select class name, count value from v$waitstat
where class='data block'
union
select name||' '||to_char(block_size/1024)||'K hit ratio',
round(((1 - (physical_reads / (db_block_gets + consistent_gets))) *
100),3)
value
from V$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'K free buffer
wait',free_buffer_wait
value
from V$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'K buffer busy
wait',buffer_busy_wait
value
from V$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'K write complete
wait',write_complete_wait value
from V$buffer_pool_statistics
/
spool off
set pages 22
ttitle off
LISTING 13.17 Example DBWR statistics report.
Date:
11/09/01 Page: 1
Time: 04:44 PM DBWR Statistic Report DBAUTIL
aultdb1 database
DBWR
Statistic Statistic Value
---------------------------------------------- ---------------
DBWR buffers scanned 0
DBWR checkpoint buffers written 2,601
DBWR checkpoints 18
DBWR cross instance writes 0
DBWR free buffers found 0
DBWR fusion writes 0
DBWR lru scans 0
DBWR make free requests 0
DBWR revisited being-written buffer 0
DBWR summed scan depth 0
DBWR transaction table writes 95
DBWR undo block writes 1,156
DEFAULT 2K buffer busy wait 0
DEFAULT 2K free buffer wait 0
DEFAULT 2K hit ratio 98
DEFAULT 2K write complete wait 0
DEFAULT 8K buffer busy wait 3
DEFAULT 8K free buffer wait 0
DEFAULT 8K hit ratio 99
DEFAULT 8K write complete wait 0
buffer is not pinned count 570,196
buffer is pinned count 392,710
change write time 340
commit cleanout failures: buffer being written 0
commit cleanout failures: write disabled 0
data block 3
dirty buffers inspected 0
free buffer inspected 0
free buffer requested 5,054
hot buffers moved to head of LRU 0
no buffer to keep pinned count 208,657
physical writes 4,792
physical writes direct 2,056
physical writes direct (lob) 0
physical writes non checkpoint 3,476
pinned buffers inspected 0
summed dirty queue length 122
switch current to new buffer 219
write clones created in background 4
write clones created in foreground 7
Pay attention to the following in the report
in Listing 13.17:
DBWR checkpoints. Number of checkpoint
requests sent to DBWR since startup.
DBWR buffers scanned. Number of DB
buffers scanned since startup.
Summed dirty queue length. Length of
the dirty buffer queue. If this gets over 50, Oracle says to add
DB_WRITER_PROCESSES..
Physical writes. Number of physical
writes performed by the DBWR. If this is high, then there may be
insufficient buffers allocated. In Oracle9i, increase DB_CACHE_SIZE;
in pre-Oracle9i, increase DB_BLOCK_BUFFERS.
Data block. A statistic harvested from
the V$WAITSTAT table; shows if there are any data block waits
occurring. Excessive data block waits when the hit ratio is high can
indicate need for more DBWR processes.
Hit ratios. A hit ratio will be
calculated for each buffer pool and each separate block size in the
default pool. Generally speaking, high hit ratios are desirable, low
are not; but hit ratio is not the end-all/be-all statistics for buffer
health.
Waits. Various waits will be reported
for all pools and all areas of the default buffer with different block
sizes. Pay attention to waits that deal with writes; if write-type
waits are excessive, then more DBWR processes are in order. Buffer
busy waits may indicate a need for more buffers.
There is no need to modify the DBWR internal
batch size in Oracle9i. The write sizes performed by DBWR are
automatically calculated by DBWR in Oracle9i. The write size depends
on the number of dirty blocks to be written, and is tempered with the
maximum number of writes (which is operating system-specific). The
values chosen by DBWR are not configurable in Oracle9i.
DBWR is monitored using the statistic-free
buffer waits. The free buffer waits statistic (available from the
V$SYSSTAT table) should be as low as possible and should remain at a
slowly increasing value. This means that, for your system, you have to
decide: If you see spikes when database activity is high, consider
using either more DB writers by setting the initialization parameter
DB_WRITERS (Oracle7), DB_WRITER_IO_SLAVES (Oracle8)
DB_WRITER_PROCESSES and DBWR_IO_SLAVES (Oracle8i, Oracle9i) equal to
the number of disks used by Oracle, if your system doesn’t support
asynchronous I/O; or setting the ASYNC_IO initialization parameter to
TRUE, if your system supports asynchronous I/O. If setting either the
parameter that controls DBWR processes or ASYNC_IO doesn’t help reduce
the spikes on free buffer waits , verify that _DB_BLOCK_MAX_SCAN_CNT
(Pre-Oracle8i and Oracle9i) is set at 30 or greater. Normally, the
default value of 30 is fine for this parameter. If you are
dissatisfied with the performance of DBWR, first try increasing the
INIT.ORA parameter _DB_BLOCK_WRITE_BATCH (on non-Oracle8i, or
non-Oracle9i databases). Increasing this parameter improves DBWR’s
ability to use operating system facilities to write to multiple disks
and write adjacent blocks in a single I/O operation. Increasing the
number of DB block buffers may also be in order if DBWR performance is
poor.
On many platforms, Oracle can use the built-in
asynchronous ability of the operating system to multithread the DBWR
process. On platforms where asynchronous I/O is not possible, multiple
DBWR processes should be started using the DB_WRITERS initialization
parameter. On some systems, such as HP, it has been shown that
multiple DBWR processes may have a positive effect, even with
asynchronous I/O set. Note that in Oracle8i, the parameter changed: it
is DB_WRITER_PROCESSES; an additional parameter, DBWR_IO_SLAVES, also
is used. The difference between processes and I/O slaves is that if an
I/O slave terminates, the instance continues to run. You cannot run
multiple DBWR processes and DBWR I/O slaves on the same instance. You
may need to increase the number of DB_BLOCK_LRU_LATCHES to increase
the number of DB_WRITER_PROCESSES. (Note that in Oracle9i,
DB_BLOCK_LRU_LATCHES has changed to _DB_BLOCK_LRU_LATCHES, and becomes
undocumented.)
Two additional undocumented parameters that
may assist in DBWR tuning are _DB_BLOCK_HASH_BUCKETS and _DB_BLOCK_HASH_LATCHES.
The _DB_BLCOK_HASH_BUCKETS parameter should be set to the nearest
prime number greater than twice the number of DB_BLOCK_BUFFERS on
pre-Oracle9i systems. In Oracle9i, this parameter is correctly set,
but on older systems, it may be set to twice the value of
DB_BLOCK_BUFFERS and will have to be adjusted. The other parameter, _DB_BLOCK_HASH_LATCHES,
is set to 1,024, but you may see better performance on some releases
of Oracle8i if this is increased to 32,768. The other undocumented
parameters dealing with the DBWR and DB blocks should not be touched.
Another way to improve the performance of DBWR
on older systems is to enable the checkpoint process by using the
CHECKPOINT_PROCESS initialization parameter. This frees DBWR from
checkpoint duties, allowing it to concentrate on buffers only. In
Oracle8 and later releases, the checkpoint process is automatically
started.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
