5.4 Oracle Database
Contents
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 toEXACT
(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 toCHAR
and the database character set toAL32UTF8
orAL16UTF16
. - 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 ofMANUAL
can be changed toADAPTIVE
. - 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 aPROCESSES
value of at least500
.
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.
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.