Oracle: Impacts of partition maintenance in RAC

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);