Here’s an example that appeared on the Oracle Developer Community forum about a year ago that prompted me to do a little investigative work. The question involved a distributed query that was “misbehaving” – the interesting points were the appearance of the /*+ rule */ and /*+ driving_site() */ hints in the original query when combined with a suggestion to address the problem using the /*+ materialize */ hint with factored subqueries (common table expressions – CTEs), or when combined with my suggestion to use the /*+ no_merge */ hint.
If you don’t want to read the whole article there’s a tl;dr summary just before the end.
The original question was posed with a handful of poorly constructed code fragments that were supposed to describe the problem, viz:
select /*+ DRIVING_SITE (s1) */ * from Table1 s1 WHERE condition in (select att1 from local_table) ; -- query n°1 select /*+ RULE DRIVING_SITE (s2) */ * from Table2 s2 where condition in (select att1 from local_table); -- query n°2 select * from select /*+ DRIVING_SITE (s1) */ * from Table1 s1 WHERE condition in (select att1 from local_table) , select /*+ RULE DRIVING_SITE (s2) */ * from Table2 s2 where condition in (select att1 from local_table) where att_table_1 = att_table_2 -- sic
The crux of the problem was that the two separate statements individually produced an acceptable execution plan but the attempt to use the queries in inline views with a join resulted in a plan that (from the description) sounded like the result of Oracle merging the two inline views and running the two IN subqueries as FILTER (existence) subqueries.
We weren’t shown any execution plans and only had the title of the question (“Distributed sql query through multiple databases”) to give us the clue that there might be three different databases involved.
Obviously there are several questions worth asking when presented with this problem. The first being “can we have a more realistic piece of code”, also “which vesion of Oracle”, and “where are the execution plans”. I can’t help feeling that there’s more to the problem than just the three tables that seem to be suggested by the fragments supplied.
More significant, though, was the surprise that rule and driving_site should work together. There’s a long-standing (but incorrect) assertion that “any other hint invalidates the RULE hint”. I think I’ve published an example somewhere showing that /*+ unnest */ would affect an execution plan where the optimizer still obeyed the /*+ rule */ hint, and there’s an old post on this blog which points out that transformation and optimisation are (or were, at the time) independent of each other, implying that you could combine the rule hint with “transformational” hints and still end up with a rule-based execution plan.
Despite old memories suggesting the contrary my first thought was that the rule and driving_site hints couldn’t be working together – and that made it worth running a little test. Then one of the other specialists on the forums suggested using subquery factoring with the materialize hint – and I thought that probably wouldn’t help because when you insert into a global temporary table the driving site has to become the site that holds the global temporary tables (in fact this isn’t just a feature of GTTs). So there was another thing prompting me to run a test. (And then I suggested using the /*+ no_merge */ hint – but thought I’d check if that idea was going to work before I suggested it.)
So here’s a code sample to create some data, and the first two simple queries with calls for their predicted execution plans:
rem rem Script: distributed_multi.sql rem Author: Jonathan Lewis rem Dated: Jul 2020 rem Purpose: rem rem Last tested rem 19.3.0.0 rem 12.2.0.1 rem 11.2.0.4 rem rem create public database link test@loopback using 'test'; rem create public database link test2@loopback using 'test2'; rem create public database link orcl@loopback using 'orcl'; rem create public database link orcl2@loopback using 'orcl2'; rem create public database link orclpdb@loopback using 'orclpdb'; rem create public database link orclpdb2@loopback using 'orclpdb2'; define m_target=test@loopback define m_target2=test2@loopback define m_target=orcl@loopback define m_target2=orcl2@loopback define m_target=orclpdb@loopback define m_target2=orclpdb2@loopback create table t0 as select * from all_objects where mod(object_id,4) = 1 ; create table t1 as select * from all_objects where mod(object_id,11) = 0 ; create table t2 as select * from all_Objects where mod(object_id,13) = 0 ; explain plan for select /*+ driving_site(t1) */ t1.object_name, t1.object_id from t1@&m_target t1 where t1.object_id in ( select t0.object_id from t0 ) ; select * from table(dbms_xplan.display); explain plan for select /*+ rule driving_site(t2) */ t2.object_name, t2.object_id from t2@&m_target2 t2 where t2.object_id in ( select t0.object_id from t0 ) ; select * from table(dbms_xplan.display);
Reading from the top down – t0 is in the local database, t1 is in remote database 1, t2 is in remote database 2. I’ve indicated the creation and selection of a pair of public database links at the top of the script – in this case both of them are loopback links to the local database, but I’ve used substitition variables in the SQL to allow me to adjust which databases are the remote ones. Since there are no indexes on any of the tables the optimizer is very limited in its choice of execution plans, which are as follows in 19.3 (the oraclepdb/orclpdb2 links).
First, the query against t1@orclpdb1 – which will run cost-based:
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | 5168 | 287K| 57 (8)| 00:00:01 | | | |* 1 | HASH JOIN SEMI | | 5168 | 287K| 57 (8)| 00:00:01 | | | | 2 | TABLE ACCESS FULL | T1 | 5168 | 222K| 16 (7)| 00:00:01 | ORCLP~ | | | 3 | REMOTE | T0 | 14058 | 178K| 40 (5)| 00:00:01 | ! | R->S | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT "OBJECT_ID" FROM "T0" "A2" (accessing '!' ) Note ----- - fully remote statement
You’ll note that operation 3 is simply REMOTE, and t0 is the object accessed – which means this query is behaving as if the (local) t0 table is the remote one as far as the execution plan is concerned. The IN-OUT column tells us that this operation is “Remote to Serial” (R->S)” and the instance called to is named “!” which is how the local database is identified in the plan from a remote database.
We can also see that the execution plan gives us the “Remote SQL Information” for operation 2 – and that’s the text of the query that gets sent by the driving site to the instance that holds the object of interest. In this case the query is simply selecting the object_id values from all the rows in t0.
Now the plan for the query against t2@orclpdb2 which includes a /*+ rule */ hint:
----------------------------------------------------------- | Id | Operation | Name | Inst |IN-OUT| ----------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | | | | 1 | MERGE JOIN | | | | | 2 | SORT JOIN | | | | | 3 | TABLE ACCESS FULL | T2 | ORCLP~ | | |* 4 | SORT JOIN | | | | | 5 | VIEW | VW_NSO_1 | ORCLP~ | | | 6 | SORT UNIQUE | | | | | 7 | REMOTE | T0 | ! | R->S | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A1"."OBJECT_ID"="OBJECT_ID") filter("A1"."OBJECT_ID"="OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 7 - SELECT /*+ RULE */ "OBJECT_ID" FROM "T0" "A2" (accessing '!' ) Note ----- - fully remote statement - rule based optimizer used (consider using cbo)
The most striking feature of this plan is that it is an RBO (rule based optimizer) plan not a cost-based plan – and the Note section confirms that observation. We can also see that the Remote SQL Information is echoing the /*+ RULE */ hint back in it’s query against t0. Since the query is operating rule-based the hash join mechanism is not available (it’s a costed path – it needs to know the size of the data that will be used in the build table), and that’s why the plan is using a sort/merge join.
Following the “incremental build” strategy for writing SQL all we have to do as the next step of producing the final code is put the two queries into separate views and join them:
explain plan for select v1.*, v2.* from ( select /*+ driving_site(t1) */ t1.object_name, t1.object_id from t1@&m_target t1 where t1.object_id in ( select t0.object_id from t0 ) ) v1, ( select /*+ rule driving_site(t2) */ t2.object_name, t2.object_id from t2@&m_target2 t2 where t2.object_id in ( select t0.object_id from t0 ) ) v2 where v1.object_id = v2.object_id ; select * from table(dbms_xplan.display);
And here’s the execution plan – which, I have to admit, gave me a bit of a surprise on two counts when I first saw it:
----------------------------------------------------------- | Id | Operation | Name | Inst |IN-OUT| ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | MERGE JOIN | | | | | 2 | MERGE JOIN | | | | | 3 | MERGE JOIN | | | | | 4 | SORT JOIN | | | | | 5 | REMOTE | T2 | ORCLP~ | R->S | |* 6 | SORT JOIN | | | | | 7 | REMOTE | T1 | ORCLP~ | R->S | |* 8 | SORT JOIN | | | | | 9 | VIEW | VW_NSO_1 | | | | 10 | SORT UNIQUE | | | | | 11 | TABLE ACCESS FULL| T0 | | | |* 12 | SORT JOIN | | | | | 13 | VIEW | VW_NSO_2 | | | | 14 | SORT UNIQUE | | | | | 15 | TABLE ACCESS FULL | T0 | | | ----------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") filter("T1"."OBJECT_ID"="T2"."OBJECT_ID") 8 - access("T2"."OBJECT_ID"="OBJECT_ID") filter("T2"."OBJECT_ID"="OBJECT_ID") 12 - access("T1"."OBJECT_ID"="OBJECT_ID") filter("T1"."OBJECT_ID"="OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 5 - SELECT /*+ RULE */ "OBJECT_NAME","OBJECT_ID" FROM "T2" "T2" (accessing 'ORCLPDB2.LOCALDOMAIN@LOOPBACK' ) 7 - SELECT /*+ RULE */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1" (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' ) Note ----- - rule based optimizer used (consider using cbo)
The two surprises were that (a) the entire plan was rule-based, and (b) the driving_site() selection has disappeared from the plan.
Of course as soon as I actually started thinking about what I’d written (instead of trusting the knee-jerk “just stick the two bits together”) the flaw in the strategy became obvious.
- Either the whole query runs RBO or it runs CBO – you can’t split the planning.
- In the words of The Highlander “There can be only one” (driving site that is) – only one of the database involved will decide how to decompose and distribute the query.
It’s an interesting detail that the /*+ rule */ hint seems to have pushed the whole query into the arms of the RBO despite being buried somewhere in the depths of the query rather than being in the top level query block – but we’ve seen that before in some old data dictionary views.
The complete disregard for the driving_site() hints is less interesting – there is, after all, a comment in the manuals somewhere to the effect that when two hints contradict each other they are both ignored. (But I did wonder why the Hint Report that should appear with 19.3 plans didn’t tell me that the hints had been observed but not used.)
The other problem (from the perspective of the OP) is that the two inline views have been merged so the join order no longer reflects the two isolated components we used to have. So let’s fiddle around a little bit to see how close we can get to what the OP wants. The first step would be to add the /*+ no_merge */ hint to both inline view, and eliminate one of the /*+ driving_site() */ hints to see what happens, and since we’re modern we’ll also get rid of the /*+ rule */ hint:
explain plan for select v1.*, v2.* from ( select /*+ qb_name(subq1) no_merge driving_site(t1) */ t1.object_name, t1.object_id from t1@&m_target t1 where t1.object_id in ( select t0.object_id from t0 ) ) v1, ( select /*+ qb_name(subq2) no_merge */ t2.object_name, t2.object_id from t2@&m_target2 t2 where t2.object_id in ( select t0.object_id from t0 ) ) v2 where v1.object_id = v2.object_id ; select * from table(dbms_xplan.display); ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | 4342 | 669K| 72 (9)| 00:00:01 | | | |* 1 | HASH JOIN | | 4342 | 669K| 72 (9)| 00:00:01 | | | | 2 | VIEW | | 4342 | 334K| 14 (8)| 00:00:01 | | | | 3 | REMOTE | | | | | | ! | R->S | | 4 | VIEW | | 5168 | 398K| 57 (8)| 00:00:01 | | | |* 5 | HASH JOIN SEMI | | 5168 | 287K| 57 (8)| 00:00:01 | | | | 6 | TABLE ACCESS FULL | T1 | 5168 | 222K| 16 (7)| 00:00:01 | ORCLP~ | | | 7 | REMOTE | T0 | 14058 | 178K| 40 (5)| 00:00:01 | ! | R->S | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID") 5 - access("A3"."OBJECT_ID"="A6"."OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - EXPLAIN PLAN INTO "PLAN_TABLE" FOR SELECT /*+ QB_NAME ("SUBQ2") NO_MERGE */ "A1"."OBJECT_NAME","A1"."OBJECT_ID" FROM (SELECT DISTINCT "A3"."OBJECT_ID" "OBJECT_ID" FROM "T0" "A3") "A2","T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" (accessing '!' ) 7 - SELECT "OBJECT_ID" FROM "T0" "A6" (accessing '!' ) Note ----- - fully remote statement
In this plan we can see that the /*+ driving_site() */ hint has been applied – the plan is presented from the point of view of orclpdb (the database holding t1). The order of the two inline views has apparently been reversed as we move from the statement to its plan – but that’s just a minor side effect of the hash join (picking the smaller result set as the build table).
Operations 5 – 7 tell us that t1 is treated as the local table and used for the build table in a hash semi-join, and then t0 is accessed by a call back to our database and its result set is used as the probe table.
From operation 3 (in the body of the plan, and in the Remote SQL Information) we see that orclpdb has handed off the entire t2 query block to a remote operation – which is ‘accessing “!”‘. But there’s a problem (in my opinion) in the SQL that it’s handing off – the text is NOT the text of our inline view; it’s already been through a heuristic transformation that has unnested the IN subquery of our original text into a “join distinct view” – if we had used a hint to force this transformation it would have been the /*+ unnest(UNNEST_INNERJ_DISTINCT_VIEW) */ variant.
SELECT /*+ NO_MERGE */ "A1"."OBJECT_NAME","A1"."OBJECT_ID" FROM (SELECT DISTINCT "A3"."OBJECT_ID" "OBJECT_ID" FROM "T0" "A3") "A2", "T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID"
I tried to change this by adding alternative versions of the /* unnest() */ hint to the original query, following the query block names indicated by the outline information (not shown), but it looks as if the code path constructs the Remote SQL operates without considering the main query hints – perhaps the decomposition code is simply following the code path of the old heuristic “I’ll do it if it’s legal” unnest. The drawback to this is that if the original form of the text had been sent to the other site the optimizer that had to handle it could have used cost-based query transformation and may have come up with a better plan.
You may be wondering why I left the /*+ driving_site() */ hint in one of the inline views rather than inserting it in the main query block. The answer is simple – it didn’t seem to work (even in 19.3) when I put /*+ driving_site(t1@subq1) */ in the main query block.
tl;dr
The optimizer has to operate rule-based or cost-based, it can’t do a bit of both in the same query – so if you’ve got a /*+ RULE */ hint that takes effect anywhere in the query the entire query will be optimised under the rule-based optimizer.
There can be only one driving site for a query, and if you manage to get multiple driving_site() hints in a query that contradict each other the optimizer will ignore all of them.
When the optimizer decomposes a distributed query and produces non-trivial components to send to remote sites you may find that some of the queries constructed for the remote sites have been subject to transformations that you cannot influence by hinting.
Footnote
I mentioned factored subqueries and the /*+ materialize */ option in the opening notes. In plans where the attempt to specify the driving site failed (i.e. when the query ran locally) the factored subqueries did materialize. In any plans where the driving site was a remote site the factored subqueries were always inline. This may well be related to the documented (though not always implemented) restriction that temporary tables cannot take part in distributed transactions.