5.4 Oracle Database

Note: Co-Location

Because all functionality is based on database performance, the database should be a key focus for performance tuning. TRIRIGA® recommends that the database server be co-located on the same subnet as the application and process servers.

For this reason, cloud database offerings such as Oracle Database Exadata Cloud Service are not recommended for adequate performance. However, hosting the entire TRIRIGA infrastructure in the cloud where the TRIRIGA infrastructure is hosted on the same subnet is a viable configuration for good performance. In addition, customers have reported good performance using Oracle Exadata Database Machine in on-premise deployments.

Always check the IBM TRIRIGA Compatibility Matrix for the latest database versions and fix packs supported. Performance issues where the database server is not co-located with the application and process servers are not supported.

5.4.1 Oracle Database Server Tuning

On an Oracle database, specify the following initialization parameters:

  • Set the CURSOR_SHARING parameter to EXACT (Oracle default) if it's not already configured that way. This ensures ideal performance because TRIRIGA uses bind variables extensively.
  • If multibyte support is required, be sure to set the NLS_LENGTH_SEMANTICS parameter to CHAR and the database character set to AL32UTF8 or AL16UTF16.
  • Use the Program Global Area (PGA) setting WORKAREA_SIZE_POLICY=AUTO to automatically size work areas.
  • Ensure the Optimizers (OPTIMIZER_FEATURES_ENABLE) are set to your current Oracle version and not to an older one.
  • The PARALLEL_DEGREE_POLICY specifies whether automatic degree of parallelism, statement queuing, and in-memory parallel execution will be enabled. The default of MANUAL can be changed to ADAPTIVE.
  • Increase the process number to handle more concurrent users by setting the PROCESSES= initialization parameter to the maximum number of users who can access Oracle concurrently. A general guideline is to add up all maximum JDBC connection pool values from each JVM and add another 100 for Oracle. For example, adding an application server (200), plus process server (200), plus Oracle (100), would require a PROCESSES value of at least 500.

In Oracle 11g and 12c, automatic memory management allows for automatically readjusting the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, and java_pool_size) based on existing workloads. Initialization parameters that are used to enable this feature are: SGA_TARGET and SGA_MAX_SIZE.

The memory_target parameter is dynamic (changeable with "alter system" commands), where RAM can be de-allocated from an instance’s SGA/PGA and re-allocated to another instance. Initialization parameters that are used to enable this feature are: MEMORY_TARGET and MEMORY_MAX_TARGET. Consult with your database administrator to size these parameters to take advantage of this feature.

Use automatic memory management. For more details, see Enabling Automatic Memory Management.

For assistance in performing Oracle database tuning, see the Oracle Database Documentation Library.

a. Oracle RAC Considerations

Many customers think that many smaller nodes in a Real Application Clusters (RAC) configuration will yield better performance. This configuration may be fine for high availability, but small system-based RAC is not the best solution for performance, and overall, ends up hurting performance. To scale Oracle for performance, the solution is vertical scalability, that is, adding CPU, RAM, and disk to the individual nodes. Many performance issues are the result of environmental configuration and the choice to use smaller servers versus bigger ones.

RAC requires more resources because it must maintain the cluster state across all the nodes, as well as serve the data out to the applications. With resources consumed by the "busy work" to maintain the cluster, each node is less powerful than if it were in a single instance. RAC is good for being highly available, but at a great cost of maintainability and individual system resources. The nodes must be vertically scaled to handle the load of the requests from the application tier. But adding nodes horizontally will only slow down the environment.

5.4.2 Oracle Application Platform Indexes

The following performance tuning indexes can be added to Oracle databases that are running TRIRIGA Platform 3.5.x. These recommended Oracle performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the Oracle database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. Oracle database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

5.4.3 Reserve Indexes for Oracle

Any implementation of Reserve should be tuned to include appropriate indexes for performance improvement. The following indexes were identified to help increase performance dramatically for reserve queries by the TRIRIGA performance team on Oracle, but you should review and tune for your specific implementation.

The following performance tuning indexes can be added to Oracle databases that are running TRIRIGA Platform 3.5.x. These recommended Oracle performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the Oracle database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. Oracle database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

5.4.4 Lease Indexes for Oracle

Performance benchmark testing for Lease was performed on the DB2 database platform. However, the findings from that platform may also be applicable to Oracle Database. Your database administrator can take the identified queries from the DB2® results and use the index adviser for your database platform to see what indexes are recommended on that platform.

Note: Customizations

The DB2 results are based on out-of-the-box queries and does not take into account any additional columns that may be in your deployment. For more information, see the 5.3.4 Lease Indexes for DB2.