Re: Slow queries on distributed databases

Lisa,

The Oracle Distributed Database Systems manual has some really good information for you that I think would help a lot. This manual details how distributed queries work and how to tune distributed queries. I'm sure that it would help your cause to spend some time reading this manual.

HTH,
Brian

Lisa McGrath wrote:
>
> 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
> **********************************************
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================