Oracle 8.1.7 - Latch problem

[Per Tore Hoff]

Hello

We are running stresstest of Oracle 8.1.7 (64bit) on an HP900/L2000 with 4x440MHz CPU, 6GB memory and an EMC disk array.

We have setup the Oracle with MTS.

When we reach 400 users the Oracle server get problems with LATCHes, and the CPUs goes to 100%. I have almost none tablescans. Without MTS I reach 120 users.

We get appox. 150 sql execs /sec and 100 pars reqs/sec.


I think this is to bad. I have some idea of wrong values of parameters of UNIX Kernel or Oracle.

Here are the parameters of UNIX and Oracle:

UNIX Kernel parameters:
bufpages = 0
dbc_max_pct = 3
dbc_min_pct = 2
maxfiles = 512
maxfiles_lim = 1024
maxusers = sum of oracle users + 64
maxuprc = 4 * maxusers
nfile = ((16*(nproc+16+maxusers)/10+32+2*(npty+nstrpty)
nflocks = 200
nproc = (20+8*maxusers)
npty = 60
o_sync_is_o_dsync = 0
semmni = 64
semmns = 200
shmmax = up to max physical memory
shmmni = 200
shmseg = 12

Oracle parameters:
NAME VALUE
background_dump_dest /progs/oracle/admin/abw/bdump
Compatible 8.1.7
control_files /data/agr/oracle/data/ctl1abw.ora,
/data/agr/oracle/redolog/ctl2abw.ora
core_dump_dest /progs/oracle/admin/abw/cdump
db_block_buffers 131072
db_block_lru_latches 8
db_block_size 8192
db_domain world
db_file_multiblock_read_count 32
db_files 100
db_name ABW
db_writer_processes 1
dbwr_io_slaves 4
dml_locks 2000
global_names FALSE
instance_name ABW
large_pool_size 200000000
log_archive_dest /data/agr/oracle/data/arch01
log_archive_format ARC%S.%T
log_archive_start TRUE
log_buffer 1536000
log_checkpoint_interval 1000000
max_dump_file_size 10000
mts_dispatchers (address=(protocol=tcp) (host=100.19.0.15)) (dispatchers=10)
mts_max_dispatchers 10
mts_max_servers 400
mts_servers 100
mts_service ABW
open_cursors 500
processes 500
rollback_segments r01,r02,r03,r04,r05,r06,r07,r08,r09,
r10,r11,r12,r13,r14,r15,r16,r17,r18,r19,r20
service_names ABW,agr.world,AGR
sessions 2005
shared_pool_size 300000000
sort_area_retained_size 512000
sort_area_size 512000
text_enable TRUE
timed_statistics TRUE
user_dump_dest /progs/oracle/admin/abw/udump39 rows selected.

[Alexander M. Ermes]

Hi there.
Here some kernel parameters ( suggestion ) :
db_max_pct 12
db_min_pct 5
maxfiles 1024
maxfiles_lim 2048
nflocks 600
npty 300 ( if necessary )
semmni 512
semmns 2048
shmmni 1024
shmseg 400

Oracle parameters

db_block_buffers 10240 ( to be tested during uptime with cache hits )

don't set db_block_lru_latches

db_files 1022
dml_locks 5000
log_buffer 16384000
mts_max_servers 200
mts_servers 20
open_cursors 3000

Hope, i could help you.
Rgds
Alexander M. Ermes

[Andreas D. Skjervold]

Hi
Did some latch tuning myself earlier, and did then test the Oracle parameter spin_count, (without it giving me any boost).

The thing is that a higher value for spin_count will cause the process to spin and try to aquire a new latch before going to sleep waiting. The higher value the more spins, and you might get the latch aquired earlier than if the proces went to sleep, and thus getting better performance.

The hatch is that this uses CPU. So when you're having 100 % util, then this value should not be increased, but rather decreased (if you have it set).

Andreas

[Alessandro Bocchino]

First of all verify you have the latest Oracle
patchset installed. There are a lot of issues
regarding memory consumption and latches
in 8.1.7.0.0, I suggest updating to 8.1.7.2.1.

What kind of latches you get contention on?
I presume they are block buffer latches since
you have about 1GB in db_block_buffers.
Raising db_block_lru_latches should decrease
latch contention probability.
If you have an high hit ratio you could
consider decreasing db_block_buffers (the optimal size depends upon many factors including DB size but I would not stay under 500MB) and raising lru_latches to 16.

I would raise shared_pool to 600MB or even 1GB and monitor its usage, it should decrease
db_file I/O.

If you do not have tablescans I would lower
db_file_multiblock_read_count to 8, 32 could
be too much

Add the parameter cursor_space_for_time=true,
this should decrease parse requests.

Hope this helps, AB