SUMMARY: Anyone using SQL*Net successfully?

This is the summary of the replies I got to the following SQL*Net questions:

1. Are you satisfied with the response time, especially with forms using many SQL statements? An interesting example would be a form with a POST-QUERY trigger which executes many SQL statements for each record fetched or a multi-SQL-statement commit-time trigger.
2. What is your connect time? I mean if you start an Oracle session with SQL*Plus or SQL*Forms, how long does it take before you are connected?
3. Are you satisfied with working with SQL*ReportWriter running on the PC and data residing on the server? I.e. can you navigate through SRW screens quickly enough?
4. What is your exact configuration? SQL*Net over which protocol?

I got the following answers: (Y/N means Yes/No; Y/N in parentheses means the answer was implicit; times are in seconds; "-" means not answered)

1. 2. 3. 4. Contact
   -- ---- --- --------------------------------------------- -------
   Y 2 Y Sun client-Sun server, TCP/IP eik Y - Y Sun client-VMS server

Y  1-5    Y  Wollongong TCP/IP,                             TJambu
	     WIN SQL*Net for TCP/IP,
	     ethernet

N   ~1  (N)  WAN spread over Australia, Pyramids as         srh
	     APPLICATION servers,
	     UTS mainframe as DB server

Y    -  (Y)  20-40 * 386 PC clients, ethernet,              osh
	     HP9000-827 UNIX DB server,
	     SQL*Net TCP/IP,
	     Forms resided on NetWare file server

N fast    -  on the average 20-25 Mac and 3-5 PC clients,   simard
	     VAX 6360 DB server,
	     SQL*Net TCP/IP

Y fast    -  20 * 386 25 MHz 4MB RAM PC client,             dtrum
	       MS-Windows 3.0, Wollongong PA 2.0 TCP/IP
	       SQL*Net + NFS,
	     thin wire ethernet,
	     386 33 MHz 16MB RAM 2 GB SCSI HD DB+file
	       server running SCO ODT 1.1

where the contact persons were:

eik     Edward I. Kizys          eik_at_icd.ab.com
TJambu  Tony Jambu               TJambu_at_cmutual.com.au
srh     Shane Hocking            srh_at_scammell.ecos.tne.oz.au
osh     John O'Shaughnessy       osh_at_a00308.cray.com
simard  Ernest Simard            simard_at_vax.sonoma.edu
dtrum   David Trum               dtrum_at_jetssrg.af.mil

In addition to answers to my questions, I got the following suggestions:

Tony advices to use 8MB RAM on the PCs. He warns that ReportWriter is very slow and advices to use SQL*Plus if possible. However, I have managed to cope with 4 MB RAM and I have not (yet) had any problems with ReportWriter. I have noticed that it's important to minimize the NUMBER of SQL statement executions to the minimum. That means you should avoid executing SQL statements in a loop, for example.

Shane's configuration spans over many Australian states, so the net is a wide area network. It takes 8 ms for a packet to travel from Melbourne to Sydney. They had to avoid certain constructs when building their forms, such as post-query triggers on multi row blocks and explicitly declared cursors that may perform many fetches. He says that post-query triggers can be avoided by denormalization or by using views; however, views require some (easy) extra work because you have to do your locking and updating yourself (by using on-update triggers etc). Sometimes the business transaction had to be broken into smaller transactions, which meant that they had to renegotiate functionality with their users. With appropriate design it's possible to get very good response times for on-line transactions.

John says that they performed numerous tests which confirmed that the network was not a bottleneck in their configuration.

Ernest says that their HyperCard reports on the Mac are slow.

David told me about his performance tests: they wrote some Pro*C programs to simulate their typical transactions and to collect response time data accurately. Then they ran those programs on varying number of PC workstations and examined the response vs number of users curve. They observed that you should configure Oracle server memory right: Oracle shadow processes may eat much memory if given too much memory (sort area etc).

Thank you very much for your replies!

Well, it seems that you loose some speed in client-server environments if you code blindly or transfer a form from monolithic environment to client-server environment. The speed decrease may not be important; but for time-critical transactions you should be prepared to do some extra work in order to obtain acceptable speed.
--

Juhani Jaakola
jaakola_at_cc.helsinki.fi Received on Mon Jul 06 1992 - 08:40:26 CEST