My Oracle Support Banner

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

Last updated on OCTOBER 24, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.5 to 11.2.0.2 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Distributed Select Statement Hangs

The following query is slow :

select distinct col, col2, etc..
...
from table1 chn, customer_address cust,
table2@<db_link> uc
where chn.circuit_identification = cust.circuit_identification
...
from table2@<db_link> start with
col3 = '111'
connect by prior col4 = col5)
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| TABLE1 | 291K| 45M| | 12124 |
| 6 | TABLE ACCESS FULL| TABLE2 | 6375K| 413M| | 6348 |
| 7 | REMOTE | | 1 | 125 | | 2 |
| 8 | VIEW | VIEW1 | 22M| 1288M| | 16264 |
| 9 | REMOTE | | | | | |
-----------------------------------------------------------------------

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.