May 19, 2010

Ignoring Hints

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.

Related

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.

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.

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.