Andrew Fraser DBA

Oracle distributed query join over remote database link slow performance leading driving_site hint fix

Oracle Optimizer doesn’t cope so well with distributed queries, joining to tables in different databases.

1) The leading and/or driving_site hints can dramatically improve distributed query performance.

SELECT /*+ leading(tab1) driving_site(tab2) */ ... FROM table1 AS tab1 JOIN table2@db2 AS tab2 ...

Driving_site specifies the database you want doing the bulk of the work, likely the largest database involved. Specify a table alias rather than a database name in the hint.

Leading specifies the table (alias) you want to start the query off at, likely the most restrictive table involved.

For my problem query, I found leading was better for consistent performance than driving_site, and that response time improved from 28 minutes without hint, down to 27 seconds with leading hint. The driving_site hint was very much cache dependent: 2 minutes 20 seconds initially when run from cold, 7 seconds if rerun while all data was still cached. Hinting both ways at the same time had the same effect in my case as just using the driving_site hint, because my leading table was not in the driving database.

For normal, non distributed queries, it would be better to try to improve optimizer stats before resorting to hints. Where possible, avoid adding hints blindly, but use only in specific cases after testing the effects. Re-testing should be done after a major upgrade, because Oracle continually improve the optimizer, and your hint may eventually prevent improvements from taking effect.

2) If you have a lot of distributed queries like this, you should consider merging the databases into a single database (can keep them in separate schemas).

3) Another option, especially for reporting systems, would be to replicate the remote tables, using e.g. materialized views or transportable tablespaces.

Jonathan Lewis has full details on this in an article in Oracle Scene Magazine Spring 2015 Issue 56. The full magazine is here, or just the four pages of his specific article here.