slow down OLTP server
- From: "Ujang Jaenudin" <ujang_jaenudin@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Apr 2006 12:55:20 +0700
dear all,
I have oltp server with these specifications:
ora9ir2.0.1
redhat7.1
ram 2.5gb
sga 1.5gb
buffer cache 512mb
large pool 608mb
shared pool 304mb
i/o raid 5 scsi
shared server, user around 25, but session can be >100
SQL> show parameter shared_server;
NAME TYPE VALUE
------------------------------------ ----------- ------------
max_shared_servers integer 20
shared_server_sessions integer 165
shared_servers integer 1
statspack:
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 17-Apr-06 09:34:30 68 24.6
End Snap: 2 17-Apr-06 10:09:48 76 26.1
Elapsed: 35.30 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 512M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 512K
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write 731 0 15 20 4.5
log file sync 150 0 1 9 0.9
db file sequential read 166 0 1 4 1.0
process startup 2 0 0 31 0.0
latch free 6 2 0 7 0.0
log buffer space 1 0 0 21 0.0
SQL*Net more data to client 553 0 0 0 3.4
control file sequential read 261 0 0 0 1.6
SQL*Net break/reset to clien 13 0 0 1 0.1
log file parallel write 607 607 0 0 3.7
db file parallel write 268 134 0 0 1.7
db file single write 16 0 0 0 0.1
direct path read 68 0 0 0 0.4
LGWR wait for redo copy 8 0 0 0 0.0
direct path write 16 0 0 0 0.1
enqueue 1 0 0 0 0.0
virtual circuit status 40,609 13 2,166 53 250.7
wakeup time manager 69 67 1,988 28814 0.4
SQL*Net message from client 40,697 0 1,228 30 251.2
SQL*Net message to client 40,700 0 0 0 251.2
-------------------------------------------------------------
please give me suggestion to tune this server.
my thinking are:
1. add more disks to differentiate for each functionality.
e.g. datafile, log file, ctl file should has their own disk?
2. can i activate async io on this server?
3. should i add shared server? or change to dedicated server?
3. any other clues?
regards
--ujang
Other related posts:
