Constants being used as bind variables
The explain plan generated at run time for this query is very poor.
When I take this code and replace two of the bind variables with the literal values, the explain plan greatly improves.
The literal values are referenced in the cursor as package constants (from a different PL/SQL package).
Eg:
my_table_alias.my_column_value = my_other_package.my_constant_value;
as opposed to
my_table_alias.my_column_value = 3';
I'm a bit surprised that Oracle is passing these into the CBO as bind variables, when their values cannot vary (barring recompilation of the referenced package).