Implement the KEEP Pool for Small-table, Full-scanned
Tables
I
worked on a database just last month in New Zealand (running
9.2.0.4) that had a 16 CPU Solaris server with 8GB of RAM. The
complaint was that performance had been degrading since the
last production change. A STATSPACK top five timed events
report showed that over 80 percent of system waits related to
“db file scattered reads.” A quick review of v$sql_plan
using plan9i.sql showed lots of small-table, full-table
scans, with many of the table not assigned to the KEEP pool
(as denoted by 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
As
you may know, 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. Running my 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
In
this case, I ran my buf_keep_pool.sql script to
reassign all tables that experienced small-table, full-table
scans into the KEEP pool. The output looks like this, and can
be fed directly into SQL*Plus:
