Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Distributed Query in 8i->9i now slower 9i->9i

Distributed Query in 8i->9i now slower 9i->9i

From: Lee Van Doren <leevd_at_adelphia.net>
Date: 27 Feb 2004 08:53:16 -0800
Message-ID: <afc7da8f.0402270853.5cb843a3@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US