Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Using the Oracle KEEP pool

Oracle Tips by Burleson

December 11, 2003, updated January 31, 2016

Note:  The BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE pool parameters have been deprecated_in Oracle 12c.  Also see my related notes on keep_pool caching and Automatic KEEP pool assignment.

There are alternatives to KEEP pool caching, including SSD and in-memory column store.

*********************************************************
To minimize disk I/O and maximize performance, we need to look at caching (using the Oracle KEEP pool) all of your small tables that experience frequent full-table scans.

The Oracle SQL Cost-based optimizer will commonly perform a full-table scan on small tables when it recognizes that a multi-block read is faster than using an index.

According to Oracle documentation, these rules may be used to identify KEEP pool candidates:

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

More concisely, any small table that is in high demand is a good candidate for KEEP caching.

Depending on your database, caching important tables and indexes can increase performance by up to 50x;

Note: Using parallel query on full-table scans will not cache the data blocks in the KEEP pool.

But what about large objects?  Respected Oracle guru Karl Reitschuster has some notes on the benefits of caching large objects in the KEEP pool with a 5x response time improvement.

"We faced a problem on our company table querying specific company types a full table scan could not prevented.  . . The company table is about 1.7G large with about 6.5M of rows.

On our test database server two concurrent queries on the company table resulted in > 60sec instead of 25sec. And even 25 sec are too long. . .

Once loaded the table into the KEEP buffer cache the result of the query response time was < 5 sec even with concurrent sessions;"

Internally, it is critical to cache small-table full-table scans because the Oracle data buffer does not increase the touch count when blocks from full-table scans are referenced.

Hence, small-table full-table scan blocks will age-out of the data buffers very quickly, causing unnecessary disk I/O. I believe that this is causing you additional disk I/O that could be easily avoided.

Fortunately, it is relatively easy to locate the small-table full-table scans and cache them in the KEEP pool.  You can query the v$sql_plan view to see small-table full-table scans, and automatically cache them in the KEEP pool using the utilities from my book Oracle_Tuning: The definitive_reference.

Finding candidates for the KEEP Pool

Finding small-table, full-table scans is as easy as running this plan.sql script.  It will give you a report showing the table sizes and the number of full scans experienced by each table.

Another method for identifying tables and indexes for the KEEP pool examines the current blocks in the data buffer.  For this query, the rules are simple.  Any object that has more than 80% of its data blocks in the data buffer could probably be fully-cached using the KEEP pool.  Of course, the standard LRU algorithm should keep freuqently-referenced data blocks in memory.

It is highly unlikely that an undeserving table of index would meet this criterion.  Of course, you would need to run this script at numerous times during the day because the buffer contents change very rapidly. The following script can be run every hour via dbms_scheduler and automate the monitoring of KEEP pool candidates.  Every time it finds a candidate, the DBA will execute the syntax and adjust the total KEEP pool size to accommodate the new object.

set pages 999
set lines 92
 
spool keep_syn.lst
 
drop table t1;
 
create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;

In sum, there are two ways to identify tables and indexes for full caching in the KEEP pool.  We start by explaining all of the SQL in the databases looking for small-table, full-table scans. 

Sizing the KEEP Pool

Once the tables and indexes have been loaded into the KEEP buffer pool, the db_keep_cache_size parameter must be increased by the total number of blocks in the migrated tables.

The following script will total the number of blocks that the KEEP pool requires, insuring 100 percent data caching. The script doubles the total to allow for growth in the cached objects and multiple block versions within the data buffer. The DBA should run this script frequently to make sure the KEEP pool always has a DBHR of 100 percent.

Here is a handy shell script to compute the optimal size of the KEEP pool, or you could simply use the SQL inside a dbms_scheduler job.  
 
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=mon1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/managerset heading off
spool run.sql
select
' alter system set db_keep_cache_size = '||trunc(sum(s.bytes)*1.5)||' scope=both;'
from
   dba_segments s
where

   s.buffer_pool = 'KEEP';
spool off;
exit
!
@run.sql

 
Once the shell script is created, this crontab can be used to daily re-size the KEEP pool for all objects within the KEEP pool:
 
$ crontab -l


#**********************************************************
# Run the daily script to compute the optimal KEEP Pool size
#**********************************************************
00 06 * * * /xxxx/size_keep_pool.ksh > /tmp/size_keep_pool.lst

 
 
Here is the resulting command as it is executed.

alter system set db_keep_cache_size =1048765 scope=both;

This script outputs the Oracle parameter that resizes the KEEP pool for the next restart of the Oracle instance. The parameter is placed in the init.ora file. Oracle10g deprecates buffer_pool_keep is deprecated in favor of the db_keep_cache_size parameter.

Advanced KEEP Pool Candidate Identification

The KEEP pool is an excellent storage location for small-table, full-table scans. It can also be a good place to store data blocks from frequently used segments that consume a lot of block space in the data buffers. These blocks are usually found within small reference tables that are accessed through an index and do not appear in the full-table scan report.

The x$bh internal view is the only window into the internals of the Oracle database buffers. The view contains much detailed information about the internal operations of the data buffer pools. Both the number of objects in a specific type and the number of touches for that object type can be counted in the x$bh table. It can even be used to create a snapshot of all the data blocks in the buffer.

The hot_buffers.sql query shown below utilizes the x$bh view to identify all the objects that reside in blocks averaging over five touches and occupying over twenty blocks in the cache. It finds tables and indexes that are referenced frequently and are good candidates for inclusion in the KEEP pool.

-- You MUST connect as SYS to run this script
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

SELECT
   object_type mytype,
   object_name myname,
   blocks,
   COUNT(1) buffers,
   AVG(tch) avg_touches
FROM
   sys.x$bh a,
   dba_objects b,
   dba_segments s
WHERE
   a.obj = b.data_object_id
and
   b.object_name = s.segment_name
and
   b.owner not in ('SYS','SYSTEM')
GROUP BY
   object_name,
   object_type,
   blocks,
   obj
HAVING
   AVG(tch) > 5
AND
   COUNT(1) > 20;


The hot_buffers.sql script will only run on Oracle8i and subsequent versions. This is because the tch column was not added until Oracle 8.1.6.

The output from the hot_buffers.sql script is shown next. It identifies the active objects within the data buffers based on 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


The DBA must now decide whether the hot objects are to be segregated into the KEEP pool. In general, there should be enough RAM available to store the entire table or index. Using the example, if consideration is given to adding the page table to the KEEP pool, 104 blocks would have to be added to the Oracle db_keep_cache_size parameter.

The results from this script will differ every time it is executed because the data buffers are dynamic, and data storage is transient. Some DBAs schedule this script as often as every minute, if they need to see exactly what is occurring inside the data buffers.

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster