Problem: Excessive
gc cr block 2-way or
db file sequential read waits against
TABPART$ during an insert after adding/dropping a partition
in another instance on a table with 10,000 partitions.
Analysis: Rather surprisingly, the waits aren't for the
TABPART$ blocks that needed to be reloaded but the segment header blocks of all the partitions. These were being loaded in CR mode during query parsing.
After changing the table partitioning it make sense that Oracle would be flushing the TABPART$ information for that table from the library cache of the other instances. The libarary cache on the instance performing partition maintenance doesn't get purged as this is updated as part of the operation. On other instances, the next operation on that table then needs to re-populate the library cache from information in
TABPART$ (which it does for ALL partitions even if only one is required). As part of reading from TABPART$, the segment headers of all partitions need to be inspected (not quite sure why). For 10,000 partitions, there are at least 10,000 physical reads (1 minute @ 6ms per read) or block transfers (5 to 15 seconds @ 0.5 to 1.5ms per transfer) required.
Once the library cache is re-loaded, subsequent statements are fast (until the next partition maintenance operation).
The flush of partition information from the library cache cannot be avoided.
The wait time can be reduced by ensuring the blocks are available on one instance in
current (CUR) mode. Segment headers can't be retained in a KEEP pool, so the only way to make them available is to load them on an idle instance.
The wait time can also be reduced by pre-loading the segment headers in
current mode on all instances impacted, however these blocks will not be frequently accessed hence may quickly drop out of the cache.
Loading segment header blocks in
current mode is necessary as blocks in
consistent (CR) mode cannot be re-used.. Segment header block can reside in multiple instances in
current mode at the same time.
When the segment headers are loaded as part of the
TABPART$ query, they are loaded on
consistent mode. Forcing them into
current mode can only done by retrieving rows from each of the partitions. This can be done using a FULL TABLE SCAN of the table with the SAMPLE option with a small number. (If anyone knows a better way that doesn't require parsing 10,000 statements with partition clauses or bind/executes, I'm all ears.) This operation needs to be run twice, as the first time the segment headers are retrieved in
consistent mode.
Workaround/Solution
1. Use an IDLE node to hold current copies of segment header blocks
2. Pre-load segment header blocks on all active instances
Supporting Queries
To inspect the segment header blocks
col object_name for a10
col subobject_name for a10
col status for a5
select file#,block#,class#,bh.status, object_name, subobject_name
from v$bh bh
join dba_objects obj
ON (objd = data_object_id)
where object_name in ('TEST')
and class# = 4
order by object_name, subobject_name
/
To load segment header blocks in current mode
select /*+ FULL(test) */ count(*) from test sample (0.00001);
