Oracle comes up with some cool tricks for fixing poorly performing SQL, especially code to which you have no direct access. If you absolutely know a given hint will always work, the Oracle optimizer blog recently posted a method for “sticking” a hint into the statement when it is executed. I wonder how long the post will be up, as it is undocumented behaviour. Test and use at your own risk.
declare
l_sqlid varchar2(100) := '&sql_id';
l_sqltext clob;
begin
select sql_fulltext into l_sqltext from v$sql where sql_id = l_sqlid;
dbms_sqldiag_internal.i_create_patch(sql_text => l_clob, hint_text => 'BIND_AWARE',name => 'test_patch');
end;
/
The next time you execute this SQL, it should have a version with ‘Y’ for the IS_BIND_AWARE element in GV$SQL.