keep pool

8/10/2019 keep pool

1/4

Home

E-mail Us

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 support

Remote plans

Remote services

Application Server

Applications

Oracle Forms

Oracle Portal

App Upgrades

SQL Server

Oracle Concepts Software Support

Remote Support

Development

Implementation

Consulting Staff

Consulting Prices

Help Wanted !

Search BC Oracle Sites Search

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 full-tablescans.

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

According to Oracle documentation, A good candidate for a segment to put intothe KEEP pool is a segment that is smaller than 10% of the size of the DEFAULTbuffer pool and has incurred at least 1% of the total I/Os in the system . Moreconcisely, a small table that is in high demand is a good candidate for KEEPcaching.

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

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

Fortunately, it is relatively easy to locate the small-table full-table scans and cachethem in the KEEP pool. You can query the v$sql_plan view to see small-tablefull-table scans, and automatically cache them in the KEEP pool using the utilitiesfrom my book below.

Finding candidates for the KEEP Pool

Finding small-table, full-table scans is as easy as running this plan9i.sql script. Itwill give you a report showing the table sizes and the number of full scansexperienced by each table.

Sizing the KEEP Pool

Once the tables and indexes have been loaded into the KEEP buffer pool, thedb_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 growthin the cached objects and multiple block versions within the data buffer. The DBAshould run this script frequently to make sure the KEEP pool always has a DBHRof 100 percent.

Here is a handy shell script to compute the optimal size of the KEEP pool, or youcould simply use the SQL inside a dbms_scheduler job.

size_keep_pool.ksh

#!/bin/ksh# First, we must set the environment . . . .ORACLE_SID=mon1export ORACLE_SID

ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`

Using the Oracle KEEP pool

Oracle Tips by Burleson Consulting December 11, 2003 Don Burleson

Oracle TipsGot Questions?ORA 19815WarningRestart a FailedDaemon ProcessORA 20000 ORU10027 bufferoverflow limit of2000 bytesBEWARE of11gR2 UpgradeGotchas!

Page 1 of 4Using the Oracle KEEP pool

12.06.2013http://www.dba-oracle.com/oracle_tips_cache_small_fts.htm

8/10/2019 keep pool

2/4

Oracle Posters

Oracle Books

Oracle Scripts

Ion

Excel-DB

export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATH$ORACLE_HOME/bin/sqlplus system/managerdba_segments swhere

see code depot for full scripts s.buffer_pool = 'KEEP';spool off;[email protected]

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

$ crontab l

#**********************************************************# Run the daily script to compute the optimal KEEP Pool size#**********************************************************00 06 * * * /xxxx/yyyy/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 nextrestart of the Oracle instance. The parameter is placed in the init.ora file.Oracle10g deprecates buffer_pool_keep is deprecated in favor of thedb_keep_cache_size parameter.

Advanced KEEP Pool Candidate Identification

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

The x$bh internal view is the only window into the internals of the Oracledatabase buffers. The view contains much detailed information about the internaloperations of the data buffer pools. Both the number of objects in a specific typeand the number of touches for that object type can be counted in the x$bh table. Itcan 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 theobjects that reside in blocks averaging over five touches and occupying overtwenty blocks in the cache. It finds tables and indexes that are referencedfrequently and are good candidates for inclusion in the KEEP pool.hot_buffers.sql-- You MUST connect as SYS to run this scriptconnect sys/manager;set lines 80;set pages 999;column avg_touches format 999column myname heading 'Name' format a30column mytype heading 'Type' format a10column buffers format 999,999

SELECTobject_type mytype,object_name myname,blocks,COUNT(1) buffers,AVG(tch) avg_touches

FROMsys.x$bh a,dba_objects b,dba_segments s

WHERE



8/10/2019 keep pool

3/4

see code depot for full scripts a.obj = b.data_object_id

andb.object_name = s.segment_name

andb.owner not in ('SYS','SYSTEM')

GROUP BYobject_name,object_type,blocks,obj

HAVINGAVG(tch) > 5

ANDCOUNT(1) > 20;

The hot_buffers.sql script will only run on Oracle8i and subsequent versions. Thisis 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 activeobjects within the data buffers based on the number of data blocks and the numberof touches.

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

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

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

Get the Complete

Oracle SQL Tuning InformationThe landmark book "Advanced Oracle SQL TuningThe Definitive Reference" is filled with valuableinformation on Oracle SQL Tuning. This book includesscripts and tools to hypercharge Oracle 11gperformance and you can buy it for 30% off directlyfrom the publisher.



8/10/2019 keep pool

4/4

Burleson is the American Team

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

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

Errata? Oracle technology is changing and we strive to update our BC Oraclesupport information. If you find an error or have a suggestion for improving ourcontent, 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 - 2012

All rights reserved.

Oracle is the registered trademark of Oracle Corporation.


12 06 2013http://www dba oracle com/oracle tips cache small fts htm

keep pool

Documents