Troubleshooting: High number of busy waits when using Oracle SecureFiles
You might experience a high number of buffer busy waits when using Oracle SecureFiles for BLOB storage.
An Oracle buffer busy wait occurs when a session cannot access a database block in the buffer cache because another session is either reading the block into the buffer or changing the buffer.
To minimize buffer busy waits:
- Upgrade your Oracle database to version 11.2.04 or later because earlier versions have more problems with SecureFiles.
- Set the hidden Oracle parameter
_securefiles_concurrency_estimateto 50 by using the following command on your Oracle system:
alter system set "_securefiles_concurrency_estimate"=50
- To reduce the number of concurrent requests, partition the table by using a composite range hash partition that is based on the pxCreateDateTime and pzInskey columns. Refer to your Oracle documentation for more information about creating a hash partition.