There are few situations where we know the execution plan of a particular query can be improved with use of proper hint, But if we are not allowed to change the application query in any way(we can’t even add a hint also),then how to achive this?

Example:

For the below query, we want to force it to use parallel 4 Hint execution plan, But without changing the query.

select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);

Currently the query is taking around 17 seconds.
Lets create a baseline for this query.

We can see two plans got loaded. Lets disable these existing plans.

Lets run the the same query with parallel hint and get the execution plan.

So for the query with parallel hint, you can get these two details from the above execution plan.

SQL ID: gq322uk9152yg
Plan hash value: 1244248794

Now load these two values to the existing sql handle id SQL_a868c91ef7af57ad

We can see a new plan has been enabled for this sql. This is the parallel hinted plan.

Lets check the explain plan of the orginal query and verify whether it is using the parallel hinted execution plan or not.

Now it is using the parallel hint execution plan, even if we haven’t used that hint in the query.