Large SGA - performance problems

Hi net!

I'm pondering about some rather irritating performance problems we have, and wondering whether you have any suggestions for me to consider.

Here's the thing:

We run 2 HP9000 K420 machines in a HP cluster. Both equipped with 1GB of memory, 2 processors and fibre channel for internal communication. Disk ? Enough! One of them is our database server, running Oracle 7.2.3, and SQL*Net v1 (Yes, I know!). The database serves the other HP over the fibre channel into a Powerhouse application.

Database characteristics:

Sizes:

TABLESPACE_NAME                        MB   COUNT(*)
------------------------------ ---------- ----------
GEN_FREE_TEXT                         800          1
INDEX01                              2400          6
RBS                                   800          2
SYSTEM                                100          1
TEMP                                  400          1
TOOLS                                  50          1
USERS                                  50          1
VMS                                  2000          5

SGA: SQLDBA> show sga

Total System Global Area     287680840 bytes
              Fixed Size         47936 bytes
           Variable Size     164589064 bytes
        Database Buffers     122880000 bytes
            Redo Buffers        163840 bytes

optimizer_mode	CHOOSE

We have optimizer statistics.

Tables, views and indices :

Tables:		351
Views:		40
Indices:	607

Concurrent users (approx) : 80

OK, and now the problem:

Every now and then a user generate a table scan (one way or another). This table scan tends to "hang" the database for a while. The database process uses ~100% CPU, and nothing else is allowed to happen on the database during the table scan. Not even a sqlplus session login. The hangs may last from 5 seconds up to over 1 minute. As of now we can cope, but as we increase number of concurrent users, I'am afraid the problem will be a nightmare.

I ask myself :

1. Is the hangs due to the very large SGA ? Is such a large SGA generally a good idea ?
2. Should I use the cost-based optimizer in Oracle 7.2.3 ?

And now I ask you:

Am I asking myself the right questions ? Any other experience on this matter you would like to share with me ?

Regards.

erlend

Erlend Dyrnes: Senior systems consultant, MBS Fjerndata AS, Bergen, Norway   System manager (HP-UX) , application configuration   email: Erlend.Dyrnes_at_mbs.no or k38122_at_ddb.be.statoil.no

• I do *not* know what it means to miss New Orleans