Oracle8 panic! - lousy performance/high load

From: Tom Karlsson - Remove NOSPAM <tk_at_babylon5.dkNOSPAM>
Date: Tue, 13 Nov 2001 16:38:47 +0100
Message-ID: <Pine.LNX.4.21.0111131616000.24342-100000@sith.ammulti.dk>

Hey all,

I'm having severe load problems with an Oracle 8.0.5 for linux. Load average climbs above 25.00 in peak hours. It's pretty much a standard install running on a dual PIII-800, 640MB RAM, 1 system disk, 1 data scsi datadisk.

Unfortunately I don't have the rocket scientist abilities to tune Oracle. As a last act of desperation I'll post my Oracle setup here, hoping some friendly soul will point out some obivious flaws, hints, anything is appreciated.

init file settings:

file                           =

/u01/app/oracle/admin/orcl/pfile/configorcl.ora

rollback_segments = (r01,r02,r03,r04)

# tuning parameters

db_files = 80

db_file_multiblock_read_count = 32                                  #

LARGE

db_block_buffers = 3200                                             #

LARGE

shared_pool_size = 9000000                                          #

LARGE log_checkpoint_interval = 10000

processes = 400

dml_locks = 500                                                     #

LARGE

log_buffer = 163840                                                 #

LARGE

sequence_cache_entries = 100                                        #

LARGE

sequence_cache_hash_buckets = 10                                      #

SMALL max_dump_file_size = 10240 # limit trace file size to 5 Meg each

global_names = TRUE

----//----

free command during high load:

             total       used       free     shared    buffers     cached
Mem:        642216     584456      57760          0       9272     336352
-/+ buffers/cache:     238832     403384
Swap:      1028120       8672    1019448

----//----

w command during high load:

3:52pm up 15 days, 11:59, 3 users, load average: 23.00, 21.57, 18.79 USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
----//----

there's over a hundred of the oracleorcl processes showing in a top:

000 501 21357 1 9 0 35668 3660 semop S ? 1:08 ora_pmon_or
000 501 21359 1 16 0 36568 11244 - R ? 60:30 ora_dbw0_or
000 501 21361 1 9 0 35428 3092 semop S ? 5:07 ora_lgwr_or
000 501 21363 1 9 0 35416 3516 semop S ? 1:16 ora_ckpt_or
000 501 21365 1 9 0 35544 20284 semop S ? 0:09 ora_smon_or
000 501 21367 1 9 0 35452 19220 semop S ? 0:04 ora_reco_or
000 501 21370 1 9 0 4996 1692 do_sel S ? 1:49 /u01/app/or
000 501 32120 1 9 0 35816 20528 tcp_da S ? 0:01 oracleorcl
000 501 32242 1 9 0 35892 20680 tcp_da S ? 0:02 oracleorcl
000 501 32246 1 9 0 35812 19504 tcp_da S ? 0:01 oracleorcl
000 501 32248 1 9 0 35592 20324 tcp_da S ? 0:01 oracleorcl
000 501 32250 1 9 0 35884 21108 tcp_da S ? 0:02 oracleorcl
000 501 32252 1 9 0 35588 20088 tcp_da S ? 0:00 oracleorcl
000 501 32257 1 9 0 35820 20408 tcp_da S ? 0:02 oracleorcl
000 501 32264 1 9 0 35820 21004 tcp_da S ? 0:02 oracleorcl
000 501 32279 1 9 0 35620 20552 tcp_da S ? 0:01 oracleorcl

----//----

Anyway help, hints, tips, what so ever is appreciated.

Thanks.

Friendly Regards
/Tom Received on Tue Nov 13 2001 - 09:38:47 CST