Oracle Concepts - Buffer
Pool Related Views
Oracle Tips by Burleson Consulting
Tuning the Three Pools
Since the classic method of tuning the shared
pool is not available in Oracle8i we must examine new methods to
achieve the same ends. This involves looking at what Oracle has
provided for tuning the new pools. A new script, catperf.sql offers
several new views for tuning the Oracle buffer pools. These views are
shown in Table 16.
Provides static information on pool
Provides Pool related statistics
Provides summary information on DBWR write
Provides write information for each buffer
Table 16: Buffer Pool Related Views
Of the four new views the
V$BUFFER_POOL_STATISTICS view seems the most useful for tuning the
buffer pool. The V$BUFFER_POOL_STATISTICS view contains statistics
such as buffer_busy_waits, free_buffer_inspected,
dirty_buffers_inspected and physical write related data.
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.
When configuring LRU latches and DBWR
processes remember that the latches are assigned to the pools
sequentially and to the DBRW processes round robin. 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
I encountered problems building the views as
defined in the catperf.sql procedure for 8.1.3, hopefully by 8.1.5
(production) these problems are corrected.
Let me begin this section by stating that the
default values for the shared pool size initialization parameters are
almost always too small by at least a factor of four. Unless your
database is limited to the basic scott/tiger type schema and your
overall physical data size is less than a couple of hundred megabytes,
even the "large" parameters are far too small. What parameters control
the size of the shared pool? Essentially only one, SHARED_POOL_SIZE.
The other shared pool parameters control how the variable space areas
in the shared pool are parsed out, but not overall shared pool size.
In Oracle8 a new area, the large pool, controlled by the
LARGE_POOL_SIZE parameter is also present. Generally speaking I
suggest you start at a shared pool size of 40 megabytes and move up
from there. The large pool size will depend on the number of
concurrent users, number of multi-threaded server servers and
dispatchers and the sort requirements for the application.
What should be monitored to determine if the
shared pool is too small? For this you need to wade into the data
dictionary tables, specifically the V$SGASTAT and V$SQLAREA views.
Source 9. shows a report that shows how much of the shared pool is in
use at any given time the script is run.
shared_pool_used format 9,999.99
column shared_pool_size format 9,999.99
column shared_pool_avail format 9,999.99
column avail_pool_pct format 999.99
@title80 'Shared Pool Summary'
from v$sgastat a, v$parameter b
where (a.pool='shared pool'
and a.name not in ('free memory'))
rem SUM(a.BYTES)/1048576 pool_used,
rem max(b.value)/(1024*1024) shared_pool_size,
rem (sum(a.bytes)/max(b.value))*100 avail_pool_pct
rem v$sgastat a, v$parameter b
rem a.name in (
rem 'reserved stopper',
rem 'table definiti',
rem 'dictionary cache',
rem 'library cache',
rem 'sql area',
rem 'PL/SQL DIANA',
rem 'SEQ S.O.') and
NOTE: In 8i a new column has been added to
v$sgastat called POOL which can now be used to determine the size of
the shared pool. However, in 8.1.3 a sum of the bytes for all objects
in the shared pool exceeded the setpoint for shared_pool_size so this
may not be as accurate as the above query. In fact, it indicated 7
megabytes free, 13 megabytes total with a setting of 10 megabytes.
Source 9 Example Script to Show SGA Usage
The script in Source 9 should be run
periodically during times of normal and high usage of your database.
The results will be similar to Listing 15. If your avail_pool_pct
figures stay in the tens or below then you may need to increase the
size of your shared pool, however, this isn't always the case.
01/28/00 Page: 1
Time: 10:45 AM Shared Pool Summary MAULT
SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL AVAIL_POOL_PCT
---------------- ---------------- -----------------
597.46 619.89 22.43
Listing 15: Example Output From Script in
To often all that is monitored is how much of
the shared pool is filled, no one looks how is it filled; with good
reusable SQL or bad, throw-away, garbage SQL. You must examine how the
space is being used before you can decide whether the shared pool
should be increased in size, decreased in size or perhaps a periodic
flush schedule set up with the size remaining the same. So how can we
determine what is in the shared pool and whether it is being properly
reused or not? Let's look at a few more reports.
The first report we will examine shows how
individual users are utilizing the shared pool. Before we can run the
report a summary view of the V$SQLAREA view must be created, I
unimaginatively call this view the SQL_SUMMARY view. The code for the
SQL_SUMMARY view is shown in Source 10.
FUNCTION: Creates summary of v_$sqlarea and dba_users for use in
REM sqlmem.sql and sqlsummary.sql reports
REM NOTE: Requires direct grants on v$sqlarea and dba_users
CREATE OR REPLACE VIEW sql_summary as
username, sharable_mem, persistent_mem, runtime_mem
sys.v_$sqlarea a, dba_users b
a.parsing_user_id = b.user_id;
Source 10: Example SQL Script to Create A View
to Monitor Pool Usage By User
Once the SQL_SUMMARY view is created the
script in Source 11 is run to generate a summary report of SQL areas
used by user. This shows the distribution of SQL areas and may show
you that some users are hogging a disproportionate amount of the
shared pool area. Usually, a user that is hogging a large volume of
the shared pool is not using good SQL coding techniques that is
generating a large number of non-reusable SQL areas.
REM FUNCTION: Generate a summary of SQL Area Memory Usage
REM FUNCTION: uses the sqlsummary view.
REM showing user SQL memory usage
COLUMN areas HEADING Used|Areas
COLUMN sharable FORMAT 999,999,999 HEADING Shared|Bytes
COLUMN persistent FORMAT 999,999,999 HEADING Persistent|Bytes
COLUMN runtime FORMAT 999,999,999 HEADING Runtime|Bytes
COLUMN username FORMAT a15 HEADING "User"
COLUMN mem_sum FORMAT 999,999,999 HEADING Mem|Sum
START title80 "Users SQL Area Memory Use"
SET PAGES 59 LINES 80
BREAK ON REPORT
COMPUTE SUM OF sharable ON REPORT
COMPUTE SUM OF persistent ON REPORT
COMPUTE SUM OF runtime ON REPORT
COMPUTE SUM OF mem_sum ON REPORT
SUM( persistent_mem) Persistent,
SUM( runtime_mem) Runtime ,
GROUP BY username
ORDER BY 2;
PAUSE Press enter to continue
SET PAGES 22 LINES 80
Source 11: Example SQL Script To Report On SQL
Area Usage By User
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. Itís
only $19.95 when you buy it directly from the publisher