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 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.
Oracle Scratchpad 
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 BST May 20,2010 |
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 BST May 23,2010 |
[…] 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 BST Jul 26,2010 |
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 BST Sep 26,2012 |
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 BST Sep 27,2012 |
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 BST Sep 27,2012 |