Re: Slow queries on distributed databases

i ran into this very problem about 5 years ago, so the solution is somewhat foggy in my mind, but it is something like create local synonyms for the remote objects so the parser "thinks" everything is local.

"Lisa McGrath" <lmcgrath_at_uwsa.edu> wrote in message news:39D0A7A3.42E03CF8_at_uwsa.edu...
>
> Hi,
>
> I'm hoping you can give me some clues on how to speed up queries running
> on distributed databases.
>
> We are running Oracle 8.0.6 on two UNIX (AIX) boxes, and have data on
> both boxes that each box needs. We have database links set up between
> the boxes, and we have created views on each box to reference the data
> objects on the other box, to make it transparent to the users.
>
> If we are on box A and select from a view that references a view on box
> B that uses data that is only on box B it works great. However, if we
> use a view on box A that references a view on box B, but the view on box
> B uses data that resides on both box A and box B the query takes several
> hours - if it ever finishes. The same query, before we split the data
> onto two boxes, ran in a couple minutes. Even running an Explain Plan
> on the query will take three to four hours.
>
> If we watch the number of Oracle reads the query seems to run well for a
> while, then just get hung up - there will be no more reads and it
> doesn't seem to get any CPU time.
>
> Are there any special options we should have installed or initSID.ora
> parameters we should look at to help speed these queries?
>
> Thanks much.
> Lisa
>
> --
> Lisa McGrath
>
> Lmcgrath_at_uwsa.edu
> Data Access Specialist
> Office of Information Services
> University of Wisconsin System Administration
> **********************************************
>
>
Received on Tue Sep 26 2000 - 16:10:01 CDT