||Oracle Tips by Burleson
Tuning the Three Pools
In Oracle8i, the classic method of tuning the shared pool is not
available, so we must examine alternative methods to achieve the
same ends. This involves looking at what Oracle has provided for
tuning the pools. A script, catperf.sql, offers several views for
tuning the Oracle buffer pools.
These views are:
V$BUFFER_POOL. Provides static
information on pool configuration.
V$DBWR_WRITE_HISTOGRAM. Provides summary
information on DBWR write activities.
V$DBWR_WRITE_LOG. Provides write
information for each buffer area.
Of these four views,
V$BUFFER_POOL_STATISTICS seems the most useful for tuning the buffer
pool. It contains statistics such as buffer_busy_waits,
free_buffer_inspected, dirty_buffers_inspected, and physical
If a buffer pool shows excessive numbers of dirty_buffers_inspected,
and high amounts of buffer_busy_waits, then it probably needs to be
increased in size.
In Oracle, the V$DB_CACHE_ADVISE view is available for tuning the
buffer areas, which is described in the next subsection.
When configuring LRU latches and DBWR
processes, remember that the latches are assigned to the pools
sequentially and to the DBRW processes in round-robin fashion. The
number of LRU processes should be equal to or a multiple of the
value of DBWR processes to ensure that the DBRW load is balanced
across the processes.
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download.