Memory sizing on NT/Netware/Linux

We are going to move our application from using a file based (non-SQL) to Oracle. I am new to Oracle, have used it now for some time in a lab- environment but not on a real production site so far.

Our application is small when it comes to database size (between 50 MB - 500 MB) and users (an installation has between 5-50 user) but is using about 200-250 tables and around 1000 data entry forms and reports (I would consider this a medium-to-large application in this sense).

The Oracle o/s environments which will be my main interest is NT, Netware and Linux (NT and Novell to start with which is what our customers are using today. However, I see Linux as an interesting choice for a server o/s as well).

I have experimented and read some books and documentation about Oracle, I understand the concepts of Oracle tuning and I understand that you can't give any general tuning recommendations (like increase parameter X and everything goes faster) and it depends on the application and usage of the application. However, I need some advice on initial tuning and memory requirements (need to recommend our customers how much memory they need in their servers). So far I have not found any recommendations for an initial setup.

Our application does not use any large result sets and is using a
"middle-ware" software which turns record-oriented commands into SQL
commands (using OCIC and have some optimization built into the software) and uses cursors for all data retrieval. This works very well. We will add embedded SQL to speed up some processes like batch updates in the future.

Let say we have an installation with 10 concurrent users and their usage range from light data entry, running small reports to heavy data entry. Server o/s is NT or Novell and nothing else is running on the server (but the server might be used as file sever but that usage will not be heavy, only storing some Word documents etc). Only one Oracle instance will be used and it is Oracle 8.0.5.

1. How much memory would you recommend in the server? Do you have any
   "rule-of-thumb" to calculate the memory requirements like X MB/user? I
   might be wrong but I believe that 128-256 MB would be enough. Am I right or wrong? I have one test server (NT) with 196 MB with a 31000 kb SGA (5000 x 2048 db buffers, 30M shared pool size) and I have run with 10-15 concurrent sessions and memory usage goes only up to a bit over 40 MB (that means about 40-50 MB memory is free)
2. Any suggestions on how to size the SGA (buffers and shared_pool_size) with:

NT 128 MB
NT 256 MB
Netware 5 128 MB
Netware 5 256 MB

3) How much does the PGA take in these environments?

4) How about O/S file caching. I have not seen any recommendations on these amtters as well. Isn't the memory used for caching better used for Oracle. And shouldn't write-caching (at the o/s level) be turned off? If the server goes down and there is unwritten data in the o/s cache which Oracle thinks it has written to disk, I guess this would make recovery impossible. Or does Oracle flush the o/s cache buffers when writing to disk? It is strange that the books I have read does not mention anything about this

Magnus Received on Wed May 12 1999 - 19:18:55 CDT