The issue is that to use a SQL Patch to hint a statement, it seems that you have to be very careful to use the complete hint specification.

The complete hint specification is an ugly beast and should be considered a strong indicator that you really want to think twice before doing any hinting, and especially as Oracle versions march onwards and the Optimizer gets more complicated, the complete hint specification becomes ever more important.
See Jonathan Lewis’s How to Hint post for a good illustration.

Bear in mind that the above is a test case only and consider what I said in the original article about what I consider to be the difference between a SQL Patch and a SQL Plan Baseline is and the different use cases.

Thanks for the comment.
I suppose it’s not completely surprising that append would not work.

But there’s definitely more to find out in this area, for example can we use something like v$sql_hint to determine what can and can’t be injected via a patch? Not obviously so.

I’ve another one for you courtesy of Jinwen Zou – result_cache.
There’s a hint that you might think might be reasonable to inject into third party application code but you don’t seem to be able to do it with a sql patch.

First, remember that the append hint (direct path insert) works only with insert/select before 11gR2 and not with insert/values (: bind). In 11gR2, there is a new hint (append_values) that allows insert /values (:bind) to be done via a direct path

Second, there are many situations where the append hint is silently ignored such as in the presence of trigger or foreign key constraint on the target table.

Consequently, I can’t work out the utility of using SQL patch to force a direct path during insert. You have to hard code the append hint in the insert and Oracle will always obey it when there are no situations such those indicated above impeaching the direct path to happen

You do not really need complete hint specification, you just need to specify the query block for the hint, which is understandable, b/c oracle has no idea where in the query this hint suppose to appear – you can have multiple selects , views , etc…

So if you’d use “HINT(…)” directly in the statement you’ll need to use “HINT(@ …)” in sql patch or profile definition.