We encountered a performance issue where the same SQL ID with the same bind variable results in different execution plans due to being used by different applications.

Disabling this parameter fixes the issue:

alter session set "_optim_peek_user_binds"=false

Note: https://smarttechways.com/2019/10/11/explain-adaptive-cursor-sharing-bind-peeking-in-oracle/

Explain the parameter: _optim_peek_user_binds parameter in Oracle controls the behavior of bind variable peeking.

Bind Variable Peeking Process: When a SQL statement using bind variables is run for the first time, the Oracle optimizer checks the values of these variables to choose the best execution plan based on the actual values. This is called bind variable peeking.

_optim_peek_user_binds: This hidden parameter decides if the optimizer will check user bind variables. By default, it is set to TRUE, which means the optimizer checks the bind variable values the first time a SQL statement is executed.

Effects of the Parameter:

• TRUE: When set to TRUE, the optimizer uses the actual values of bind variables to create an execution plan. This can result in more efficient plans if the values are typical.
• FALSE: When set to FALSE, the optimizer ignores bind variable values and creates a general execution plan. This is helpful in environments where those values change significantly, as peeking could result in inefficient plans for some runs.

Use Cases:

• Performance Tuning: Changing this setting can improve SQL performance. For instance, if a query runs slowly because of bind variable peeking, setting _optim_peek_user_binds to FALSE may help.
• Consistency: Avoiding peeking at bind variables can result in more stable execution plans, particularly in environments with fluctuating data distributions.