Re: Oracle tuning on Windows NT

Brian -

1. ODBC is slow - much slower than SQL*Net native.
2. NT's throughput is slow, though that doesn't appear to be a problem here.

My immediate suggestion would be to take an insert transaction, convert it to a stored procedure and have the VB app call the procedure, passing it the requisite parameters. The less network traffic the faster the transaction will run; using a stored procedure to do this means the code may well stay in the shared pool, improving performance still more.

If that doesn't improve response, it sounds like your Windows client has a problem. Which Windoze is it running? Is the client swapping while the app runs?

FWIW, having the entire database on one drive is very bad. You should at least have index and data tablespaces on different drives, index and temp on different drives, rollbacks and redo logs on different drives. For a two-drive installation (not uncommon with NT servers), I would suggest system, index, rollbacks on one drive; data, temp and redo logs on another (if you're not running archivelog mode, multiplex the redo logs as yuo do the control files).

hth -

Regards,
jh

"Brian M. Biggs" <bbiggs_at_cincom.com> wrote:

>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/

Jared Hecker, CODBA       | Oracle  and Sybase Architect and DBA
jared_at_planet.net          | - consulting in the 
76276.740_at_compuserve.com  |   NYC/NJ region