High-level recommendations for Oracle databases
A series of recommendations for improving performance on Oracle databases.
Tuning the input/output (I/O) subsystem
Disk I/O performance is the most important factor for good Oracle performance. Oracle is an intense user of I/O services and requires high performance disks to operate efficiently. A single RAID0+1 array striped across 5 SCSI disks should give adequate performance. If you have a high-end installation—for example, a very high load, many users, or many gigabytes of data—, you should consider splitting your data across disks.
Oracle provides a large amount of documentation on how to set up an ideal deployment. Testing confirms that following these guidelines can lead to performance improvements. For example, you can increase the performance by distributing the undo and working tablespaces across different disks. With a two disk setup, for example, you could have one disk containing the working tablespace and the other containing the undo tablespace. With a three-disk setup, you could have the undo tablespace stored on one disk and the working tablespace split into files that reside on the other two disks.
The decision of splitting these contents by disk or using RAID to distribute the content for you varies from installation to installation. The important point is that you want to spread the load across multiple disks.
Tuning memory requirements
Oracle databases require a large amount of memory to run efficiently. Oracle recommends configuring the Shared Global Area (sga_max_size) to use 80% of the RAM available on the server. You should be able to achieve good performance for WorldServer if you make at least 8 GB of RAM available to the computer that serves as the Oracle server. Larger organizations should consider 16-32 GB of RAM.
- Retain the default settings for the Large Pool Size (
large_pool_size) and the Java Pool Size (java_pool_size). - Set the Shared Pool Size (
shared_pool_size) to 30% of the remaining SGA size. - Set the Buffer Cache size (
db_cache_size) to the rest of the SGA size. - Set the PGA (
pga_aggregate_target) to 100 MB. Oracle will automatically manage the Sort Area Size based on this.
Oracle connection pool
WorldServer uses a connection pool of JDBC connections. Connections are initialized when users start up WorldServer and are shared by all threads within one WorldServer instance. Each connection consumes one Oracle connection process. You can control the size of the connection pool through the following property in the general.properties file: database_connection_pool_size.
Make sure that the value of the Oracle processes parameter accommodates all running WorldServer instances connected to the database. For most small installations, the default number of processes should be sufficient. However, in a clustered environment, use this formula to determine the minimum value for the processes parameter: processes=(nodes in cluster x database_connection_pool_size)+10
Adjusting these settings will eliminate most Oracle performance problems. In the rare case that you need to perform additional tuning, contact WorldServer Support.
When the connection pool is exhausted, WorldServer threads will just block for a free connection, however long it takes for another WorldServer thread to finish what it is doing and return the connection to the pool. You might see a lot of blocking (when threads are waiting for connections to become available) at times of heavy load if the connection pool is too low or if you already have the maximum connections to the connection pool. You can turn on the sql.ConnectionPool logging category to see information about how WorldServer is using connections. This logging category shows when connections are taken, when they are returned to the pool, how many free connections there are at any point, when connections are exhausted, and which threads are holding connections when the pool is exhausted.
- http://<ws-host>:<ws-port>/ws-legacy/ws_gate?&token=<token>&action=log&level=debug&category=sql.ConnectionPool
- http://<ws-host>:<ws-port>/ws-legacy/ws_gate?&token=<token>&action=log&level=warn&category=sql.ConnectionPool
Number of cursors
The recommended number of cursors is: 10 * database_statement_cache_size
You can configure the database_statement_cache_size property in the WorldServer general.properties file. If, for example, database_statement_cache_size is 50 by default, we would recommend a maximum cursors setting for Oracle of 500.
Under heavy load, when all connections are being used heavily, you might need to increase the value of open_cursors; however, the recommended setting should be sufficient in most cases.