SQL Developer SQL Tuning Advisor not all variables bound

Question

I'm trying to use the SQL tuning advisor with SQL Developer (versions 4.1.3.20 and 17.2.0.188). Queries using more than one instance of a bind variable, for example:

select * from dual where :one = :one;

are causing an error in the SQL Tuning Advisor:

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-01008: not all variables bound

-------------------------------------------------------------------------------

Is there a workaround or some other method to get the same SQL tuning info for queries that have multiple instances of one or more bind variables?

Answer 1

Not sure why you want to use ":one = :one", you can replace it as "1 = 1", or add one more variable assigned by the same value, select * from dual where :one = :two;

Answer 2

Basically, the Tuning Advisor binds by position (not by name). So the workaround would be to rename all the repeated variable instances. Adding a numbered suffix would be enough. In your example, you would have to name your variables :one and :one_1.

select * from dual where :one = :one_1;

I know, it's not ideal, but it should work.