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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Locating Tables and Indexes for the KEEP Pool

Oracle Tips by Burleson

The Oracle documentation states, “A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system.” In other words, small, highly accessed tables are good candidates for caching.

So, how do we identify small-table full table scans? The best method is to explain all of the SQL that is currently in your library cache and then generate a report showing all of the full table scans in your database at that time. We will introduce a very important script called access.sql that will produce these reports in Chapter 15, but for now, let's just assume that we have already explained all of the SQL in our library cache.

The access_report Script

This script interrogates the execution plans from the output of access.sql and produces a report of the frequency of full table scans. The details for running this script are explained in Chapter 11.


-- ********************************************************
-- 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 a24;
column ch       format a1;
column K        format a1;

spool access.lst;

set heading off;
set feedback off;
ttitle 'Total SQL found in library cache'
select count(distinct statement_id) from plan_table;

ttitle 'Total SQL that could not be explained'
select count(distinct statement_id) from plan_table where remarks is not null;

set heading on;
set feedback on;
ttitle 'full table scans and counts|  |Note that "?" indicates in the table is cached.'
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','K','DEFAULT',' ') K,
   s.blocks blocks,
   sum(s.executions) nbr_FTS
   dba_tables t,
   dba_segments s,
   sqltemp s,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
      operation = 'TABLE ACCESS'
      options = 'FULL') p
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   t.owner = s.owner
   t.table_name = s.segment_name
   t.table_name =
   t.owner = p.owner
   sum(s.executions) > 9
group by
   p.owner,, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(s.executions) desc;

The first section of the access_report.sql script interrogates the plan table and produces a report showing all full table scans and their frequency. We also have two columns showing if a table is marked as cached (Oracle7) or assigned to the KEEP pool (Oracle8).

This report shows the following columns:

  • OWNER The owner of the table.

  • NAME This is the table name.

  • NUM_ROWS The number of rows in the table as of the last compute statistics.

  • C (Oracle7 only)This is an Oracle7 column and will display Y if the table is cached, and N if it is not cached.

  • K (Oracle8 only)This is an Oracle8 column that will display a “K” if the table exists in the KEEP pool.

  • BLOCKS This is the number of blocks in the table as defined by the dba_segments view.

  • NBR_FTS This is the number of full table scans against this table from the data currently in the library cache.

This should give us all of the information we need to identify tables for the KEEP pool. Any small tables (for example, less than 2% of db_cache_size) that have a high number of full table scans will benefit from being added to the KEEP pool. In the following report, we see output from an Oracle Applications database, and we see full table scans on both large and small tables.

Mon Jan 22                                                             page    1
                   Total SQL found in library cache


Mon Jan 22                                                             page    1
                   Total SQL that could not be explained


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     
APPLSYS    FND_CONC_REL_DISJ_MEMBER           39 N K        2   50,174     
APPLSYS    FND_FILE_TEMP                       0 N         22   48,611     
APPLSYS    FND_RUN_REQUESTS                   99 N         32   48,606     
INV        MTL_PARAMETERS                      6 N K        6   21,478     
APPLSYS    FND_PRODUCT_GROUPS                  1 N          2   12,555     
APPLSYS    FND_CONCURRENT_QUEUES_TL           13 N K       10   12,257     
AP         AP_SYSTEM_PARAMETERS_ALL            1 N K        6    4,521     
APPLSYS    FND_CONCURRENT_QUEUES              13 N K       10    4,078

From examining this report, we identify the following files for addition to the KEEP pool. We select those tables with less than 50 blocks that are not already in the KEEP pool (the “K” column).

CAUTION: Remember that identifying tables for the KEEP pool is an iterative process. These reports only show you the SQL that happens to reside in your library cache at the time you ran the report.

OWNER          NAME                         NUM_ROWS C K   BLOCKS  NBR_FTS
-------------- ------------------------ ------------ - - -------- --------
PPLSYS         FND_FILE_TEMP                      10 N         22   48,611
APPLSYS        FND_RUN_REQUESTS                   99 N         32   48,606
APPLSYS        FND_PRODUCT_GROUPS                  1 N          2   12,555

Remember, our goal is for the data buffer hit ratio for the KEEP pool to always be 100 percent. Every time we add a table to the KEEP pool, we must also add the number of blocks in the table to the KEEP pool parameter in our Oracle file.

Once you have explained all of the SQL in your library cache, you will have a plan table with all of the execution plans and a table named sqltemp with all of the SQL source code (see Chapter 11 for details on explaining all of your SQL). Once these tables are populated, you can run a script to generate the KEEP syntax for you. Let's take a look at this script:


   'alter table '||p.owner||'.'||||' storage (buffer_pool keep);'
   dba_tables t,
   dba_segments s,
   sqltemp s,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
      operation = 'TABLE ACCESS'
      options = 'FULL') p
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   t.table_name = s.segment_name
   t.table_name =
   t.owner = p.owner
   t.buffer_pool <> 'KEEP'
   s.blocks < 50
group by
   p.owner,, t.num_rows, s.blocks
order by
   sum(s.executions) desc;

To make it easy, we can simply run this script and let Oracle generate the KEEP syntax on our behalf:

SQL> @access_keep_syntax

alter table APPLSYS.FND_FILE_TEMP storage (buffer_pool keep);
alter table APPLSYS.FND_RUN_REQUESTS storage (buffer_pool keep);
alter table APPLSYS.FND_PRODUCT_GROUPS storage (buffer_pool keep);

The final step is to increase the buffer_pool_keep  parameter by the total number of blocks in our tables. The prior value is 1400, and we see that these three tables add 56 blocks to the prior value. Hence we change our Oracle parameter as follows:


We are now ready to bounce the database and then execute the output from the access_keep_syntax script.

STATSPACK Tables and KEEP Pool Data

If you want to get extremely sophisticated, you can try to explain the SQL statements that are stored in the stats$sql_summary table. If you are taking the default level-5 STATSPACK snapshots, the stats$sql_summary table will contain the top SQL statements that were in your library cache at the time of each hourly snapshot.

You can simply modify the access.sql script from Chapter 11 to use the stats$sql_summary table instead of the v$sqltext.

Advanced KEEP Pool Candidate Identification

In addition to small-table full table scan tables, the KEEP buffer pool may be a good place to keep data blocks from segments that are used frequently and occupy a significant amount of blocks in the data buffer. These are commonly blocks within small reference tables that are accessed via an index and do not appear in our full table scan report.

There is only one window into the internals of the Oracle database buffers: the x$bh internal view. The x$bh internal view contains a great deal of detailed information about the internal operations within the data buffer pools. From the x$bh table, we can count the number of objects in a specific type and the number of “touches” for that object type. It is even possible to use this view to create a picture of all data blocks that are in the buffer.

The following query uses the x$bh view to identify those objects whose blocks average more than five touches and occupy more than twenty blocks in the cache. This will identify tables and indexes that are frequently referenced, and therefore become candidates for inclusion in the KEEP pool.


--    hot_buffers.sql
--    Written by Donald K. Burleson
--    1/22/2001
-- *********************************
-- You MUST connect as SYS
connect sys/manager;
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

   object_type  mytype,
   object_name    myname,
   COUNT(1) buffers,
   AVG(tch) avg_touches
   sys.x$bh    a,
   dba_objects b,
   dba_segments s
   a.obj = b.object_id
   b.object_name = s.segment_name
   b.owner not in ('SYS','SYSTEM')
   AVG(tch) > 5
   COUNT(1) > 20;

NOTE: The hot_buffers.sql script will not run on releases lower than Oracle8i because the tch column is a new addition in Oracle 8.1.6.

Next is the output from this script. The hot_buffers.sql script identifies those objects that are active within the data buffers, both as a function of the number of data blocks and the number of touches.

Type    Name                               BLOCKS  BUFFERS AVG_TOUCHES
------- ------------------------------ ---------- -------- -----------
TABLE   PAGE                                  104      107          44
TABLE   SUBSCRIPTION                          192       22          52
INDEX   SEQ_KEY_IDX                            40       34          47
TABLE   SEC_SESSIONS                           80      172          70
TABLE   SEC_BROWSER_PROPERTIES                 80       81          58
TABLE   EC_USER_SESSIONS                       96       97          77
INDEX   SYS_C008245                            32       29         270

Once you identify hot objects using this script, you are faced with the decision of segregating the objects into the KEEP pool. As a general rule, you should have enough available RAM storage for the entire table or index. For example, if you wanted to add the page table to the KEEP pool, you would need to add 104 blocks to the buffer_pool_keep Oracle parameter.

Because of the transient nature of the data buffers, the results from this script will be different every time you execute the script. Consequently, some DBAs will schedule this script to execute every minute whenever they need to see exactly what is happening inside the data buffers.

Now that we have covered the internals of the KEEP pool, let's turn our attention to the identification of candidates for the RECYCLE pool.

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