Contention

[steeve123]

I have a oltp environement with about 20 user inserting orders.
When there is more than 5 user concurently inserting orders, they complain about
time it takes to save the order.


SQL> SELECT * FROM V$WAITSTAT;

CLASS COUNT TIME
------------------ ---------- ----------
data block 5146 3703
sort block 0 0
save undo block 0 0
segment header 1 2
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0

CLASS COUNT TIME
------------------ ---------- ----------
system undo block 0 0
undo header 254 62
undo block 203 29


I have lots of wait on data block .
Presently, all the tables and indexes has a freelist of 5 and initrans of 5.

Do i need to increase freelist and initrans?
or just freelist?

Any more advice to give me?

[gbrabham]

I would look at increasing the freelist. That's where the free
blocks are "housed". With concurrent inserts, the contention
is with the freelist. I would try to get the freelist close
to the # of concurrent inserts.

Gregg

[steeve123]

I only have 1 datafile of 3 gig.
Does that would be a contention issue also?

[Mr.Hanky]

Tell applications to learn how to write their friggin code, or just shoot em!!

I doubt this is a hardware issue, but hardware may solve your problem, SHOOT 'EM!.


With Love

MH

[steeve123]

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 9,676 9,842 51.78
latch free 163,725 6,475 34.06
db file scattered read 1,755 1,477 7.77
log file sync 294 368 1.94
control file parallel write 218 177 .93



Here's my top 5 wait event, does anyone has advice?