KEEP Pool
A client was running Oracle on a system that had a 16 CPU Solaris
server with 8GB of RAM. The client complained that performance had
been degrading since the last production change. A STATSPACK top
five timed events report showed that more than 80 percent of system
waits were related to
db file scattered reads
.
A quick review of
v$sql
_plan using
plan9i.sql showed a
number of small-table full-table scans, with many of the tables not
assigned to the KEEP pool. Tables assigned to the
KEEP pool are denoted in the “K” column in the listing below):
Full table scans and counts
OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
---------- -------------------- ------------ - - -------- --------
APPLSYS FND_CONC_RELEASE_DISJS 39 N 44 98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 21 78,232
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2 66,864
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262 52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 322 50,174
APPLSYS FND_FILE_TEMP 0 N 544 48,611
APPLSYS FND_RUN_REQUESTS 99 N 98 48,606
INV MTL_PARAMETERS 6 N K 16 21,478
APPLSYS FND_PRODUCT_GROUPS 1 N 23 12,555
APPLSYS FND_CONCURRENT_QUEUES_TL 13 N K 10 12,257
AP AP_SYSTEM_PARAMETERS_ALL 1 N K 6 4,521
Rows fetched into the
db_cache_size from
full-table scans are not pinged to the Most-Recently-Used (MRU )
end of the data buffer upon re-reads. Running a
buf_blocks.sql script confirmed that the FTS blocks
were falling off the least-recently-used end of the buffer and had
to be frequently reloaded into the buffer.
Contents of Data Buffers
Number of Percentage
Blocks in of object
Object Object Buffer Buffer Buffer Block
Owner Name Type Cache Blocks Pool Size
------ -------------- ----------- --------- ------- ------- -------
DW01 WORKORDER TAB PART 94,856 6 DEFAULT 8,192
DW01 HOUSE TAB PART 50,674 7 DEFAULT 16,384
ODSA WORKORDER TABLE 28,481 2 DEFAULT 16,384
DW01 SUBSCRIBER TAB PART 23,237 3 DEFAULT 4,096
ODS WORKORDER TABLE 19,926 1 DEFAULT 8,192
DW01 WRKR_ACCT_IDX INDEX 8,525 5 DEFAULT 16,384
DW01 SUSC_SVCC_IDX INDEX 8,453 38 KEEP 32,768
Therefore, running a
buf_keep_pool.sql
script to reassign all tables that experienced small-table
full-table scans into the KEEP pool was required. The output
looks like this, and can be fed directly into SQL*Plus :
alter TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);
alter TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);
alter TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);
alter TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);
In less than one hour, the problem was fixed via more efficient
buffer caching and overall database performance more than doubled.
SEE CODE DEPOT FOR FULL SCRIPTS
