High-level recommendations for Oracle databases
Tuning the 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. Oracle recommends a highly distributed disk layout, as described in the chapter "Optimal Flexible Architecture" in the Oracle Administration manual. SDL generally does not require as complicated a setup as Oracle describes. We have found that a single RAID0+1 array striped across 5 SCSI disks gives adequate performance for the Oracle database. However, if you have a high-end installation — for example, you have 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 by SDL confirms that following these guidelines can yield performance improvements. For example, it is possible to 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 table space is split into files residing on the other two disks.
The decision as to whether you split these contents by disk or whether you use RAID to distribute the content for you is up to you and will vary from installation to installation. The important point is that you want to spread the load across multiple disks.
Tuning memory requirements
Oracle requires 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. In our experience, you can 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. We also make the following recommendations:
- 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 at WorldServer startup and are shared by all threads within one WorldServer instance. Each connection consumes one Oracle connection process. The size of the connection pool is controlled by the general.properties setting: database_connection_pool_size.
Ensure that the Oracle processes parameter is set high enough to accommodate 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 nearly all Oracle performance problems. In the rare case that you need to perform additional tuning, contact SDL 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 configured too low, or if you already have the maximum connections to the connection pool. There is a sql.ConnectionPool logging category that can be turned on to show 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://host:8080/ws-legacy/ws_gate?&token=<token>&action=log&level=debug&category=sql.ConnectionPool
http://host:8080/ws-legacy/ws_gate?&token=<token>&action=log&level=warn&category=sql.ConnectionPool
Number of cursors
10 * database_statement_cache_size
database_statement_cache_size is configured in WorldServer general.properties.
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 open_cursors; however, the recommended setting above should be sufficient in most cases.