Slow OLTP on 14CPU SunFire

[tamilselvan]

There are couple of issues:
1. Log Writer bottleneck & Init.ora file
2. MTS connection

== from you init.ora
log_buffer 2621440
log_checkpoint_interval 10000
log_checkpoint_timeout 0
SQL*Net message from client 3,085,839 0 5,278,952 17 54.7
virtual circuit status 1,361 60 243,750 1791 0.0
SQL*Net message to client 3,085,835 0 548 0 54.7
SQL*Net more data from clien 11,044 0 107 0 0.2
-------------------------------------------------------------

====
1. LGWR
I have gone through the statspack report.
Here are my observations:
log_checkpoint_interval is set to 10000 OS blocks. In most of unix OS, the OS block is 512 bytes. So when the LGWR writes 5,120,000 bytes on to the redo log file, the log file gets synchronized.That's why you see "log file sync waits" in the statspack report. This value, 10000 is too low. I usually set it to very high value (9M or 10M ) so that sync will occur after the log switch. Also, reduce log_buffer value to less than 1MB, unless your system is pure DW.
If you use JAVA, change autocommit to false. This is also one of the reasons for more log file sync wait.

2. MTS
Since the box has a huge physical memory(48 GB), I would not recommend to use MTS. MTS is basically designed for low memory systems. You can very well go for dedicated server connection.
MTS is suitable for pure OLTP transactions where it is expected that the transaction finishes its work in less than 45 seconds (Tom explained it in his first book, page number 87) and low data transfer between client and server.
From your statspack report the wait on the SQL*net message from and to client is very high. This is mainly because of poor MTS design. Today, big shops never use MTS. To improve the speed of data transfer between Server and clients, increase SDU and TDU also.

Other than these 2, I don't find any serious bottleneck from the statspack report.

What is the CPU_COUNT value?


Tamil