Hi Laurentiu
Maybe that adjustment is for covering additional overhead due to multiple
calls of the remote query by network, since every loop is a new remote call
with bind variables by dblink.
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE
http://orasql.org
On Tue, 4 Oct 2022, 15:39 Laurentiu Oprea, <laurentiu.oprea06@xxxxxxxxx>
wrote:
Hello everyone,
DB version 19.5
I have a situation (which I think I saw in the past) where in the
execution plan the result of a NL join, estimated to cardinality 1, is hash
joined with a remote table (estimated similarly to cardinality 1).
The problem is, because of HASH join(right outer), the join predicate is
not pushed in the remote query..and as a consequence the remote query is
taking 1 hour. With NL, the remote query will take just a few
seconds (because of the added where clause).
Looking into 10053 looks like the cost for NL is 27.154647 and cost for
Hash is 27.180501, so in theory NL should be used but just above best join
method I can see: "Cost adjustment for NL join with remote table 0.005859"
and then HASH is used.
Can someone help me understand this "Cost adjustment for NL"?
Thank you.
