Maximo -- Use optimizer_mode to improve Maximo performance on Oracle database.

Body

On Oracle database, the default optimizer_mode is ALL_ROWS.  This optimizer_mode is inefficient for Maximo SQL queries, especially users screen response.

Here is an explanation of Oracle optimizer_mode from oracle documentation -- http://docs.oracle.com/database/121/TGSQL/tgsql_influence.htm

Relevant information extracted from the above link

• Best throughput (default)

  When you set the OPTIMIZER_MODE value to ALL_ROWS, the database uses the least amount of resources necessary to process all rows that the statement accessed.

  For batch applications such as Oracle Reports, optimize for best throughput. Usually, throughput is more important in batch applications because the user is only concerned with the time necessary for the application to complete. Response time is less important because the user does not examine the results of individual statements while the application is running.

• Best response time

  When you set the OPTIMIZER_MODE value to FIRST_ROWS_n, the database optimizes with a goal of best response time to return the first n rows, where n equals 1, 10, 100, or 1000.

  For interactive applications in Oracle Forms or SQL*Plus, optimize for response time. Usually, response time is important because the interactive user is waiting to see the first row or rows that the statement accessed.

As you can see from the above statements, the default optimizer mode (ALL_ROWS) uses less resources but has slower response. If Maximo users need fast response and better overall performance, then the optimizer_mode of FIRST_ROWS_N works better.   I have recommended FIRST_ROWS_100 to many customers and the response has been very good.

Here is the statement to set the optimizer_mode to FIRST_ROWS_100

SQL> ALTER SYSTEM SET OPTIMIZER_MODE='FIRST_ROWS_100';

Here is another useful link explaining how the oracle optimizer_mode works  -- http://www.dba-oracle.com/t_optimizer_mode_sql_execution_plans.htm