Oracle V7.1 show-stopping performance problem

We're desperate! We need help badly! We've got a performance problem with Oracle V7.1 running under Netware that both we and a hired Oracle gun have not been able to solve. This problem is so serious that we have has stopped all application development until this problem is solved. I hope that there is someone out there with sufficient knowledge/experience to guide us in the right direction.

First, here is how we setup our database:

> Oracle V7.1 is running in a Netware V4.1 environment.
 

> We have 4 databases located on 4 database servers, each
          at a separate location tied together with T1 comm lines.

> Each database is identical in structure, having the
          same schemas - only the data in the tables is different.           

	  FYI, this project is a regional effort. Each participating
	  locality uses the same table layout, but populates their 
	  tables with their own data. Each locality also has its
	  Netware LAN and Oracle database server.



	> Canned queries make use of stored views using indexed columns.

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.

Third, here are some things we THINK we have eliminated as the cause:           

> WAN communications
> LAN connectivity
> Inadequate buffer space for Oracle
> Non-indexed columns in the joined columns
        

Lastly, what do you think? What else could cause Oracle to run so smoothly at a single site, but run so badly when doing the same exact operation across mutiple servers, joining the results of each local query into a single results table for the user?

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). Received on Fri Sep 08 1995 - 00:00:00 CEST