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:

begin
     dbms_sqldiag_internal.i_create_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
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "STAGE"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Drop the SQL Patch you have created:

begin
  DBMS_SQLDIAG.DROP_SQL_PATCH(name  => 'test_parallel_patch');
end;
/

Recreate it as:

begin
    dbms_sqldiag_internal.i_create_patch(
                         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:

Note
--------
- 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.