Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter