 |
|
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
pool
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
RECYCLE Pool
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:
Mon Jan
22 page 1
full table scans and counts
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
---------- ------------------------ ------------ - - --------
--------
APPLSYS FND_CONC_RELEASE_DISJS 39 N K 2
98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2
98,864
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2
98,864
SYS DUAL N K 2
63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262
52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 22
50,174
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.
access_recycle_syntax.sql
--
********************************************************
-- 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;
select
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool
recycle);'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
s.addr||':'||TO_CHAR(s.hashval) = p.stid
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.buffer_pool <> 'RECYCLE'
having
s.blocks > 1000
group by
p.owner, p.name, t.num_rows, s.blocks
order by
sum(s.executions) desc;
Here is an example of the output from this
script:
SQL> @access_recycle_syntax
alter table APPLSYS.FND_CONC_PP_ACTIONS storage (buffer_pool
recycle);
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
reused.
hot_recycle_blocks.sql
set lines
80;
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
object_type mytype,
object_name myname,
blocks,
COUNT(1) buffers,
100*(COUNT(1)/totsize) pct_cache
FROM
sys.x$bh a,
dba_objects b,
dba_segments s,
(select value totsize from v$parameter
where name = 'db_cache_size')
WHERE
a.obj = b.object_id
and
tch=1 -- This line only works in 8.1.6 and above
and
b.object_name = s.segment_name
and
b.owner not in ('SYS','SYSTEM')
GROUP BY
object_type,
object_name,
blocks,
totsize
HAVING
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
the pool.
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
scans.
Type
Name BLOCKS BUFFERS PCT_CACHE
--------- ------------------------------ ---------- --------
---------
INDEX WIP_REQUIREMENT_OPERATIONS_U1 1042 334
5.57
TABLE MTL_DEMAND_INTERFACE 847 818
13.63
TABLE MTL_SYSTEM_ITEMS 4227 493
8.22
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. |
|
