Re: Oracle V7.1 show-stopping performance problem

Greg D. (nngis_at_nc5.infi.net) wrote:
:
: Second, here is our problem illustrated through typical use of the system:
:
: > A user in one locality queries a single locality's database.
: These queries usually execute in seconds and work well.
:
: > A user in one locality queries multiple locality's databases.
: This causes our system to join tables/views across multiple servers.
: These queries take anywhere from 5 - 20 minutes, hence our
: problem.
:

I experienced this problem recently too, in a much smaller scale but same tim problems. Oracle is not overly smart apparently when you join to tables across database links. It will pull the entire remote table(s) over the link and perform the join locally in tempsegs. I had a view that joined to one table over a link and it increased my query time from 4 seconds to 3 minutes. I have a feeling buffer pools may help alleviate some of this but the base problem is that the distributed processing isn't doing the work on the remote machines effectively.

Possible solutions include snapshots of the other localities onto the local machines, or using data replication in much the same way.

Joining across the link in and of itself is the problem, but I don't have a real good explanation why, just a lot of guesses. Anyone?

Joe

:
: Is the answer to revisit our database design? Should we revisit our indexing?
: Should we throw more memory at Oracle's buffer pools? Should we do all of
: the above, cross our fingers, and hope for the best?
:
: Any help would be greatly appreciated. If the response warrants it, I'd
: be happy to give credit where it is due and summarize responses and post
: the solution as well.
:
: Greg DiGiorgio
: nngis_at_infi.net
:
: "The views represented here are not my own. Any likeness to what
: I truly believe is purely accidental.", Bendict Arnold (hot dog
: stand operator, South Bronx).
 

-- 
                                   
=------------------------------------------------------------------------=
Joe Nardone               |    
joe_at_access.digex.net      |