Skip to Main Content
  • Questions
  • How to improve timings of query executed on a DB link?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 29, 2021 - 10:23 am UTC

Last updated: October 04, 2021 - 4:47 am UTC

Version: Oracle 19 C

Viewed 100+ times

You Asked

Hello Team,

First of all, thanks for all the good work you are doing.

It would be great if you can help us understand how we can improve timings of query executed on a DB link.

Consider an Oracle instance (DB1) that can talk with another oracle instance (DB2) via DB Link. A select statements fetches ~1,00,000 values from DB1 and pass them to a function that is defined on DB2. The function do some operations on the values and returns the results. All the data is in DB1 but the function has to be in DB2.

We have tried multiple things and using DRIVING_SITE hint helped to reduce timings. However, it comes with its own issues( Code re-write, hint can be ignored, issue in techniques used in plan stabilization, query transformation, non-applicability to DML ). Things like switching to bulk processing did not help much.

Any thing else that we can try?

We are looking for techniques that can be applied to a large number of statements.

Thanks,
AB

and Chris said...

I discussed options for tuning distributed queries in an Office Hours session a while ago:



https://asktom.oracle.com/pls/apex/asktom.search?oh=5684

All the data is in DB1 but the function has to be in DB2.

Why does the function have to be in DB2?

The best way to speed this query up is to stop transferring the data around and process it all locally. Creating the function on DB1 and avoiding DB links is the single biggest gain you can make performance-wise.

Or - even better - moving the logic out of the function and into pure SQL should also help; this isn't always practical however.

Rating

  (1 rating)

Comments

Reason for moving the function outside DB1

A reader, October 01, 2021 - 5:12 am UTC

Thank you for the response Chris.

The function invokes a C lib via External Procedure call. The lib do some processing and returns the result back to the function.

The function has to be moved to DB2 (a non-ExaCC DB) because DB1 is in ExaCC and we are not allowed to put the C lib in ExaCC.

Also, due to some security restrictions, we can not move logic from the C lib in DB.
Connor McDonald
October 04, 2021 - 4:47 am UTC

I spoke to some of our Exa CC people - they replied

While we recommend to not install additional software on servers (as especially additional RPMs will affect OS patching/upgrade), it never says anywhere you are not allowed to install additional software….

One option you could consider is using a docker container for additional software, as this does not interfere with the OS updates then… See: Decoupling RPM Dependencies, running Oracle R Distribution (ORD) on Exadata Database Nodes in Docker (Doc ID 2257680.1)

If this is the case, you may want to put the software onto an ACFS folder, to have a common location across the cluster (and not to worry to have to update each node individually).

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database