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.

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 processes.

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.

Shared Pool

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.

column 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'
spool rep_out\&db\shared_pool
 select
  least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,
  max(b.value)/(1024*1024) shared_pool_size,
  greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-(sum(a.bytes)/(1024*1024))
shared_pool_avail,
  ((least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)))/max(b.value))*100
avail_pool_pct
   from v$sgastat a, v$parameter b
 where (a.pool='shared pool'
 and a.name not in ('free memory'))
 and
 b.name='shared_pool_size';
rem SELECT
rem         SUM(a.BYTES)/1048576 pool_used,
rem                max(b.value)/(1024*1024) shared_pool_size,
rem                (max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,
rem                (sum(a.bytes)/max(b.value))*100 avail_pool_pct
rem   FROM 
rem         v$sgastat a, v$parameter b
rem   WHERE
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
rem                b.name='shared_pool_size';
spool off
ttitle off

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.

Date: 01/28/00                                             Page:   1
Time: 10:45 AM              Shared Pool Summary             MAULT         
                              dw database                                                                                                             

SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL AVAIL_POOL_PCT        
---------------- ---------------- ----------------- --------------        
          597.46           619.89             22.43            .00        

1 row selected.

Listing 15: Example Output From Script in Source 9.

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.

REM 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
REM
CREATE OR REPLACE VIEW sql_summary as
SELECT
   username, sharable_mem, persistent_mem, runtime_mem
FROM
   sys.v_$sqlarea a, dba_users b
WHERE
   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
REM FUNCTION: Generate a summary of SQL Area Memory Usage
REM FUNCTION: uses the sqlsummary view.
REM          showing user SQL memory usage
REM
REM sqlsum.sql
REM
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"
SPOOL rep_out\&db\sqlsum
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
SELECT
  username,
  SUM(sharable_mem) Sharable,
  SUM( persistent_mem) Persistent,
  SUM( runtime_mem) Runtime ,
  COUNT(*) Areas,
  SUM(sharable_mem+persistent_mem+runtime_mem) Mem_sum
FROM
  sql_summary
GROUP BY username
ORDER BY 2;
SPOOL OFF
PAUSE Press enter to continue
CLEAR COLUMNS
CLEAR BREAKS
SET PAGES 22 LINES 80
TTITLE OFF

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 here.