Poor performance after enabling db links

1. SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'1hnnzbwqq1wjh')); 
2. PLAN_TABLE_OUTPUT 
3. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
4. SQL_ID  1hnnzbwqq1wjh, child number 0 
5. ------------------------------------- 
6. SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM 
7. "APPS"."C$_727935750_4_0" "C$_727935750_4_0" 
8. Plan hash value: 562628296 

Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.

I will note however that that plan looks bad! The first bit is a nested loops full tablescan, that is never a good idea. Looking back at your query and view, you're hinting the nested loops here. The interesting thing about the join is that it's against some non-merged views. I expect when your query runs fast, these views are mergable and the nested loops can use an index (or is invalid so use a hash join/something else scalable). I have seen things like distributed queries preventing views to be merged, however like I mentioned earlier, I can't quite see what's distributed here (there are other reasons why simple views can be prevented from being merged, the 10053 trace file would tell you but I don't think it's worth going into that before we have established the facts).

I'm not familiar with whatever application you're running so I'm not sure what's going on here, maybe you can shed some light. Your query references:

APPS.FND_FLEX_VALUES   B, APPS.FND_FLEX_VALUE_NORM_HIERARCHY   H 

But your plan goes to a view named FND_FLEX_VALUES# which is on top of another view named FND_FLEX_VALUES# which is on top of a table named FND_FLEX_VALUES.

Does that seem right to you?

The same behaviour looks to be effecting the second part of the query, causing the anti join to do nested loops (via the filter operator) using a tablescan and bulky joins.

The second plan you shared has two major differences and an explanation,

-it's parallel enabled

-It uses hash joins and anti hash joins where you previously had nested loops and filter operations

-The views were merged so your hints were invalid

So, I think we have the opposite of what I first proposed, you currently have some hints that are not valid because they don't reference the real objects after the query is transformed, however when you do this over the DB link (although I'm still not confident the first query was over the DB link), the views are no longer mergeable so your hints actually refer to the objects used (the views) - the hints are bad ones though so you get rewarded with a bad plan.

-Just before I hit "Add Reply", it's dawned on me that the first query may be the execution plan taken from the source DB but the query was fired from an insert statement on the target DB and become fully remote.

I think we can get a similar performance as without the DB link by removing the hints. The views will probably not merge still though so you'll still have to suffer from predicates not being able to be applied in the most efficient manner. I'll have a play and see if I can come up with a decent solution that allows you to really benefit from the DB link (the pl/sql solution I offered earlier might certainly be okay but not fantastic).

1. createtableSYFBI_DW.C$_702522750_4_0
2. (
3. C1_INTEGRATION_IDVARCHAR2(4000)NULL,
4. C2_SRC_EFF_FROM_DTDATENULL
5. )
6. NOLOGGING