As a DBA, time to time, you may wish to add a hint to a query sent by a developer or a third party application. Actually this is possible by using SQL Patch. Essentially SQL Patch is the recommendation provided by SQL Repair Advisor. When you apply the patch, it directs optimizer to the way it suggested. This issue has been revealed by a Oracle blog, let's try it in a different way. Let's say you want to run the query below in parallel: 

select count(*) from stage

To add PARALLEL hint to the query with SQL Patch:

                         sql_text => 'select count(*) from stage',
                         hint_text => 'PARALLEL(stage,2)',
                         name  => 'test_parallel_patch');
end; /

When you run the query, you will see that it didn't run in parallel. To confirm you can run the query below and see no rows returned.

select px_maxdop, sql_id, sql_text
from v$sql_monitor
where sql_text = 'select count(*) from stage';

However if you check the execution plan, you will see the statement "SQL patch "test_parallel_patch" used for this statement" under the note section. So SQL Patch is applied though not run as accepted. There is a workaround for the problem. Display execution plan with outline option first:

explain plan for select count(*) from stage; select * from table(dbms_xplan.display(format=>'+OUTLINE'));
Outline Data
FULL(@"SEL$1" "STAGE"@"SEL$1")

Drop the SQL Patch you have created:

  DBMS_SQLDIAG.DROP_SQL_PATCH(name  => 'test_parallel_patch');

Recreate it as:

                         sql_text => 'select count(*) from stage',
                         hint_text => 'PARALLEL(@"SEL$1" "STAGE"@"SEL$1",2)',
                         name  => 'test_parallel_patch');
end; /

Notice that we changed table name with the one displayed in execution plan's Outline Data section. Run the query again and see that it runs in parallel, execution plan says:

- Degree of Parallelism is 2 because of
- SQL patch "test_parallel_patch" used for this statement    

This situation does not takes place if you use a hint without a table name, such as using PARALLEL(2) instead of PARALLEL(stage,2), or a hint not requiring a table name.