Optimizing Oracle instances

Question

I am working with a team that develops an application which works on SQL Server and Oracle.

SQL Server has the concept of an instance, which can house multiple databases. Oracle 10g requires one instance per database (and can allow more for redundancy), so for each database we run, we have a completely separate set of processes, and thus much greater memory usage.

Because of this, we've started moving more to having one instance with separate schemas. However, we still want to keep separate customers' (or dev machines') data separate.

Most of our instances are created using locally developed scripts (for example, oradim on Windows).

What can be done to cut down on the memory usage requirements, footprint etc, of Oracle instances, so that multiple instances can run safely on one machine? Is Linux or Windows a better host? Can we make easy gains by disabling extra features (data mining, Oracle Text etc) that we don't need?

Answer 1

Memory IMHO is the most important for Oracle performance.

Running multiple databases implies keeping multiple sets of cached SQL and PL/SQL, multiple data cache for system tables, etc.

If you just need to keep the data separate, you may create different TABLESPACES for different users. You'll still have to share LOGFILES though.

Disabling extra features like Data Mining won't help you much, as they do not consume memory when not being used.

You'll certainly need to lower any memory values, but it's hard to tell which you should keep and which you need to lower without seeing you database design.

As a very imprecise rule of thumb, if you have OLTP database, that is little tables and high level of concurrency, you probably should sacrifice sort_area_size and hash_area_size, but keep db_block_buffers as high as possible.

If you have large tables using HASH JOINS and MERGE JOINS, you'll need sort_area_size and hash_area_size for efficient joins and ordering, but you can decrease db_block_buffers, as you'll not be able to cache these tables anyway.

Linux and Windows do not differ much in terms of Oracle performance. Linux, though, graces LOCK_SGA. Windows tries to do it too, but can swap out in tough memory conditions.