Huge Database - Performance Problem

Oracle gurus:

I inherited a large ( 8.2 Gig ) Oracle 7.1.3 database running on an Alpha box 6.1 OS and am having some performance problems. The application is 2-tier client-server architecture where a user connects to a server process which then acts as an interface between the 14 table Oracle database and a large chemical structural database. The top-level table has 1.5 million rows which then join down to the remaining tables with an average of 3 million rows in the remaining tables. The top-level table also links to the structural database which has .6 million structures.

I have the data tablespaces and indexes spread out over 3 disks respectively and indexes are created in logical places.

I've tweaked the init.ora parameters according to the tuning docs and rebuilt the SGA pad 3 times. I'm running out of pagefile space and worried about the SGA eventually swapping out.

Am I missing something or do I need an Oracle tuning specialist ?

Option #1:

                 Cluster the table data and indexes

Option#2:
                 Compress the 14 tables into some logical combinations so
I don't              have so many joins.

Option #3:
                 Index more columns.  ( and buy 3 more disks  <sic>) 

At my wits end,
Cliff
(noodles_at_aol.com) Received on Wed May 15 1996 - 00:00:00 CEST