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!
|
|
|
|
| |
|
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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
