Tuning the RECYCLE Pool
Oracle Tips by Burleson
The RECYCLE pool was created in Oracle8 to
provide a reusable data buffer for transient data blocks. A
transient data block is a data block that is being read as a part of a
large-table full table scan and is not likely to be requested again by
Oracle in the near future.
In Oracle7, a
RECYCLE pool is implemented in the data buffer by reserving blocks at
the end of the most recently used chain. These blocks at the end of
the buffer were loaded from data blocks from full table scans while
all other incoming data blocks went to the head of the data buffer
(see Figure 9-10).
Figure 9-64: The Oracle7 version of the RECYCLE
Now that we see how the KEEP pool is used,
let's discuss the RECYCLE pool and see how it is used for buffering
within the SGA.
Identifying Candidates for the
The goal of placing objects into the RECYCLE
pool is to segregate large tables that perform frequent full table
scans. To see the large-table full table scans, we must return to our
full table scan report from access.sql:
22 page 1
full table scans and counts
OWNER NAME NUM_ROWS C K BLOCKS
---------- ------------------------ ------------ - - --------
APPLSYS FND_CONC_RELEASE_DISJS 39 N K 2
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2
SYS DUAL N K 2
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 22
From this listing, we see one table that is clearly a
candidate for the RECYCLE pool. The fnd_conc_pp_actions table contains
1,262 blocks and has had 52,036 full table scans. This is a clear
candidate for inclusion into the RECYCLE pool.
CAUTION: Before blindly assigning a table to the
RECYCLE pool, the prudent DBA should always verify that the
large-table full table scan is legitimate. In many cases, a poorly
tuned query may be performing a full table scan against the table even
though the query returns far less than 40 percent of the table rows.
As a general rule, large-table full table scans are only legitimate in
systems such as data warehouses where frequent SUM or AVG queries are
required to touch the majority of the table rows.
Once we have noticed possible candidates for
the RECYCLE pool, we can run a script that will read the plan table
generated from access.sql. This query will look for large
tables (greater than 1,000 blocks) that are not already assigned to
the RECYCLE pool.
-- Report section
set echo off;
set feedback on
set pages 999;
column nbr_FTS format 999,999
column num_rows format 999,999,999
column blocks format 999,999
column owner format a14;
column name format a25;
set heading off;
set feedback off;
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool
operation = 'TABLE ACCESS'
options = 'FULL') p
s.addr||':'||TO_CHAR(s.hashval) = p.stid
t.table_name = s.segment_name
t.table_name = p.name
t.owner = p.owner
t.buffer_pool <> 'RECYCLE'
s.blocks > 1000
p.owner, p.name, t.num_rows, s.blocks
Here is an example of the output from this
alter table APPLSYS.FND_CONC_PP_ACTIONS storage (buffer_pool
Remember, before adding any table to the
RECYCLE pool, the DBA should extract the SQL source and verify that
the query is retrieving more than 40 percent of the rows in the table.
Now that we have covered a technique for
finding large-table full table scans from the library cache, let's
look at another approach for finding RECYCLE pool candidates that uses
the x$bh view.
Advanced RECYCLE Pool Tuning
The following query uses x$bh.tch to identify
objects in the buffer cache with single-touch buffer counts totaling
more than 5 percent of the total cache. These segments are potentially
good candidates for placement in the RECYCLE buffer pool as they are
occupying significant cache space with blocks that have not yet been
set pages 999;
column avg_touches format 999
column myname heading 'Name' format a30
column mytype heading 'Type' format a10
column buffers format 999,999
(select value totsize from v$parameter
where name = 'db_cache_size')
a.obj = b.object_id
tch=1 -- This line only works in 8.1.6 and above
b.object_name = s.segment_name
b.owner not in ('SYS','SYSTEM')
100*(COUNT(1)/totsize) > 5
NOTE: You must remove the reference to the touch (tch)
column if you are using a release of Oracle prior to 8.1.6. This
report is useful in releases prior to 8.1.6, but you will not be able
to tell how many times the objects have been touched after entry into
Here is a sample report from the output of this
script. Note that these indexes and tables comprise more than 5
percent of the space in the data buffer and the data blocks have only
been touched once. This is characteristic of large-table full table
Name BLOCKS BUFFERS PCT_CACHE
--------- ------------------------------ ---------- --------
INDEX WIP_REQUIREMENT_OPERATIONS_U1 1042 334
TABLE MTL_DEMAND_INTERFACE 847 818
TABLE MTL_SYSTEM_ITEMS 4227 493
Again, when making a decision to add one of the
objects to the RECYCLE pool, you need to consider the number of blocks
in the table and the frequency that the table or index appears in the
output from this script.
Remember, locating candidates for the RECYCLE
pool is an iterative process. Because of the dynamic nature of the
data buffer caches, the DBA may want to run this script every minute
for several hours to get the full picture of data block activity
within the data buffer.
Now that we have covered the monitoring and
tuning of the data buffer pools, let's take a look at scripts that can
be used to plot the average data buffer hit ratio over specific
periods of time.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.