sql - Is there a way to force Oracle to change a query's plan without using hints?

Question

I have a query using wrong indexes. I can see that with the usage of index there is no easy way for oracle fetch the data.The query is framed by a vendor software, and cannot be changed, Is there a way to force oracle to change the explain plan without hints. Any help would be much appreciated.

Answer 1

There are at least 11 ways to control a plan without modifying the query. They are listed below roughly in the order of usefulness:

1. SQL Plan Baseline - Replace one plan with a another plan.
2. SQL Profiles - Add "corrective" hints to the plans. For example, a profile might say "this join returns 100 times more rows than expected", which indirectly changes the plan.
3. Stored Outline - Similar in idea to SQL Plan Baseline, but with less features. This option is simpler to use but less powerful and not supported anymore.
4. DBMS_STATS.SET_X_STATS - Manually modifying table, column, and index stats can significantly change plans by making objects artificially look more or less expensive.
5. Session Control - For example alter session set optimizer_features_enable='11.2.0.3';. There aren't always helpful parameters. But one of the OPTIMIZER_* parameters may help, or you may be able to change the plan with an undocumented hint or disabling a feature like this: alter session set "_fix_control"='XYZ:OFF';
6. System Control - Similar to above but applies to the whole system.
7. DBMS_SPD - A SQL Plan Directive is similar to a profile in that it provides some corrective information to the optimizer. But this works at a lower level, across all plans, and is new to 12c.
8. DBMS_ADVANCED_REWRITE - Change a query into another query.
9. Virtual Private Database - Change a query into another query, by adding predicates. It's not intended for performance, but you can probably abuse it to change index access paths.
10. SQL Translation Framework - Change a query into another query, before it even gets parsed. This can enable totally "wrong" SQL to run.
11. SQL Patch (dbms_sqldiag internal.i_create_patch) - Change a query into another query. Similar to DBMS_ADVANCED_REWRITE but it's undocumented and perhaps a bit more powerful.