Oracle Scratchpad

January 16, 2011

Ignoring hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 10:32 pm UTC 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"]

 

6 Comments »

  1. Thanks for the highlight. Worth adding that, although it has a bug number, it has been deemed to be “Not a Bug”.

    Comment by Gary — January 16, 2011 @ 10:42 pm UTC Jan 16,2011 | Reply

  2. Could it be that only the hints AFTER the SQL keyword get ignored? I made the following quick example inserting the word NOLOGGING to different places in the hint:

    09:34:16 SQL> create table t1 (n number);

Tabelle wurde erstellt.

09:34:33 SQL> create table t2 (n number);

Tabelle wurde erstellt.

09:34:42 SQL> set autotrace traceonly explain

09:34:54 SQL> select /*+ NOLOGGING parallel(t1,4) parallel(t2,4) */ * from t1, t2;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 787647388

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | T2   |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


09:37:51 SQL> select /*+ parallel(t1,4) NOLOGGING parallel(t2,4) */ * from t1, t2;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3248347947

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |    26 |     5   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |     1 |    26 |     5   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    MERGE JOIN CARTESIAN  |          |     1 |    26 |     5   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     SORT JOIN            |          |       |       |            |          |  Q1,01 | PCWP |            |
|   5 |      PX BLOCK ITERATOR   |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   6 |       TABLE ACCESS FULL  | T1       |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |     BUFFER SORT          |          |     1 |    13 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |      BUFFER SORT         |          |       |       |            |          |  Q1,01 | PCWC |            |
|   9 |       PX RECEIVE         |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  10 |        PX SEND BROADCAST | :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |        | S->P | BROADCAST  |
|  11 |         TABLE ACCESS FULL| T2       |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------

09:37:53 SQL> select /*+ parallel(t1,4) parallel(t2,4) NOLOGGING */ * from t1, t2;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1997482048

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |    26 |     5   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |     1 |    26 |     5   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    MERGE JOIN CARTESIAN  |          |     1 |    26 |     5   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     SORT JOIN            |          |       |       |            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND BROADCAST  | :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   7 |        PX BLOCK ITERATOR |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |     BUFFER SORT          |          |     1 |    13 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  10 |      PX BLOCK ITERATOR   |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  11 |       TABLE ACCESS FULL  | T2       |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

09:38:08 SQL>

    Comment by Todor Botev — January 17, 2011 @ 9:01 am UTC Jan 17,2011 | Reply

    • Todor,
      the MOS-Bug is indeed named “Bug 8432870: NOLOGGING HINT IN 10G CAUSES HINTS AFTER IT TO BE IGNORED” – and there is an example similar to the one you provide.

      Comment by Martin Preiss — January 19, 2011 @ 8:38 am UTC Jan 19,2011 | Reply

  3. It’s getting difficult to avoid using SQL keywords. With 1,144 to choose from in 10.2.0.4 you could have a pretty good game of Scrabble with them.

    “SQL Keyword Scrabble” would be a pretty good team game for a conference. “Blank”,A,B,E,N,M,Q … anyone?

    Comment by David Aldridge — January 17, 2011 @ 11:18 am UTC Jan 17,2011 | Reply

  4. Jonathan,
    in his comment to Charles Hooper’s post Kerry Osborne gives the advice to separate hints from other comments – and this sounds convincing to me. On the other side I remember that Randolf Geist recently explained that “there are cases/environments where comments are stripped prior to execution” (with the example of PL/SQL). Do think it’s a good idea to separate hints from other comments? Or do you think it is save to add comments inside a hint (after the hint-Keywords)?

    Comment by Martin Preiss — January 19, 2011 @ 8:54 am UTC Jan 19,2011 | Reply

  5. Here is a little note about the hinting: http://valentinnikotin.com/2012/01/31/comments-in-hint/

    Comment by Valentin Nikotin — January 31, 2012 @ 2:19 pm UTC Jan 31,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Theme: Rubric. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 1,394 other followers