Re: Large SGA - performance problems

Erlend Dyrnes wrote:

[snip]
>
> 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.
>

 [snip]
> 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.
>

[snip
<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

Well, we have an HP9000 T500 running Oracle 7.2.2 on HP-UX 10.01. While you mention
that you have plenty of disks, you may not have enough channels. We had similar performance
problems on our box related to disk i/o bottlenecks...Oracle could pump out the data faster
than the disks could read/write it. We ended up buying 5 more SCSI controllers so that we
could separate the various Oracle tablespaces across channels. Oracle's preference is lots
of channels-hoping to parallelize as much of the work as possible.

So, make sure that you've load balanced the disk i/o as much as possible. Separate your redo
logs, rollback segments, temporary segments, and data tablespaces on separate channels. And
don't use RAID 5 for those objects doing sequential reads. You don't mention
whether the table scan takes place in memory or on disk, but if Oracle needs to create
temporary segments to sort or process the data then disk i/o bottlenecks could occur.

And, as stated before, make sure you've installed all the recommended Oracle and kernel
patches.

hope this helps...judie
stroup_at_ops195.lmsc.lockheed.com Received on Mon Jan 06 1997 - 00:00:00 CST