When Bind Variables Backfire:
Bind variables are placeholders in SQL statements that improve performance and enhance security. Instead of generating a new execution plan for each query, the database reuses the same plan for queries with identical structures, differing only in the values of the bind variables. This approach reduces the overhead of parsing and planning while also mitigating risks like SQL injection.
The main advantage of bind variables is their ability to enable cursor sharing in the library cache, reducing hard parses and helping to prevent ORA-4031 errors. However, a disadvantage is that crucial information, such as the actual values of bind variables, is hidden from the query optimizer, which can hinder its ability to generate optimal execution plans, this limitation applies only when the adaptive cursor sharing feature is not utilized which is enabled by default.
When a SQL statement is parsed, the database creates a cursor in the library cache. The cursor stores the parsed SQL statement, execution plan and metadata, which includes information about the bind variables, such as their names, positions and data types. The type and the size of bind variables matter for sharing the cursor.
Sometimes working with bind variables can present challenges, as certain situations may disrupt their intended optimization benefits. This can lead to an excessive number of child cursors being generated for reasons such as BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, and BIND_EQUIV_FAILURE. In such cases, the _cursor_obsolete_threshold parameter can help manage the situation by limiting the number of child cursors created. By setting this parameter to a lower value (default is 8192 in Oracle Database 19c), the impact can be mitigated until the underlying issue is resolved. For non-CDB environments, 1024 might be a good starting point. Refer to Mike Dietrich’s blog post (Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default) for more details.
These terms (BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, and BIND_EQUIV_FAILURE) appear as reasons in the v$sql_shared_cursor view. As of December 2024, in Oracle Database 23ai Edition, there are 64 distinct reasons for creating child cursors. These three reasons can easily be confused with one another, especially since all three involve the term “bind.”
Now I’ll try to explain what they mean, how they differ and provide examples to make them easier to understand.
I will use SH sample schema and SALES table on Oracle Database Version 23.5 running on Docker. You may refer to my blog post for “Install Oracle Database 23ai and Sample Schemas on Macbook M Series (Apple Silicon)” for creating a test environment.
BIND_MISMATCH :
Description : The bind metadata does not match the existing child cursor.
First, I will use a bind variable with a NUMBER type. The column data type is also NUMBER, so no implicit conversion is needed.
I will query the sales data for customers with a customer ID less than 100,000. There are a total of 918,843 sales records, out of which 916,039 sales belong to customers with cust_id lower than 100000 The selectivity for this condition is calculated as 916,039 / 918,843 = 0,996948336114004
Based on this low selectivity, the Cost-Based Optimizer (CBO) is expected to choose an Full Scan for optimal performance.
SQL> set feedback on sql_id
SQL> var bind_var number;
SQL> exec :bind_var := 100000;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL_ID: 580z1pv8ga9b5
...
916039 rows selected.
SQL> select sql_id, child_number, BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='580z1pv8ga9b5';
SQL_ID CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE BIND_EQUIV_FAILUR
________________ _______________ ________________ __________________________ _____________________
580z1pv8ga9b5 0 N N N
1 row selected.Second, I will use a bind variable with a VARCHAR2 type, while the column data type is numeric. An implicit conversion will take place, and the optimizer will create a new child cursor since the data type of the bind variable differs from the current available cursors of that sql statement stored in the library cache.
SQL> var bind_var varchar2(10);
SQL> exec :bind_var := '100000';
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL_ID: 580z1pv8ga9b5
...
916039 rows selected.
SQL> select sql_id, child_number, BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='580z1pv8ga9b5';
SQL_ID CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE BIND_EQUIV_FAILURE
________________ _______________ ________________ __________________________ _____________________
580z1pv8ga9b5 0 N N N
580z1pv8ga9b5 1 Y N N
2 rows selected.
Another child cursor is created which can be observed in v$sql_shared_cursor.
V$SQL_BIND_METADATA describes metadata related to bind variables. Bind metadata provided by the client will be checked from here.
SQL> SELECT * from v$sql_bind_metadata where address in (SELECT CHILD_ADDRESS FROM V$SQL WHERE sql_id='580z1pv8ga9b5');
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME CON_ID
___________________ ___________ ___________ _____________ ____________ ____________ _________
000000006D9FD228 1 2 22 0 BIND_VAR 3
0000000068AB53C8 1 1 32 0 BIND_VAR 3
2 rows selected.
The first query I executed used a bind variable where DATATYPE 2 (NUMBER). This query resulted in the creation of Child Cursor 0, as it was the initial cursor for this query.
For the second query, I used a bind variable with DATATYPE 1 (VARCHAR2), having a maximum length of 32. Since the data type of the bind variable differed from the first query, the database could not reuse the existing cursor. As a result, it created Child Cursor 1 to accommodate this new bind variable type and length.
BIND_LENGTH_UPGRADEABLE :
Description : Bind length(s) required for the current cursor are longer than the bind length(s) used to build the child cursor.
Up to now, I have queried the SH.SALES table using the bind_var variable with data types NUMBER and VARCHAR2(32). This time, I will run the same query but increase the upper limit of the VARCHAR2 data type length to 128.
Bind variables are classified into four size groups based on their length:
- Up to 32 bytes
- 33 to 128 bytes
- 129 to 2,000 bytes
- More than 2,000 bytes
SQL> var bind_var varchar2(128);
SQL> exec :bind_var := '123456789123456789123456789123456789123456789123456789';
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL_ID: 580z1pv8ga9b5
...
918843 rows selected.
SQL> select sql_id, child_number, BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='580z1pv8ga9b5';
SQL_ID CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE BIND_EQUIV_FAILURE
________________ _______________ ________________ __________________________ _____________________
580z1pv8ga9b5 0 N N N
580z1pv8ga9b5 1 Y N N
580z1pv8ga9b5 2 N Y N
3 rows selected.When it is checked from the v$sql_bind_metadata:
SQL> SELECT * from v$sql_bind_metadata where address in (SELECT CHILD_ADDRESS FROM V$SQL WHERE sql_id='580z1pv8ga9b5');
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME CON_ID
___________________ ___________ ___________ _____________ ____________ ____________ _________
000000006D9FD228 1 2 22 0 BIND_VAR 3
0000000068AB53C8 1 1 32 0 BIND_VAR 3
00000000732A49E8 1 1 2000 0 BIND_VAR 3
3 rows selected.Now, for the same query, we have three different child cursors. Imagine numerous SQL statements with bind parameters running in the background, each using inconsistent data type definitions or lacking standardized definitions on the application side. This inconsistency can quickly lead to the same SQL statement being associated with a large number of child cursors, significantly impacting both performance and memory usage.
BIND_EQUIV_FAILURE :
Description: The bind value’s selectivity does not match that used to optimize the existing child cursor.
In the first query, I retrieved the sales data for customers with a customer ID less than 100,000. The Cost-Based Optimizer (CBO) opted for a full table scan, peeking at the bind variable value of 100,000. Now, I will query the sales for customers with a customer ID lower than 3. There are 176 sales for customers with cust_id < 3, resulting in a selectivity of 176/918,843 = 0.000191545236781. In this case, an index scan appears to be a much more feasible decision.
I will execute the query with different bind values, which will result in additional child cursors being created as the predicate selectivity rate varies.
SQL> exec :bind_var:=3;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL> exec :bind_var:=3;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL> exec :bind_var:=5000;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL> exec :bind_var:=78000;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL> exec :bind_var:=50000;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
SQL> exec :bind_var:=97000;
SQL> SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var;
Child cursors:
SQL> select sql_id, child_number, BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='580z1pv8ga9b5';
SQL_ID CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE BIND_EQUIV_FAILURE
________________ _______________ ________________ __________________________ _____________________
580z1pv8ga9b5 0 N N N
580z1pv8ga9b5 1 Y N N
580z1pv8ga9b5 2 N Y N
580z1pv8ga9b5 3 Y N N
580z1pv8ga9b5 4 N N Y
580z1pv8ga9b5 5 N N Y
580z1pv8ga9b5 6 N N Y
580z1pv8ga9b5 7 N N YSql Plans:
SQL> select sql_id, child_number, plan_hash_value , is_shareable from v$sql WHERE sql_id='580z1pv8ga9b5';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE IS_SHAREABLE
________________ _______________ __________________ _______________
580z1pv8ga9b5 0 1550251865 N
580z1pv8ga9b5 1 1550251865 N
580z1pv8ga9b5 2 1550251865 Y
580z1pv8ga9b5 3 1875067431 N
580z1pv8ga9b5 4 1875067431 Y
580z1pv8ga9b5 5 1550251865 N
580z1pv8ga9b5 6 1550251865 N
580z1pv8ga9b5 7 1550251865 YAlthough there are two feasible execution plans, numerous child cursors are generated, constantly updating their selectivity statistics.
This reason one is by far the most troublesome in these three reasons. Many organizations remain cautious about using the Adaptive Cursor Sharing (ACS) feature. In its early days, the feature was a little bit plagued with bugs that made its behavior unpredictable. If left unchecked or unmanaged, ACS could rapidly consume shared pool memory (ORA-4031) and significantly impact CPU utilization, leading to performance degradation in a short time. When it comes to understanding ACS, Mohamed Houri’s blog stands out as an invaluable resource. I often refer to his insights and advice, as they provide practical solutions and deep technical understanding of ACS. His advice: By default, let Adaptive Cursor Sharing (ACS) and Extended Cursor Sharing (ECS) work without altering any related parameters. However, if performance issues arise, you should be able to identify that ACS is the root cause and solve it at the sql statement level.
To diagnose ACS-related issues, you can use the following query.
SQL> SELECT
sql_id,
COUNT(1)
FROM
gv$sql_cs_selectivity
GROUP BY
sql_id
ORDER BY
2 DESC;Bind Variables Gone Wrong: A Tale of Skewed Data and Nonsense Correlations
If you are not utilizing ACS, the optimizer loses access to valuable bind information. Without ACS, bind peeking occurs only during the first execution, and while the goal is to create a stable execution plan, your database’s performance stability becomes precariously dependent on the bind values of the first statement’s bind parameters.
I will use the example of gender data in the army to explain this. It’s a great illustration of skewed data. If you query information about women in the army, an index scan is optimal due to the smaller dataset. However, for men, a full table scan is more efficient since they form the majority. Now, imagine a critical application screen is first executed with skewed bind values. If the execution plan is decided based on those parameters, overall performance will suffer significantly for subsequent runs with the majority.
Another interesting example comes from Tom Kyte, as explained by Alex Gorbachev in “Oracle Database Administration from the Oak Table” book. This example, which I particularly liked, highlights Adaptive Cursor Sharing (ACS) and introduces the concept of nonsense correlation. It describes how seemingly unrelated factors can impact database performance due to the order of execution and bind variable peeking. When an execution plan is generated using an initial set of skewed bind values, such as values that target a small subset of data and favor an index scan, subsequent queries with different bind values may perform poorly if a full table scan would have been more efficient.
Mrs. Early Bird typically logs into the system early on Mondays and opens the main screen, triggering a key SQL statement that uses bind variables. Due to a weekend database instance bounce, all execution plans are cleared. When Mrs. Early Bird runs the statement first, Oracle’s bind variable peeking optimizes it for fast performance using a nested-loop join with an index range scan.
However, if she’s delayed (e.g., due to heavy Monday rains), a 9 AM report runs first, using different bind variable values better suited for a full table scan and hash join. This causes Oracle to create an inefficient execution plan for subsequent users, leading to performance issues.
The heavy Monday rains and performance problems form a “nonsense correlation,” illustrating how bind variable peeking and the order of SQL execution can impact database performance.
Yet Another Oracle Database Tool/Script : VERSION_RPT
Version_rpt is a function that can be used for investigating child cursors. Script’s latest version is available in the link.
The function generates a summary report from the v$sql_shared_cursor view, providing additional diagnostic details based on the reason code. It counts all versions with a ‘Y’ in any column and identifies those where all columns contain ‘N’. More information is available on High SQL Version Counts – Script to determine reason(s) (Doc ID 438755.1)
Here is the sample report for sql statement (580z1pv8ga9b5)
SQL> start version_rpt3_25.sql
SQL> SELECT * FROM TABLE(version_rpt('580z1pv8ga9b5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 13-dec-24 18:24
RDBMS Version :23.0.0.0.0 Host: 0bbd6422dac9 Instance 1 : FREE
==================================================================
Addr: 0000000076C03A78 Hash_Value: 3505726821 SQL_ID 580z1pv8ga9b5
Sharable_Mem: 336897 bytes Parses: 11 Execs:12
Stmt:
0 SELECT * FROM SH.SALES WHERE CUST_ID < :bind_var
1
Versions Summary
----------------
COLUMN_VALUE
--------------------------------------------------------------------------------
BIND_MISMATCH :2
BIND_EQUIV_FAILURE :4
LOAD_OPTIMIZER_STATS :1
BIND_LENGTH_UPGRADEABLE :1
Total Versions:7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 8192 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN_VALUE
--------------------------------------------------------------------------------
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
1550251865 7
1875067431 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
COLUMN_VALUE
--------------------------------------------------------------------------------
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
ISION,SCALE)
======== ======== =============== =============== ======== =============== =====
COLUMN_VALUE
--------------------------------------------------------------------------------
============
2 1 32 2000 1 Yes (,)
6 1 22 22 2 No (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 8 5 4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_EQUIV_FAILURE :
COLUMN_VALUE
--------------------------------------------------------------------------------
Details shown already.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for LOAD_OPTIMIZER_STATS :
No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_LENGTH_UPGRADEABLE :
Details shown already.
####
COLUMN_VALUE
--------------------------------------------------------------------------------
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3505726821, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
65 rows selected.
Final Thoughts:
Not using bind variables is not an option, but inefficient usage can sometimes lead to the creation of additional child cursors. In such cases, the root cause should be investigated, and an appropriate solution must be implemented. The version_rpt tool can also be used for this investigation.
For issues like BIND_MISMATCH or BIND_LENGTH_UPGRADEABLE, modifying the application code usually resolves the problem.
When dealing with child cursors caused by BIND_EQUIV_FAILURE, I first attempt to rewrite the SQL statement, if possible. For Adaptive Cursor Sharing (ACS)-related issues, since they are primarily tied to predicates, rewriting the SQL may not always resolve the problem because the same predicates are involved. In such cases, working with column histograms and extended statistics can also help. However, in most scenarios, the solution ends up with the addition of the /*+ NO_BIND_AWARE */ hint.
Hope it helps.
Osman’s DBlog 
Leave your comment