Hot blocks are another common cause of cache buffers chains latch contention. This happens when multiple sessions repeatedly access one or more blocks that are protected by the same child cache buffers chains latch. This is mostly an application issue. In most cases, increasing the number of cache buffers chains latches will do little to improve performance. This is because blocks are hashed to hash buckets and chains based on the block address and the number of hash buckets, and not the number of cache buffers chains latches. If the block address and the number of hash buckets remain the same, chances are those few hot blocks will still be covered by one cache buffers chains latch, unless the number of latches is drastically increased.
When sessions compete for the cache buffers chains latches, the best way to find out if you have a hot blocks situation is to examine the P1RAW parameter value of the latch free wait event. (Remember, in Oracle Database 10g this event is latch: cache buffers chains.) The P1RAW parameter contains the latch address. If the sessions are waiting on the same latch address, you have hot blocks. Based on the following example, there are hot blocks on chains covered by the 00000400837D7800 and 00000400837DE400 latches:
The next step is to see what blocks are covered by the latch. You should also capture the SQL statements that participate in the competition. This is because a cache buffers chains latch covers many blocks, and you can identify the hot blocks by the tables that are used in the SQL statements. In Oracle8i Database and above, you can identify the hot blocks based on their TCH (touch count) values using the following query. Generally, hot blocks have higher touch count values. However, bear in mind that the touch count is reset to 0 when a block is moved from the cold to the hot end of the LRU list. Depending on the timing of your query, a block with a 0 touch count value is not necessarily cold.
As previously mentioned, hot blocks are an application issue. Find out why the application has to repeatedly access the same block (or blocks) and check if there is a better alternative.
As for the workaround, the idea is to spread the hot blocks across multiple cache buffers chains latches. This can be done by relocating some of the rows in the hot blocks. The new blocks have different block addresses and, with any luck, they are hashed to buckets that are not covered by the same cache buffers chains latch. You can spread the blocks in a number of ways, including:
- Deleting and reinserting some of the rows by ROWID.
- Exporting the table, increasing the PCTFREE significantly, and importing the data. This minimizes the number of rows per block, spreading them over many blocks. Of course, this is at the expense of storage and full table scans operations will be slower.
- Minimizing the number of records per block in the table. This involves dumping a few data blocks to get an idea of the current number of rows per block. Refer to the “Data Block Dump” section in Appendix C for the syntax. The “nrow” in the trace file shows the number of rows per block. Export and truncate the table. Manually insert the number of rows that you determined is appropriate and then issue the ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK command. Truncate the table and import the data.
- For indexes, you can rebuild them with higher PCTFREE values, bearing in mind that this may increase the height of the index.
- Consider reducing the block size. Starting in Oracle9i Database, Oracle supports multiple block sizes. If the current block size is 16K, you may move the table or recreate the index in a tablespace with an 8K block size. This too will negatively impact full table scans operations. Also, various block sizes increase management complexity.
For other workarounds, if the database is on Oracle9i Database Release 2 or higher, you may consider increasing the _SPIN_COUNT value as discussed earlier. As a last resort, you may increase the number of hash buckets through the _DB_BLOCK_HASH_BUCKETS parameter. This practice is rarely necessary starting in Oracle8i Database. If you do this, make sure you provide a prime number—if you don’t, Oracle will round it up to the next highest prime number
Leave a Reply