Posted by We have a fairly common query process, where we run a MERGE command to compare a remote table to a local copy of it, as “poor mans” Golden Gate to bring that table up to date on a regular basis. [Editors note: Writing MERGE’s is more complicated but a lot cheaper than Golden Gate :-)]
After an upgrade to 12c, the performance of some of the MERGE’s went very bad…and you can see what happened with the (sanitised) example below:
The bold part is a join that we’ll be executing on the remote database (mydb). It’s been hinted to run in a particular way.
SQL> explain plan
2 into sys.plan_table$
3 for
4 merge
5 into local_copy.loc_t1 w
6 using ( select /*+ leading(t1) index(t2 t2_pk) use_nl_with_index(t2)*/
7 t1.col1
8 ,t1.col2
9 ,t1.col3
...
27 from scott.t1@mydb t1,
28 scott.t2@mydb t2
29 where t1.seq = t2.seq
30 and ...
31 ) p
32 on ( p.seq = w.seq
33 )
34 when matched then
..
75 when not matched then
..
Explained.
Now we can’t see directly from the explain plan how the query will be run on the remote database – we just get a “REMOTE” line in the plan. However, the additional data in the plan reveals a problem
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1068K| 696M| | 2492K (1)| 00:01:38 | | |
| 1 | MERGE | LOC_T1 | | | | | | | |
| 2 | VIEW | | | | | | | | |
|* 3 | HASH JOIN OUTER | | 1068K| 298M| 210M| 2492K (1)| 00:01:38 | | |
| 4 | REMOTE | | 1068K| 90M| | 50193 (2)| 00:00:02 | PORAI~ | R->S |
| 5 | TABLE ACCESS FULL| T1 | 38M| 3625M| | 91205 (2)| 00:00:04 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P"."SEQ"="W"."SEQ"(+))
Look very closely at what query Oracle will be throwing at the remote database
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT /*+ NO_MERGE LEADING ("T1") USE_NL_WITH_INDEX ("A2") INDEX ("A2" "T2_PK") */
...
FROM "SCOTT"."T1" "A1","SCOTT"."T2" "A2"
WHERE "A1"."SEQ"="A2"."SEQ"
The two tables have been re-aliased as A1 and A2, but notice that one of the hints did NOT get corrected. The lack of a (valid) leading hint led to a bad plan on the remote database, and performance problems as a result. For our case, the solution was to explictly add a NO_MERGE hint into the original statement:
SQL> explain plan
2 into sys.plan_table$
3 for
4 merge
5 into local_copy.loc_t1 w
6 using ( select /*+ no_merge leading(t1) index(t2 t2_pk) use_nl_with_index(t2)*/
7 t1.col1
8 ,t1.col2
9 ,t1.col3
...
27 from scott.t1@mydb t1,
28 scott.t2@mydb t2
29 where t1.seq = t2.seq
30 and ...
31 ) p
32 on ( p.seq = w.seq
33 )
34 when matched then
..
75 when not matched then
..
Explained.
which yielded a correctly hinted SQL on the remote database
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT /*+ NO_MERGE LEADING ("A1") USE_NL_WITH_INDEX ("A2") INDEX ("A2" "T2_PK") */
...
FROM "SCOTT"."T1" "A1","SCOTT"."T2" "A2"
WHERE "A1"."SEQ"="A2"."SEQ"
This only has come to light on 12c – the previous version we were on (11.2.0.3) was unaffected.
Learning is not a spectator sport 
Your ‘anonymisation’ of the SQLs left the prop/opbet aliases which is a bit confusing.
sorry – will correct asap
What’s your solution of this problem?