Re: Huge Database - Performance Problem

noodles_at_aol.com (Noodles) wrote:

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

How much RAM & % utilization.
How much CPU utilization.
Disk I/O rate.

>The application is2-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.

And then does what? OLAP, DSS, OLTP, ad-hoc queries?

>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

Only 3? That could do it. How SCSI many controllers?

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

What IS your PROBLEM? The only place in this message that the word problem occurs is in the phrase "some performance problems". We need more and specific information if you expect useful suggestions.

>Option #1:
> Cluster the table data and indexes

Rule of thumb, avoid clustering at all costs unless the style of user interaction warrants it, and even then consider a denormalization to handle this.

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

Will probably require more disk space because of data redundancy.

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

Use explain plan and tkprof with init.ora timed stats on to explain the execution characteristics of your query. Add indexes when indicated. Also examine your query sql statements to see if syntax is causing indexes are being ignored.

>At my wits end,
>Cliff
>(noodles_at_aol.com)

Without knowing anymore than the meager description you have provided, I suggest you...

Use more spindles distributed across several controllers, Increase RAM in server if you have 90% or more in use. Use the utilities explain plan and tkprof to govern query syntax and index creation.

Craig Wall Received on Thu May 16 1996 - 00:00:00 CEST