Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Distributed Query in 8i->9i now slower 9i->9i
An 8i database PLRU is linked to a 9i database SBGS
We exported all data tables from PLRU into PLRS, a 9i database. We established a link from PLRS to SBGS.
A distributed query that runs across PLRU-SBGS (8i-9i) runs in 17s. Running the same query in PLRS-SBGS (9i-9i) runs in 15 MINUTES.
From everything we can tell the equivalent configurations are present
between the two databases.
The explain plan on each execution show some differences. Notably the
faster query shows it utilizing several MB of memory for one step
while the slower query never chooses to use more than 111K for any
step. Is there something making it think it doesnt have much memory
to work with?
The faster plan also shows it using MERGE JOIN CARTESIAN while the
slower plan doesn't use that option.
Also, queries running internally on PLRS (9i) run on average TWICE as FAST as PLRU (8i).
I also minimized the query down to mainly just working with the remote tables and see the same type of behavior. The two plans are closer to each other but still a big difference in the faster plan taking advantage of memory while the slower plan will not.
What could be different?
The data is the same in both databases and they are on equivalent
sized servers with equivalent configured data and memory.
Note that the SBGS database is on the same physical machine as the
PLRS database (even though it is the slower combination).
Any ideas?
Lee
Received on Fri Feb 27 2004 - 10:53:16 CST