Oracle Scratchpad

May 23, 2012

Logical tuning

Filed under: Hints,Ignoring Hints,Oracle,Performance,Tuning — Jonathan Lewis @ 6:22 pm BST May 23,2012

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 – which was the client version:

November 18, 2011


Filed under: Execution plans,Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 12:54 pm GMT Nov 18,2011

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

January 16, 2011

Ignoring hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 10:32 pm GMT Jan 16,2011

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”]


December 3, 2010

ANSI – argh

Filed under: ANSI Standard,CBO,Execution plans,Hints,Ignoring Hints — Jonathan Lewis @ 7:30 pm GMT Dec 3,2010

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

May 19, 2010

Ignoring Hints

Filed under: distributed,Hints,Ignoring Hints,Troubleshooting,Tuning — Jonathan Lewis @ 9:04 pm BST May 19,2010

I’ve previously published a couple of notes (here and here) about the use of the driving_site() hint with distributed queries. The first note pointed out that the hint was deliberately ignored if you write a local create as select (CTAS) or insert/select that did a distributed query. I’ve just found another case where the hint is ignored – this time in a simple select statement.

Try running an ordinary distributed query from the SYS account, and then try using the driving_site() hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from 2ANSI” to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.

I was running my queries between two databases using – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.

Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.

[Further reading on “ignoring hints”]

February 11, 2010

Ignoring Hints – 2

Filed under: CBO,Execution plans,Hints,Ignoring Hints — Jonathan Lewis @ 7:28 pm GMT Feb 11,2010

Here’s a little puzzle that someone sent to me a couple of days ago – it’s a case where the optimizer seems to be ignoring a hint.


October 2, 2009

Quiz Night

Filed under: Hints,Ignoring Hints — Jonathan Lewis @ 6:15 pm BST Oct 2,2009

Why is Oracle ignoring my hints ?
I have a table and want to count the rows, so here’s the query and execution plan I get on the first attempt:

select /*+ full(t) */ count(*) from t1 t;

| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT      |       |     1 |    79   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 47343 |    79   (2)| 00:00:01 |


June 1, 2009

Hints and Nulls

Filed under: CBO,Hints,Ignoring Hints,Indexing,NULL,Troubleshooting — Jonathan Lewis @ 7:16 pm BST Jun 1,2009

From time to time I check the site statistics to see if they give me any clues about why people are coming to blog – and recently I noticed that over the last year a particular referral from the OTN Database forum was had appeared fairly regularly – and it’s one that covers a small but significant optimizer detail that combines two crtical questions:  why is the optimizer not using my index and why is the optimizer ignoring my hint under the heading “Index hint does not work”.

I’ll leave you to read the thread – but the short answer is NULL.

A hint is illegal if using it could produce the wrong answer, and indexes where every column is nullable won’t necessarily reference every row in its table.

[Further reading on “ignoring hints”]

February 21, 2007

Ignoring Hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 9:04 pm GMT Feb 21,2007

Here’s a whimsical, but very telling, example of Oracle “ignoring” hints.

I have the following query, which includes a hint to use a specific index when visiting a certain table. It’s the primary key index, so has no issues relating to null values making the hint invalid – yet Oracle does not use this index. Has it ignored the hint ?


January 18, 2007

Using 10053

Filed under: CBO,Execution plans,Hints,Ignoring Hints,trace files,Troubleshooting — Jonathan Lewis @ 8:51 pm GMT Jan 18,2007

A little while ago I wrote a note about an anomaly that someone was seeing with the index_ss() hint. Since then, they forwarded me a 10053 trace file of the question.

I’ve posted brief analysis of it on the Wayback Machine archive of my old website, just to give you an idea of the sorts of things you can look out for when you’re trying to work out why Oracle seems to be ignoring a hint.

[Further reading on “ignoring hints”]

January 3, 2007

Hints and Guesses

Filed under: Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 8:45 pm GMT Jan 3,2007

If you visit this thread on, you will find a discussion about the index_ss() hint. It’s an interesting example of how (a) you could argue that Oracle is ignoring hints until (b) you realise that we really can’t tell what’s going on because we don’t know what a hint is supposed to do.


October 28, 2006

Taking a Hint

Filed under: CBO,Execution plans,Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 10:32 pm BST Oct 28,2006

It seems the word is gradually getting around the Oracle world that “hints” are really absolute directives to the optimizer that cannot be ignored – if they are correctly used.

Unfortunately the documentation on hints is far from complete, so it is very easy to produce examples that suggest that a hint has been ignored. Here’s one I came across recently in a note about 10g.


« Previous Page

Website Powered by