Oracle Scratchpad

January 16, 2007

Full Hinting

Filed under: Execution plans,Hints — Jonathan Lewis @ 9:17 pm BST Jan 16,2007

This question came up some time ago on the comp.database.oracle.server newsgroup. It’s an interesting question, and I’m not sure I know the answer.

Is there a way to tell the optimizer to precisely follow the exact plan & access paths you want ? You can’t specify a hint to join a result set to a table, then the result set this join will yield with another table etc., so how can you deal with this ? If I have:

SELECT...
FROM    tab1, tab2, tab3, tab4
WHERE
        tab1.id = tab3.id
AND     tab3.id = tab2.id
AND     tab2.id = tab4.id
AND     {filter clauses};
;

How could I force the optimizer to first join TAB1 and TAB3 with a nested loop, then join this result to TAB2 using a hash etc etc. ??

For the general case I’m not sure if there is always a solution; but for this particular example, Oracle 10g makes it particularly easy to express:

/*+
        leading (tab1, tab3, tab2, tab4)
        use_nl(tab3)
        use_hash(tab2) swap_join_inputs(tab2)
        use_nl(tab4)
*/

The leading() hint allows you to specify the join order, and the use_nl(tab3) ensures we get a nested loop from tab1 to tab3. We then have to join to t2 because of the leading() hint, but the  swap_join_inputs() would make tab2 the build (first) table and the intermediate result the probe (second) table in the hash join. Finally we specify a nested loop for the join to tab4. (For 9i, just change the leading() hint to an ordered() hint, in this case, and swap the order of tab2 and tab3 in the from clause))

But even in 10g there are probably cases where you would have  to do things like introduce partial order through in-line views with no_merge hints, or subquery factoring with materialize hints to get the plan you want.

Unfortunately, one of the difficulties of exercising total control is that Oracle’s optimizer works very hard to produce “left-deep” trees; and sometimes we know that the best plan comes from a “bushy” tree – and it can be hard to produce a legal execution plan which generates partial results and still joins those results efficiently.

Ultimately there are a few dozen hints which exist but are not documented and a few dozen that exist but are not documented properly, and the list keeps growing – so it can be hard to figure out exactly how to force a particular execution plan to appear; it’s hard to know if there are even enough hints (yet) to describe every path we might want.

Footnote: Originally I had tabN in the query, and tN in the hints – which is why some of the comments have references to tN still.

14 Comments »

  1. Why are hints growing?

    This will get us to more (and more) “try this-try that”.

    I’m thinking if the ANSI join syntax can really help (Oracle or us?) selecting the right join order.

    Comment by Antonio — January 17, 2007 @ 8:40 am BST Jan 17,2007 | Reply

  2. Jonathan,

    can you give some more details on what you mean by “left-deep” and “bushy” trees?

    Comment by Graham — January 17, 2007 @ 9:48 am BST Jan 17,2007 | Reply

  3. Is the art of hinting not the fun of writing a procedural program in a declarative language?

    Comment by MaartenV — January 17, 2007 @ 11:18 am BST Jan 17,2007 | Reply

  4. Jonathan, this might have been asked before but here goes anyway:

    is it possible to know what “wide meaning” hints like FIRST_ROWS translate to in terms of final execution plan with “leading”, “use_nl”, and so on? As in: what has FIRST_ROWS really done in any given instance? Not just the dbms_plan output. 10053?

    Comment by Noons — January 17, 2007 @ 12:17 pm BST Jan 17,2007 | Reply

  5. “but the swap_join_inputs() would make t2 the build (first) table and the intermediate result the probe (second) table in the hash join.”

    Does that mean that use_hash(t) means that “use t as the probe table” in the hash join ?

    Comment by amit poddar — January 17, 2007 @ 12:31 pm BST Jan 17,2007 | Reply

  6. Hi,

    I tried to combine the 10132 trace (that shows hints in ‘outline data’ section) with the 10g dbms_sql_tune task that parses all possible execution plans for a query.

    So I have the trace with all possible execution plans and their outline information.
    It can give an idea of the hints required to have the desired execution plan, but outline data gives something like:

      /*+
        BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('10.2.0.1' )
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$58A6D7F6" )
          MERGE(@"SEL$1" )
          OUTLINE(@"SEL$2" ) 
          OUTLINE(@"SEL$1" )
          INDEX(@"SEL$58A6D7F6" "E"@"SEL$1" "IDX$$_07E70001" )
          INDEX(@"SEL$58A6D7F6" "D"@"SEL$1" ("DEPT"."DEPTNO" ))
          LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "D"@"SEL$1" )
          USE_NL(@"SEL$58A6D7F6" "D"@"SEL$1" )
        END_OUTLINE_DATA
      */
    

    and I had to translate it to the following hints: /*+ INDEX(E PK_EMP) INDEX (D (DEPT.DEPTNO)) LEADING (E D) USE_NL(D) */

    But I have no idea what means “IDX$$_07E70001″ – and, by the way, I didn’t know about the INDEX( ()) syntax
    Maybe you have more information about it.

    I think this can help having ‘full hinting’ for a query.

    Regards,
    Franck.

    Comment by Franck Pachot — January 17, 2007 @ 12:52 pm BST Jan 17,2007 | Reply

  7. Antonio,

    “Why are hints growing?”

    why is software complexity growing ?

    “I’m thinking if the ANSI join syntax can really help (Oracle or us?) selecting the right join order.”

    what do you mean by “the right” ?

    there is no “right join order”

    Comment by Sokrates — January 17, 2007 @ 2:49 pm BST Jan 17,2007 | Reply

  8. does

    strings `which oracle`|grep “\/\*\+” | grep “\*\/” | sed ‘s/\*\/.*//’ | sed ‘s/.*\/\*\+//’ | tr ‘ ‘ ‘\n’ | dd conv=ucase | sort -u

    contatin a complete list of all (documented and undocumented) Hints ?

    Comment by Sokrates — January 17, 2007 @ 3:09 pm BST Jan 17,2007 | Reply

  9. @Sokrates

    “there is no “right join order””

    Came on! :)

    I mean the “one that make your query fast”.

    I didn’t mean something like “left outer join” nor right nor full…

    I was talking about join T1 to T2 using a NL or T3 to T1 using hash…
    Hope it’s a bit more clear now.

    “why is software complexity growing ?”

    Only as an excuse for having more and more hints! ;-)

    The optimizer gets “smarter” at any new Oracle release and having more hints it’s like having more way to tweak the engine.
    It’s as good or bad as you good or bad your Oracle is configured, your statistics are gathered and so on…

    More and more I keep thinking to Oracle patterns…

    Bye! :-)

    Comment by Antonio — January 17, 2007 @ 4:17 pm BST Jan 17,2007 | Reply

  10. I wonder what the implications of this are for optimizer plan stability? Is it that the optimizer can achieve stability itself through the use of undocumented hints (or more complete “knowledge” of what the hints mean) but that it is very difficult for humans to achieve, or is it that plan stability is not absolute?

    Comment by David Aldridge — January 18, 2007 @ 2:16 am BST Jan 18,2007 | Reply

  11. I’m terrified of using hints in a production environment. Its seems to “conditional” based on the time and state of the data and database. Often data will change or the version of the db (even if just a patchset) and the hint is no long the best method.

    Comment by RobH — January 18, 2007 @ 2:21 pm BST Jan 18,2007 | Reply

  12. Antonio, I think the increase in hints is closely related to the increase in optimizer features. Every time the designers adds a new mechanism, they have to include a hint to force the mechanism to be used in those cases where the optimizer ought to use it but fails to. Another thought is that the “outline” dump that appears at the end of the 10053 needs to have a hint to describe the cases where the feature is relevant. I don’t really see ANSI join syntax helping very much – the optimizer still transforms the incoming query before optimising it, and you can only hint in the select clause.

    Graham , left deep and bushy – I’ll try to write up a note about that some time soon. [Update: now done.] Roughly speaking left-deep is what the optimizer tree looks like when a query has a single from clause with list of tables; bushy is what you get if you force Oracle to generate intermediate results.

    Noons, I think the only way an outsider could find out is to generate lots of scenarios with, and without, the first_rows hint, then compare the 10053 trace files to see what calculations were omitted when first_rows is invoked. Generating a complete set of scenarios is, of course, the tricky bit.

    Amit, the use_hash(tabX) hint tells the optimizer to use a hash join to join this table to the preceding result set. It doesn’t dictate the order at all. The swap_join_inputs(tabX) forces tabX to be the build (first) table in the join, the 10g hint no_swap_join_inputs(tabX) forces tabX to be the probe (second) table.

    Sokrates - If I’ve read your syntax correctly this won’t get all the hints, just the ones that happen to be used in the embedded SQL – and there are far more than that. I used to use strings on the binaries then search the text by hand for an “obvious” hint such as star_transformation, then check any text around it. This doesn’t work in 10g as the hints are now just reserved words, and don’t end up grouped together in the binary. [Update June 2009: In 11g you have the view v$sql_hint, listing the hints, the version they appeared in, and the version they got into stored outlines.]

    David - I’m fairly sure that the constant change in the optimizer means that some outlines won’t work as expected after an upgrade. I haven’t actually tested this, but I have a few easy examples that could demonstrate the principle and I will get around to testing them one day. However, it is possible that outlines are more stable than “ordinary” end-user hints, as there is some information in the outline tables (especially in 9i) that is not available through normal hints, and this may be enough to keep the outlines on track.

    RobH - “terrified” is a fairly good approach, though possibly just a little over the top. Hints are NOT a good thing in production for the reaons I gave in an earlier article. I always advise people to minimise their use of hints, document the cases properly, and check each one carefully on any upgrade.

    Comment by Jonathan Lewis — January 18, 2007 @ 8:09 pm BST Jan 18,2007 | Reply

  13. [...] his Oracle Scratchpad, Jonathan Lewis writes about hinting for optimization of plans and paths: “Ultimately there are a few dozen hints which exist but are not documented and a few dozen [...]

    Pingback by Pythian Group Blog » Log Buffer #28: a Carnival of the Vanities for DBAs — March 10, 2007 @ 2:48 am BST Mar 10,2007 | Reply

  14. Reg. hint swap_join_inputs() (changing order joined tables especially build & probe table in hash joins), how do you verify that oracle did what you asked it do? How do you validate if it(changing the order ) actually made a difference in performance? I mean which stat would show the difference?

    Comment by Dharmendra — March 17, 2008 @ 10:25 pm BST Mar 17,2008 | Reply


RSS feed for comments on this post.

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,161 other followers