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.
Recent comments
3 years 5 weeks ago
3 years 17 weeks ago
3 years 22 weeks ago
3 years 23 weeks ago
3 years 27 weeks ago
3 years 48 weeks ago
4 years 16 weeks ago
4 years 46 weeks ago
5 years 30 weeks ago
5 years 31 weeks ago