Symptom

You might experience a high number of buffer busy waits when using Oracle SecureFiles for BLOB storage.

Explanation

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.

Solution

To minimize buffer busy waits:

1. Upgrade your Oracle database to version 11.2.04 or later because earlier versions have more problems with SecureFiles.
2. Set the hidden Oracle parameter _securefiles_concurrency_estimate to 50 by using the following command on your Oracle system:

   alter system set "_securefiles_concurrency_estimate"=50

3. 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.