Re: db_links and optimiser problem?

Got it. The problem is that the remote table is the "driven" table of a nested loops join. So here's what's happening:

1. Full scan of local table.
2. Get the first record of that.
3. Oracle goes and gets the data from the remote table for the first record. (open connection, log in remote database, get data, close connection)
4. Get the second record from the local table.
5. Oracles goes and gets the data from the remote table for the second record. (open connection, get data, close connection).
6. Lather. Rinse. Repeat. Watch performance die.

Even if the query of the remote table is efficient, you're going across that link *every single time*, and it's a performance hit. I've seen it absolutely kill performance of NL joins like yours.

So, what to do? Well, here are a few options: 1. Use a hint to force the REMOTE table to be the driving table for the query. That way it's hit once.
2. Use a hint or disable indexes to force the query to use a MERGE JOIN instead of a NL join. That way the remote and local tables are full scanned (once) but the query will be using set processing instead of row processing. 3. Create a view on the remote side, and use a grouping function in the view. That may improve the chances that the remote data will drive the query.

All 3 of these options reduce the number of remote calls by doing full scans of the remote data. Since you're changing the access path to the remote data, that may make performance poor as well. It's a trade-off. Another option is to replicate the remote data into your local database.

This is a very short overview of tuning queries that use remote databases. See Chapter 11 of Oracle8 Advanced Tuning & Admin (obdisclaimer) for a discussion of remote table accesses (tip 8) and the different join methods Oracle uses (tip 3) and the impact of views (tip 4).

Hope this helps.
Kevin Loney.
http://www.kevinloney.com Received on Thu Oct 08 1998 - 07:56:00 CDT