I tried to use explain plan window inside my plsql developer (I had to strip just the query out of the block)

This might actually be the real problem. Sure, Oracle compiles PL/SQL constants down to bind variables for reasons I still cannot determine, but when hard parsing a query (as done in first-time execution), Oracle performs bind peeking. That is, it uses the real value passed into the query to determine the optimal plan, which in this case would be the constant you want to optimize for anyways.

The issue is that EXPLAIN PLAN FOR does not engage in bind peeking, so you won't see the optimal plan for the constant you want. I could not actually find official documentation to this end, but here's an SO answer stating this, and of course you can run a test for yourself (I used a bind variable on a column where 99.99% of the values are the same and EXPLAIN PLAN went for a full-table scan, but executing it for real passing in the 0.01% value into the bind variable hard parsed to a plan using the index on the column).

As such, PL/SQL constants compiling down to bind variables should be a rare problem in practice, though you may run into it if you run a query with variable parameters but nonetheless provide a constant for a common/default parameter. I cannot find a direct fix, but there are four workarounds I've looked into, each with their own drawbacks (and aren't already covered in the question or other answers).

Note: these aren't necessarily directly related to your specific query, I had a similar issue on a query in my DB and my goal was to avoid using magic numbers while still having a performant query. This was surprisingly difficult to research so I figured I'd post my findings in case others are having similar issues (though in my case it turned out to be a stale stats issue...).

The first two workarounds accept the bind variable compilation and work on improving the performance regardless, while the last two are alternative ways of encoding the constant:

1. Make the query bind-aware. Assuming a default set-up, you'll need to set optimizer_adaptive_statistics to TRUE. This effectively instructs Oracle to monitor queries with bind variables to identify queries that are sensitive to bind values. A BIND_AWARE hint can be added on queries known to be problematic to pre-identify to Oracle bind-value dependent queries.

   • Drawbacks: This is a DB-level parameter which will impose a performance penalty as Oracle monitors bind-sensitive queries. It is not recommended for OLTP systems. Additionally, it generally requires the bind value to be used in an equality or range WHERE clause.

2. Add an optimizer hint (or several). My approach for this would be to run an EXPLAIN PLAN on the query with desired literal values substituted, take notes of which indexes or joins were used, and add these as hints to the query with PL/SQL constants. A less heavy-handed approach might be negative hints to avoid a known problem, eg. NO_USE_NL to prevent a nested loops join.

   • Drawbacks: The usual ones with using hints, eg. forcing the optimizer into a particular plan (or narrowing the options) which may not be long-term advantageous or work well with bind values other than the constant.

3. Use dynamic SQL. Something like: EXECUTE IMMEDIATE 'SELECT ' || my_constant || ' FROM DUAL' INTO v_my_id;

   • Drawbacks: The usual ones with dynamic SQL, like a performance hit & SQL injection (depending on type of my_constant, should be safe with integer though)

4. (Mis-)use conditional compilation (CC) flags. You can encode my_constant as a CC flag using ALTER SESSION SET plsql_ccflags = 'my_constant:1'. In the PL/SQL package, it can be used with the syntax $$my_constant. Then, compile.

   • Drawbacks: Not at all recommended for production. If the package gets recompiled, then it risks my_constant being undefined unless it happens to be recompiled in the same session (ALTER SYSTEM can make it stick more, but is still not recommended). Additionally, this only works with TRUE, FALSE, NULL and PLS_INTEGER-type literals.

AI disclosure: Copilot did alert me to the bind-aware workaround. It then either mostly hallucinated or told me stuff I already knew.