||Oracle Tips by Burleson
Oracle keep pool caching
Oracle developed the KEEP pool in order to
fully cache blocks from frequently accessed tables and indexes in a
separate buffer. When determining the size of the KEEP pool, the
number of bytes comprising all tables that will reside in the KEEP
area must be totaled. This will insure that the KEEP buffer is large
enough to fully cache all the tables that have been assigned to it.
Oracle9i requires that a table only reside in
a tablespace of the same block size as the buffer assigned to the
table. For example, if the DEFAULT buffer is set at 32K, the alter
command below would not work if the customer table resides in a 16K
tablespace. Recall that the DEFAULT, KEEP, and RECYCLE designations
only apply to the default block size; KEEP and RECYCLE buffers cannot
be assigned different sizes than the default db_block_size.
alter table CUSTOMER storage (buffer_pool
The whole point of the KEEP pool is to always
have a data buffer hit ratio of 100 percent. The block size of the
KEEP pool is not important because all blocks, once loaded, will
remain in RAM memory. A KEEP pool might be defined as a 32K block size
because a large RECYCLE buffer was needed to improve the performance
of full-table scans.
CAUTION: Selecting tables for the KEEP pool is
an iterative process. These reports include only SQL that happens to
be in the library cache at the time of the report.
We emphasize that since our goal for the data
buffer hit ratio of the KEEP pool is 100 percent, each time a table is
added to KEEP, the number of blocks in that table must also be added
to the KEEP pool parameter in the Oracle file.
Besides the full-table scan report, we can
generate a report that shows every index access within SQL currently
residing in the library cache.
The script output below shows how Oracle uses
indexes to access Oracle tables (Listing 3.4).
code depot for full scripts
Index range scans and counts
OWNER TABLE_NAME INDEX_NAME TBL_BLOCKS NBR_SCANS
--------- ------------ -------------------
SYS JOB$ I_JOB_NEXT
SYS OBJ$ I_OBJ2
SYS ACCESS$ I_ACCESS1
SYS DEPEN I_DEPENDENCY1
SYS IDL_SB4$ I_IDL_SB41
SYS IDL_UB1$ I_IDL_UB11
SYS IDL_CHAR$ I_IDL_CHAR1
SYS IDL_UB2$ I_IDL_UB21
SYS ARGUMENT$ I_ARGUMENT2
SYS OBJAUTH$ I_OBJAUTH1
Index unique scans and counts
OWNER TABLE_NAME INDEX_NAME
--------- -------------------- --------------------
SYS C_OBJ# I_OBJ#
SYS C_COBJ# I_COBJ#
SYS C_FILE#_BLOCK# I_FILE#_BLOCK#
SYS C_USER# I_USER#
SYS PROCEDUREJAVA$ I_PROCEDUREJAVA$
SYS SMON_SCN_TO_TIME SMON_SCN_TO_TIME_IDX
SYS C_TS# I_TS#
Listing 3.4 – Index access methods and counts
for current SQL
Now that we can identify and locate
small-table, full-table scans, let’s see how we can quickly move small
tables and indexes into the KEEP pool. We will use a data dictionary
query to automatically generate KEEP and RECYCLE syntax.
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
shares their personal arsenal of Oracle data dictionary
scripts in this comprehensive download. With decades of
experience using Oracle monitoring scripts and Oracle tuning
scripts, we shares our secrets for navigating the Oracle
Packed with more than 680 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.