Oracle tuning on Windows NT

From: Brian M. Biggs <bbiggs_at_cincom.com>
Date: 1997/01/22
Message-ID: <32E66973.1C62@cincom.com>#1/1

All,

First, why is activity in the new newsgroups so low? Is everyone else still using the old group? I thought it went away in December, but activity in the new groups is still much lower than it ever used to be.

Now my real question: we are running a 16-bit Visual Basic 3.0 application (through ODBC) against Oracle Workgroup Server 7.3.2.2.1 on a Windows NT 4.0 machine (dual Intel P166 CPUs). Performance is pretty bad with more than a single user, and even a single user operation takes about 1 minute. The particular operation is doing about 95% inserts.

I've done all of the typical tuning exercises that I could think of with Oracle in general, but I haven't found anything that really stands out as a problem. The CPUs are not being stressed at all, the hit ratio is steady around 93%, disk activity is non-existent, no disk sorts. The particular operation is doing 95% inserts.

Below is some information about the particular transactions being performed. Does anyone have any ideas about how to improve the performance of Oracle? Or, with the information provided about the transaction, is it just going to take a long time? I have all of Oracle and the database on one drive (C:), but the machine has 80MB and is not doing any disk I/O. It just seems like this operation isn't really doing all that much and that the machine should be able to turn out a much faster response than what we're getting. Any clues?

Is there possibly a problem with the excessive number of SQL*Net trips? Is this the classic "slow ODBC" problem that we're not going to get away from?

SVRMGR> show sga

Total System Global Area       8367384 bytes
Fixed Size                       35700 bytes
Variable Size                  7172516 bytes
Database Buffers               1126400 bytes
Redo Buffers                     32768 bytes


----------------------------

I looked at V$SESSTAT for a particular session before and after the operation and took the difference. Here are a few of the statistics that I felt stood out.

SQL*Net roundtrips to/from client                  3531
bytes received via SQL*Net from client          1042025
bytes sent via SQL*Net to client                  52467
calls to kcmgas                                      55
calls to kcmgcs                                     738
calls to kcmgrs                                    2245
consistent gets                                    2089
db block changes                                   3880
db block gets                                      4366
free buffer requested                               720
physical writes                                       0
recursive calls                                     921
redo entries                                       1926
redo size                                        859363
redo small copies                                   588
sorts (disk)                                          0
user calls                                         3531


----------------------------

I thought about increasing db_block_buffers, but the hit ratio is already pretty high, and I didn't think it would help. Worth trying anyway?

Thanks,
Brian

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/

Received on Wed Jan 22 1997 - 00:00:00 CST