Re: IPC versus TCP performance

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Oct 2018 17:51:12 +0000
Message-ID: <CWXP265MB1493D2D0C1BFE9B4A94FD53FA5CD0_at_CWXP265MB1493.GBRP265.PROD.OUTLOOK.COM>

Still (and inevitably) true.
You either do a nested loop that typically results in a large number of small dialogues taking place across the network or you do a hash join (or merge join) which typically requires a huge flood of redundant data to be dragged across the network (in relatively small chunks) and mostly discarded.

Often the best optimisation strategy for distributed queries is to make sure the data generation happens at the correct site. (driving_site hint).

Add to the mechanical problems the optimizer doesn't: pull histograms across the network
take notice of virtual columns or extended stats consider function-based indexes (which fits with the "virtual columns being ignored" bit)

and it only examines 20 of the indexes on any remote table - a few tests suggest its the 20 most recently created, but it may be more subtle than that.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> Sent: 31 October 2018 17:25
To: oracle-l-freelist; mwf_at_rsiz.com; contact_at_soocs.de; Amir.Hameed_at_xerox.com Subject: RE: IPC versus TCP performance

I agree with the optimizer question. At least a few versions ago, joining tables across a database link had poor performance. Anybody know if still true.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 31 2018 - 18:51:12 CET