I’ll probably have to file this one under “Optimizer ignoring hints” – except that it should also go under “bugs”, and that’s one of the get-out clauses I use in my “hints are not hints” argument.
Sometimes an invisible index isn’t completely invisible.
A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:
I have two queries like this:
select * from emp where dept_id=10 and emp_id=15;
select * from emp where dept_id=10 and emp_id=16;
When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.
This, of course, is extremely unlikely – even if we assume that the two queries are more complex than the text shown. On the other hand you might, after a little thought, come up with the idea that perhaps the optimizer had done something really clever like join factorization (moving a join that’s common to the two parts of the UNION from inside to outside the UNION), or maybe there’s some really new trick the optimizer had played because a UNION ultimately requires a SORT UNIQUE, and the optimizer had chosen a different path that returned the data from each part of the UNION in sorted order to decrease the cost of that final sort.
In fact it turned out to be a lot simpler than that. The query looked more like this:
Here’s a live example demonstrating a point I’ve often made – you have to be very detailed in your hinting or Oracle will find a way to obey your hints and do the wrong thing. A recent posting on the OTN database forum gave use the following query and execution plan:
I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):
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 184.108.40.206 – 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 220.127.116.11 – 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 18.104.22.168 – which was the client version:
When I wrote a note last week about the fixes to the subquery factoring optimizer code in 22.214.171.124, 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:
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.
Footnote: you don’t have to demonstrate the method, just a brief outline of the idea will be sufficient.
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))
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”.
Here’s a quick tutorial in hinting, promped by a question on the OTN database forum.
The OP has a hash semi-join and Oracle appears to be ignoring a hint to use a nested loop:
Yes, finally, really ignoring hints – but it’s a sort of bug, of course.
Thanks to Timur Akhmadeev for telling us about bug 8432870 (you’ll need an account on MOS for the link) in his reply to Charles Hooper’s recent post.
In the upgrade from 9i to 10g there was a change in the “hint parser”. If you put a valid SQL keyword inside the hint delimiters (the note says /*+ */ but doesn’t mention the –+ alternative for specifying a hint, thought it’s probably still true there) when the keyword is not a valid hint – for example the word NOLOGGING which I have seen people use as if it were a hint – then Oracle will ignore all the hints.
Earlier versions of Oracle simply noticed that you had embedded something that wasn’t a valid hint, but that didn’t stop the parser from reading the rest of the hints correctly.
If the invalid hint is not a valid SQL keyword then there are no nasty side effects.
This might explain why I ran into an odd problem a little while ago when I added a comment to my hint list and found that the hints stopped working. I can’t remember the exact details any more but I think my comment was something along the lines of: “Do not … because …”, and this broke the hints until I changed it to “Don’t … because …”.
[Further reading on “ignoring hints”]
I have four simple (non-partitioned, non-clustered, not views, not object type – really I’m not trying to be cunning or devious here) heap tables, and write a query that joins them:
leading(t1 t2 t3 t4)
use_hash(t2) use_hash(t3) use_hash(t4)
t2.id2 = t1.id1
and t3.id3 = t2.id2
and t4.id4 = t3.id3
I’m not keen on ANSI standard SQL – even though it is, technically, the strategic option and even though you have to use it for full outer joins and partitioned outer joins.
One reason for disliking it is that it “separates join predicates from filter predicates” – a reason often given in praise of the syntax which, to my mind, claims a spurious distinction and introduces a mechanism that makes it harder to keep mental track of what’s going to happen as you walk through the join order. (I have to admit that I was temporarily fooled into thinking it was quite a nice idea – in an abstract sort of way.)