PlsqlBindBucketWidths
Specifies the rounding size to use while binding the width of elements in a collection bind.
While executing PL/SQL statements, the Oracle database maintains a cache of PL/SQL statements in the shared SQL area, and attempts to reuse the cached statement if the same statement is executed again. Oracle's matching criteria required that the statement texts be identical, and also requires that the bind variable data types match. Unfortunately, the type match for strings is sensitive to the exact byte size specified, and for collection bindings is also sensitive to the number of elements in the collection. Since mod_plsql binds statements dynamically, the odds of hitting the shared cache are low and it may fill up with near-duplicates and lead to contention for the latch on the shared area. This parameter reduces the effect by bucketing bind widths to the nearest level.
All numbers specified should be in ascending order. After the last specified size, subsequent bucket sizes will be assumed to be twice the last one.
The last bucket width must be equal to or less than 4000. This is due to the restriction imposed by OCI where array bind widths cannot be greater than 4000
- Syntax:
- PlsqlBindBucketWidths [number multiline]
- Default:
- 32,128,1450,2048,4000
- Example:
- PlsqlBindBucketWidths 40
- PlsqlBindBucketWidths 400
- PlsqlBindBucketWidths 2000
Tips for PlsqlBindBucketWidths
The defaults should suffice for most PL/SQL applications
This parameter is relevant only if you are using procedures with array parameters and passing varying sizes of parameters to the procedure
To see if this parameter needs tweaking, check the number of versions of a sql statement in the sql area
Consider using Flexible Parameter Passing to reduce the problem
In older versions, this parameter was called bind_bucket_widthsc