Re: Slow queries on distributed databases

In all the previous posts, I did not see any indication of table sizes. All in all, consider network traffic as a threat - consider creating the views on "the opposite side" if you feel it will reduce network traffic. Besides, I strikes me as ineffective to refer to a view (on B) from A, whereas this view uses data on A (being remote, when seen from B). That is doubling network traffic!
Rather rework the select not to rely on that view, but on the tables in A, joined with (simple) views to B.

BTW, AIX 4.2 is not very effective with TCP/IP - consider 4.3.2 - you didn't state the o/s version...

--
Frank
Lisa McGrath <lmcgrath_at_uwsa.edu> schreef in berichtnieuws
39D11643.9D092439_at_uwsa.edu...


> That's what we tried doing with the views, to make everything look as if

 its


> local.  We used views instead of synonyms to get around a security

 problem.  If


> we created a synonym to reference a remote object we couldn't grant users

> privileges to the synonym (can't grant privs on a remote object) .  By

 creating


> views that basically select * from the remote object we are able to grant

 the


> users select privileges on the view.  Do synonyms work more efficiently

 than


> views, and if so, is there a way to get around the security problem with

> synonyms?

>

> Thanks for the help.

>

> Lisa

>

> Steve Long wrote:

>

> > 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

> > > **********************************************

> > >

> > >

>

> --

> Lisa McGrath

>

> Data Access Specialist

> Office of Information Services

> University of Wisconsin System Administration

> **********************************************

>

>