Oracle Performance and Engineering

Good news - Tom Kyte has started a blog.

There've been many situations where a view is hinted for performance. That view, then, is reused by many a query. For cases when the said view contains a "global" hint like "RULE" -- the whole query - unless otherwise hinted - assumes the hint!

There're some other subtleties in the hint 'workflow' (i.e., how to find the scope of a hint) -

(1) QUERY A (hinted)
UNION
QUERY B (not hinted)

The hint will apply only with the first part (if at all - Oracle could merge both together and do a single statement executed).

(2) A hint can also be applied against a "query block" after finding the system generated query block name from the explain plan. A good example of query block could be -

select ename, mgr_name from emp
where salary =
(select emp_type, max(salary) from emp group by emp_type)

The last line could be a query block and will be system-named.

Here's an example of applying hint to a query block.

Query blocks can also be named with the hint QB_NAME.

(3) Hints do not propagate inside a complex view.

(4) For "optimizer goal" hints - the query level hint is used. The hint in the view is ignored.
i.e., if a query is hinted FIRST_ROWS and the view ALL_ROWS -- optimizer will try to execute the query in FIRST_ROWS plan.

(5) If top-level query does not have a goal hint - the goal hint from the view is applied against the whole query.

(6) With non-mergeable views optimization goal hints inside the view are totally ignored.
For such views the access path and join hints are preserved.

Nonmergeable views are basically like stand-alone tables. Oracle will have to materialize the results of the view before joining it to other data sources.

(7) FIRST_ROWS hint is ignored in all aggregate or set operations like UNION, DISTINCT, ORDER BY. This is easy to understand since for such operations as GROUP BY the full dataset have to be materialized before returning the asked data.

(8) RULE hint is unsupported!

(9) INDEX_SS is a hint to use "index skip scan"

(10) Sometimes for OLTP operations HASH join needs to be avoided. USE_NO_HASH coupled with USE_NL_WITH_INDEX hint works wonder in those cases.

Tomorrow I will show a real-life example where a RULE hint inside a (non-mergeable) view was killing the performance of a query that called the view.