Tuning the database

Use indexing and query tuning to tune your database for optimal performance.

Indexing

Indexes use key parts of data from a table in a binary structure to enhance searching capability. Each record in the table has data that is associated in the index. Regularly evaluate the following opportunities for indexing:
  • Analyze the Maximo® Real Estate and Facilities logs.
  • Use the Performance Analyzer for long-running queries.
  • Use database tools such as Oracle AWR Reports and DB2® snapshots to identify long-running queries.

When you identify long-running SQL queries, use the database platform's query tuning utilities to identify potential indexes to improve the query response time. Oracle SQL Developer, IBM Data Studio, and Microsoft SQL Server Management Studio each provide SQL tuning utilities. These utilities operate similarly. You input the SQL query and select the option to tune the query. The utility then lists any potential indexes that can be created to improve query performance. For more information, see the documentation for your database platform.

Indexing increases search speeds. However, a drawback of indexes is that for each insert, update, or delete, the index must also be updated. Database administrators often apply many indexes to a table to enhance searching and then sometimes find that other activities are slower. Review and test all potential indexes to ensure that you have the optimal balance for searching and for updating tables.

Maximo Real Estate and Facilities includes several built-in indexes against IBS_SPEC_ASSIGNMENTS. However, you must analyze IBS_SPEC_ASSIGNMENTS indexes to ensure that the indexes improve performance. Do not add indexes to IBS_SPEC_ASSIGNMENTS because they can cause issues in Maximo Real Estate and Facilities.

Indexes depend on their configuration and data composition. Analyze the configurations and data for indexes that might be helpful for your database configuration. The indexes that are included with Maximo Real Estate and Facilities help in most use cases.

Also, check existing indexes against new indexes to make sure that there is no overlap. For example, a new index might recommend an index on columns A, B, and C. However, there might be an existing index on columns A and B. Instead of having multiple indexes, modify the existing index to add column C after column B.

Customizing Maximo Real Estate and Facilities can change the way that you select information from the database. Some customizations include more tables and columns. If you customize Maximo Real Estate and Facilities, compare indexes to the user functions that use them. You might need to alter existing indexes to include new columns and create new indexes for new tables.

Use the Database Table Manager tool to view, alter, drop, and create database table indexes because they are then managed by the product. For more information, see Database tables.

Analyze the IBS_SPEC_ASSIGNMENTS table to understand your data

Module Level Associations (MLA): If your database is converted to enable Module Level Associations, the ALLOW_REVERSE_ASSOCIATION property is not supported. The Reverse Association flag in queries and reports is ignored, and only forward associations are allowed in queries and reports.

Maximo Real Estate and Facilities stores all associations between records in the IBS_SPEC_ASSIGNMENTS table. Over time, this table can exceed 100 million rows. Processes like the Platform Maintenance Scheduler, which was named Cleanup Agent, take longer to finish because it queries this table to clean associated data. Analyze your IBS_SPEC_ASSIGNMENTS table to identify data in your system that you can delete to reduce the size of your table.

Performance tuning indexes

Tune those indexes that are the result of iterative performance tuning cycles. The indexes that are listed are not included in the Maximo Real Estate and Facilities base product unless otherwise stated. For more information, see:

These indexes provide performance improvements when measured against a broad performance test workload. Add these indexes based on their respective database platform. However, performance gains might vary depending on factors such as application usage, load patterns, hardware sizing, application, and database server configuration. Database administrators must monitor databases for efficient index usage to determine the overall impact that is produced by applying the indexes and to determine more indexes that improve performance based on situational and data composition needs.

Tuning queries

Review custom queries and reports for efficient SQL and use of indexes. In all of these cases, improving the efficiency of user queries also improves the efficiency of the workflows that use them.

Many long-running queries and reports are often improperly filtered. In addition, most of the filters in reports and queries are generated by users with run-time filter operators and by association filter operators that are defined in the Report Manager. While these filters are powerful features of Maximo Real Estate and Facilities, they can produce inefficient SQL. For more information, see Query tuning in Query and report performance.