The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.
The workaround in the bug suggested running this ALTER SESSION command.
alter session set "_optimizer_unnest_disjunctive_subq"= FALSE;
Rather than alter the whole session, we used the OPT_PARAM hint, making the subquery look something like this.