Currently, if you use a constant in a SQL statement in PL/SQL (e.g. to avoid the use of "magic" values, as is good coding practice), it gets treated as a bind variable when it's parsed and run. E.g.:
declare
k_constant_val varchar2(10) := 'X';
v_result varchar2(10);
begin
select dummy
into v_result
from dual
where dummy = k_constant_val;
end;
/
returns the following when viewed in V$SQL:
SELECT DUMMY FROM DUAL WHERE DUMMY = :B1
This could impact performance when running complex SQL, because the optimizer might not choose the most appropriate plan, because it doesn't know the bind variable is a constant value.
Instead, it would be good if PL/SQL could recognise the fact it's passing across a constant, and replace the bind variable with the value from the constant, e.g. for the example above, I'd want to see the following when looking at V$SQL:
SELECT DUMMY FROM DUAL WHERE DUMMY = 'UNCHANGING'
This would ensure that the optimizer has more information about the query (i.e. it doesn't have to do bind variable peeking on that value any more) and therefore could help performance.