Oracle Scratchpad

May 19, 2010

Ignoring Hints

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

I’ve previously published a couple of notes (hereand here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote 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 ANSI 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 11.1.0.7 – 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"]

6 Comments »

  1. Interesting…and funny that you have a strategy of converting ANSI SQL to Oracle dialect when you encounter a problem…that’s the first thing I do when someone hands me any ANSI SQL. Over the past two days, I’ve been given three pieces of SQL, all of which were getting a suboptimal plan, but when converted to Oracle dialect – no other changes and they were all reasonably simple SQL – the plan improved considerably and the query ran in minutes, rather than days.

    Comment by Jeff Moss — May 20, 2010 @ 5:53 am GMT May 20,2010 | Reply

    • Jeff,

      I do have a strategy for translating ANSI to Oracle (since I know that ANSI sometimes is the problem) – but it’s an option I would only adopt if I were fairly sure that ANSI was the problem.

      I actually made two mistakes in my analysis in this case – the first one was that I didn’t consider the fact that it might be the SYS account that was causing the problem … and I should have thought of that before trying the rewrite. The second was rewriting the query correctly instead of writing a simplified variant of the ANSI query to see if it still failed, rather than spending time doing an exact rewrite into Oracle SQL.

      Rewriting queries is something I’m not keen on (even Oracle to Oracle), because it can be very hard to prove that the rewrite is logically equivalent to the original.

      Comment by Jonathan Lewis — May 23, 2010 @ 1:42 pm GMT May 23,2010 | Reply

  2. [...] Bugs in Oracle Database cause the hint to be lost (see Jonathan Lewis’ comment in the above forum post, and the examples on his blog – for example) [...]

    Pingback by Demonstration of Oracle “Ignoring” an Index Hint « Charles Hooper's Oracle Notes — July 26, 2010 @ 3:31 pm GMT Jul 26,2010 | Reply

  3. I have come across strange issue today regarding the ignoring global hints in remote queries.

    For instance if we look at below example..

    explain plan for
    INSERT  INTO TESTING tt
                      (ACP_APPL_ID
                       ,ACP_APPL_RECEIVED_DATE
                       ,ACP_APRV_AMT
                                         )
             SELECT /*+ USE_HASH(st.acct st.ext) */
                        ACP_APPL_ID
                       ,ACP_APPL_RECEIVED_DATE
                       ,ACP_APRV_AMT
                        FROM origmgr.acaps_acct_auto_d@infoprod st
              WHERE acp_appl_received_date BETWEEN to_date('13jun2012','ddmonyyyy') and to_date('25sep2012','ddmonyyyy')
                   and ACP_BRANCH_CODE  is not null;
    
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
    --------------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |                   |  1015 | 28420 | 25190   (1)| 00:05:03 |        |      |
    |   1 |  LOAD TABLE CONVENTIONAL | TESTING           |       |       |            |          |        |      |
    |   2 |   REMOTE                 | ACAPS_ACCT_AUTO_D |  1015 | 28420 | 25190   (1)| 00:05:03 | INFOP~ | R->S |
    --------------------------------------------------------------------------------------------------------------
    
    Remote SQL Information (identified by operation id):
    ----------------------------------------------------
    
       2 - SELECT /*+ OPAQUE_TRANSFORM */ "ACP_APPL_ID","ACP_APPL_RECEIVED_DATE","ACP_APRV_AMT","ACP_BRANC
           H_CODE" FROM "ORIGMGR"."ACAPS_ACCT_AUTO_D" "ST" WHERE "ACP_BRANCH_CODE" IS NOT NULL AND
           "ACP_APPL_RECEIVED_DATE">=TO_DATE(' 2012-06-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
           "ACP_APPL_RECEIVED_DATE"<=TO_DATE(' 2012-09-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing remote)
    

    we can see it completely ignored hash hint. I am accesing remote database view that's why i have specified globally.

    I am wondering is there any way to change this pattern.

    Comment by jayasankar — September 26, 2012 @ 1:56 pm GMT Sep 26,2012 | Reply

  4. I’ve not been able to find a method. I think that somewhere I’ve published a note that if the “remote SQL” is for a single table then hints for that table will be forwarded, but if the remote SQL includes a join then the hints disappear.

    A possible side effect of this that I haven’t yet tested is that SQL Baselines involving remote joins may fail to stabilise the execution plan, because basically an SQL Baseline is a set of hints. I’ve tried copying the outline (dbms_xplan.display(null,null,’outline’)) hints back into a fully remote SQL statement – like your select from a view – and found that even the “guaranteed” full set won’t work.

    Comment by Jonathan Lewis — September 27, 2012 @ 8:37 am GMT Sep 27,2012 | Reply

    • Thanks for your update. Yes for single table hints will be forwarded to remote site like you said.

      Comment by jayasankar — September 27, 2012 @ 9:52 am GMT Sep 27,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:

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