A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Oracle 10g introduced the QB_NAME hint that can come handy in case hints need to be applied to more complex statements, in particular when possibly multiple layers of views / subqueries are involved.Jonathan Lewis has a older blog post that describes more details.Just in case you wonder why sometimes apparently the QB_NAME hint - along with all other hints that refer to the assigned query block name - seems to be ignored: One possible reason is that it looks like there is an undocumented length limitation of the query block names that can be assigned - 20 characters seem to be the maximum possible (I haven't checked the effect of multi-byte database character sets).Consider this simple example:

drop table t1;

purge table t1;

That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 11.2.0.3 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

I have a small collection of postings where I’ve described anomalies or limitations in subquery factoring (the “with subquery”, or Common Table Expression (CTE) to give it the official ANSI name). Here’s another example of Oracle’s code not behaving consistently. You may recognise the basic query from yesterday’s example of logical tuning – so I won’t reprint the code to generate the data sets. This examples in this note were created on 11.2.0.2 – we start with a simple query and its execution plan:

Here’s a model of a problem I solved quite recently at a client site. The client’s query was much more complex and the volume of data much larger, but this tiny, two table, example is sufficient to demonstrate the key principle. (Originally I thought I’d have to use three tables to model the problem, which is why you may find my choice of table names a little odd). I ran this example on 11.2.0.2 – which was the client version:

When I wrote a note last week about the fixes to the subquery factoring optimizer code in 11.2.0.3, I finished with a comment about having more to say on the test case if I materialized the subquery. Today’s the day to talk about it. As a reminder, here’s the query, but with the /*+ materialize */ hint in place:

with subq as (
	select
	/*+ materialize */
		outer.*
	from
		emp outer
	where
		sal > 1000000
	and	outer.sal > (
			select
				avg(inner.sal)
 			from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
select	*
from	subq
;

Here are the three plans – from 10.2.0.5, 11.1.0.7, and 11.2.0.3 respectively:

Browsing a little history recently I came across a note I’d written about the new-style index hint. In that note I claimed that:

… the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way.

On reading this statement I suddenly realised that I hadn’t actually proved (to myself, even) that if I had the indexes (product_group, id) and (product_group, id, other_col) then a two-column hint forced Oracle to use the two column index in all (legal) circumstances.

So, tonight’s quiz – are there any edge cases, and what easy ways can you think of to prove (or disprove) the claim for the general case.

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
index(@sel$1 prd@sel$1(product_group  id))

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

As I’ve often pointed out, this blog isn’t AskTom, or the OTN forum, so I don’t expect to have people asking me to solve their problems; neither do I answer email questions about specific problems. Occasionally, though, questions do appear that are worth a little public airing, and one of these came in by email a couple of weeks ago. The question is longer than the answer I sent, my contribution to the exchange doesn’t start until the heading: “My Reply”.

Last week I find a very interesting thing about use_hash hint accidentally. That is when you have join two tables using unique column from one table and you have a equal predicate on that column, you cannot use hint to make them using hash join.  I know that it does not make sense to use hash join in this case because nested loop is the best way to do it. The point is why Oracle ignore the hint here.