Re: Large SGA - performance problems

Erlend Dyrnes wrote:
>
> 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.
>
>
> 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
Erlend,

   There are two things that I can think of that could be causing your database to "hang".

1. The arch (archiver) can't keep up with the lgwr (log writer).
2. It's busy doing a checkpoint (writing modified database buffers to disk). Since you have about 120Mb of DB buffers in the SGA this can take some time to complete.

If you are not archiving, then #1 can't be the problem. If you are archiving, check the alert.log. If there are error messages indicating "log file not available" (or some such message), then #1 is the problem. One solution would be to increase the size of your log files. Also, you should have more than 2 log files.

If #2 is the problem, try starting up the "checkpoint" process. You do this by setting the parameter "checkpoint_process = true" in the init.ora
file. Also, examine the init.ora parameters that control checkpointing to make sure that you are not checkpointing too often.

Hope this gives you some ideas to solve your problem. Received on Mon Jan 06 1997 - 00:00:00 CST