Distributed Query Using Remote Objects Causing Hash Join Degrading Performance (Doc ID 360920.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.5 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Distributed Select Statement Hangs
Local d/b : 9.2.0.5.0 -> Remote d/b : 9.2.0.5.0

Local tables : fac_channel_asgn chn, customer_address cust
Remote tables : uis_circuit, fac_component,

The following query is slow :

select distinct uc.customer_name, cust.customer_mcn,
...
from fac_channel_asgn chn, customer_address cust,
uis_circuit@<db_link> uc
where chn.circuit_identification = cust.circuit_identification
...
from fac_component@<db_link> start with
component_clfi = '101 T3 IRVNCA12 SNANCAAV '
connect by prior clfi = component_clfi)
order by 1,2 ;

The execution plan for this statement is of the form :

----------------------------------------------- ------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 415 | | 41808 |
| 1 | SORT UNIQUE | | 1 | 415 | | 41788 |
| 2 | HASH JOIN | | 1 | 415 | | 41767 |
| 3 | NESTED LOOPS | | 1 | 356 | | 25347 |
| 4 | HASH JOIN | | 1 | 231 | 48M| 2534 5 |
| 5 | TABLE ACCESS FULL| CUSTOMER_ADDRESS | 291K| 45M| | 12124 |
| 6 | TABLE ACCESS FULL| FAC_CHANNEL_ASGN | 6375K| 413M| | 6348 |
| 7 | REMOTE | | 1 | 125 | | 2 |
| 8 | VIEW | VW_NSO_1 | 22M| 1288M| | 16264 |
| 9 | REMOTE | | | | | |
-----------------------------------------------------------------------

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms