Oracle Scratchpad

February 10, 2014

RAC Plans

Filed under: Execution plans,Hints,Oracle,RAC,Troubleshooting — Jonathan Lewis @ 1:12 pm GMT Feb 10,2014

Recently appeared on Mos – “Bug 18219084 : DIFFERENT EXECUTION PLAN ACROSS RAC INSTANCES”

Now, I’m not going to claim that the following applies to this particular case – but it’s perfectly reasonable to expect to see different plans for the same query on RAC, and it’s perfectly possible for the two different plans to have amazingly different performance characteristics; and in this particular case I can see an obvious reason why the two nodes could have different plans.

Here’s the query reported in the bug:

SELECT /*+ INDEX(C IDX3_GOD_USE_LOT)*/
   PATTERN_ID, STB_TIME
    FROM mfgdev.MTR_AUTO_GOD_AGENT_BT C
   WHERE 1 = 1
     AND EXISTS (SELECT /*+ INDEX(B IDX_MTR_STB_LOT_EQP)*/ 1
            FROM MFGDEV.MTR_STB_BTH B
           WHERE B.PATTERN_ID = C.PATTERN_ID
             AND B.STB_TIME = C.STB_TIME
             AND B.ACTUAL_START_TIME < SYSDATE
             AND EXISTS (SELECT /*+ INDEX(D CW_LOTID)*/
                   1
                    FROM F14DM.DM_CW_WIP_BT D
                   WHERE D.LOT_ID = B.LOT_ID
                     AND D.SS = 'BNKI'));

See the reference to “sysdate”. I can show you a system where you had a 15 minute window each day (until the problem was addressed) to optimize a particular query if you wanted a good execution plan; if you optimized it any other time of day you got a bad plan – and the problem was sysdate: it acts like a peeked bind variable.

Maybe, on this system, if you optimize the query at 1:00 am you get one plan, and at 2:00 am you get another – and if those two optimizations occur on different nodes you’ve just met the conditions of this bug report.

Here’s another thought to go with this query: apparently it’s caused enough problems in the past that someone’s written a couple of hints into the code. With three tables and two correlated subqueries in the code a total of three index() hints is not enough. If you’re going to hard-code hints into a query then take a look at the outline it generates when it does the right thing, and that will tell you about the 15 or so hints you’ve missed out. (Better still, consider generating an SQL Baseline from the hinted code and attaching it to the unhinted code.)

5 Comments »

  1. Many programers, DBA etc. still do not understand why 3 hints is not enough in query like this. I’m also big fan of having at least two hints for each table in query (one for join method and one for access path) + join order and some more details like “swap join inputs”. However SQL Baseline is suitable only for “hot fix” to application code. It is very difficult to keep further changes to application code with SQL baselines, in some situations even not possible. Moreover, SQL baseline is quite often not able to reproduce execution plan for more complex queries.
    I always suggest to generate SQL baseline as a hot fix and adding proper hints to code for next application update

    Comment by Pavol Babel — February 10, 2014 @ 10:08 pm GMT Feb 10,2014 | Reply

    • Pavol,

      I’m a little torn between the options. I used to be very keen on using the outline to supply the hints but writing the “legal” hints into the code, then working out which of the original hints (like push_subq) still needed to be in the production hint set; over time, though, I’ve become more enthusiastic about the SQL Baseline with the occasional (rare) lapse when the Baseline hasn’t reproduced the required plan.

      Once the optimizer needs assistance to do the right thing with a query the proper documentation and management becomes very important – and that includes a statement and justification of the expected execution path – so I’m not going to be dogmatic about whether you get there by using a Baseline or by hinting; it’s just that baselines usually work and it often takes far too long time to make any change to a production system.

      Comment by Jonathan Lewis — February 11, 2014 @ 9:53 am GMT Feb 11,2014 | Reply

      • Jonathan,

        I’m not going to be dogmatic too, however the real life shows sql statement is fixed by Baseine, however next sql statement changed is not reported by developer and Baseline is recreated too late (after next performance issue in production). Still not able find sataisfactory methodology for developers to keep code consistent with Baselines

        Comment by Pavol Babel — February 12, 2014 @ 7:08 pm GMT Feb 12,2014 | Reply

  2. […] last Jonathan Lewis post on RAC Plans which finished by the following […]

    Pingback by How to attach a hinted SPM baseline to a non hinted sql query? | Mohamed Houri’s Oracle Notes — February 11, 2014 @ 8:37 am GMT Feb 11,2014 | Reply

  3. Jonathan,

    I have shown here below an example on how to generate an SQL Baseline from the hinted code and attach it to the unhinted code

    http://hourim.wordpress.com/2014/02/11/how-to-attach-a-hinted-spm-baseline-to-a-non-hinted-sql-query/

    Best regards

    Mohamed

    Comment by hourim — February 11, 2014 @ 8:45 am GMT Feb 11,2014 | 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:

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,267 other followers