Maximo and setting ORACLE's optimizer_mode database setting to FIRST_ROWS - #explainBestPractices mini-series

This is the first article in the #explainBestPractices mini-series. This series will explain the importance of following key guidelines in the IBM's Best Practices for System Performance.

This article starts by explaining about ORACLE's optimizer_mode setting and why it is important to change it to FIRST_ROWS.

This setting is often overlooked when a system is installed. Changing this setting can deliver a performance boost when querying the database.

How data is stored in a table and why an index can help speed up a query

Indexes store pointers to the data that matches the index criteria e.g. siteid = BEDFORD

The database uses an algorithm to calculate the cost of using an index or querying a database table.

The optimiser will calculate the cost and then choose the option with the lowest cost.

It writes an explain plan that describes the option and shows the associated costs.

The optimiser can be configured to change the costs of a particular option for all queries e.g. reduce the cost of a full table scan. This is achieved by setting the optimizer_mode database setting to one of the modes which applies policies to influence the choice.

IBM recommend making this change in several technotes.

The technotes don't explain why this should deliver a benefit. This article explains why this setting is important.

ALL_ROWS

The default optimiser mode is "ALL_ROWS" – this is set so the database will gather all the rows and then return them to the requesting process. This returns the best results for processes that will display many rows e.g. Cognos/BIRT report.

It means that the database process waits for all the records to be returned before any records are sent to Maximo's interface. The user could wait for a long time if the query returns a lot of rows.

FIRST_ROWS

IBM recommend changing this setting to FIRST_ROWS.

This returns the first set of rows before all the other rows are returned. This gives the user a quicker response time.

Using indexes

The Maximo developers have designed the code and they deliver indexes that are designed to support that code.

The optimiser will only use an index if it will return less than 7% of the rows in a table. A substantial performance gain can be achieved when an index is used.

The optimiser will determine the 7% based on statistics that it has collected about the table. It will include details about the spread of the values e.g. how many asset records have a particular value in an indexed field (e.g. siteid). This type of meta data information (data about data) is used by the optimiser when it calculates if an index is appropriate.

Importance of statistics.

As data is inserted/updated/deleted the balance of the data changes e.g. inserting 100,000 assets for the BEDFORD site will change the percentage of rows in that table for site BEDFORD.

The cost of these calculations means that the costs are not automatically updated when row related operations occur. DBAs / system administrators can update these statistics.

The frequency of the calculations depends on several factors and the frequency could be different for individual customers.

Statistics should be generated on a weekly basis, or if particularly large volumes of data manipulation statements (inserts, updates or deletes) they should be gathered manually on a schema / table basis.

This is particularly important after dataloads because these can change the balance of the data in the table.

If the statistics are not up to date then the optimiser may use a full table scan because it believes that an index isn't appropriate.

This section give an indication of the type of information that is gathered:

Oracle optimiser statistics are a collection of data that describe additional detail concerning the database and the objects within it. These statistics are used by the query optimiser to ascertain the best execution plan for each SQL statement executed. These statistics include the following:

Table statistics

• Row count
• Block count
• Average row length

Column statistics

• The number of distinct values in each column
• The number of nulls in each column

Index statistics

• The number of leaf blocks
• Levels
• Clustering factor

System statistics can also be gathered and provide detail around the following:

• I/O performance and utilization
• CPU performance and utilization

Note that if statistics for tables and indexes are not kept up to date inappropriate execution plans can be generated and if particularly stale the optimiser will force full-table scans.

Why is FIRST_ROWS so important?

If the ALL_ROWS setting is used then ORACLE will ignore potentially useful indexes.

Setting this to FIRST_ROWS will encourage the optimiser to use indexes making it easier to see queries that are using full table scans because there are no appropriate indexes. A system administrator can then focus their investigations onto those queries and indexes that could help.

Will changing this setting deliver a significant benefit?

The impact will depend on the queries that are being executed. If there are lots of queries returning lots of rows then it could provide a lot of value.

Here are several areas in Maximo that could try to retrieve lots of rows:

• Start centre portal queries – I discuss these in this article - https://www.garudax.id/pulse/using-sql-tracing-start-centre-performance-problems-mark-robbins/
• Where clauses used in queries
• Reports

I talk about the importance of database tuning in my Database performance & DBA tips mini-series.

Vetasi advice

It is important to consider adding in extra indexes for new customisations. I have solved performance problems by adding in an index to support queries being executed by customisations.

Ensure that the table statistics are regularly updated to help the optimiser make the best recommendations.

Database problems can be tricky to investigate. Vetasi have advanced tools/processes that can dramatically speed up investigations into performance problems. Vetasi offer consultancy to resolve performance problems

If you suspect that your database may be contributing to a performance problem then do the following:

• Check the Maximo logs for BMXAA6720W log entries indicating Slow SQL statements
• Ensure that the table and index statistics are regularly updated for each pertinent schema to help the optimiser make the best recommendations
• Use DBMS Scheduler to schedule a job to collect the statistics
• Ask the DBA to run statspack reports or AWR reports (these can incur license costs) to investigate the SQL that is using the most resources
• Contact Vetasi to attend our SQL tuning optimisation course to get a better understanding about how Maximo builds its SQL statements and how they can be improved - This page gives details of the SQL tuning knowledge that I believe Maximo system administrators should have to avoid database performance problems
• Contact Vetasi to analyse your logs and provide a comprehensive report discussing the BMXAA6720W entries – this report is designed specifically for system administrators and helps identify the source of the problems. Vetasi Support customers just need to raise a support call for this.
• Contact Vetasi to provide a detailed list of the indexes that are in place on the system including warnings about table statistics that are likely to be out of date Vetasi Support customers just need to raise a support call for this.

This blog series

This article is one of a series of articles to help system administrators understand the Maximo logs and the underlying architecture.

If you like this article then please share or like it.

Whilst I support the wider Maximo community and encourage the spread of knowledge, when republishing content from this blog please include the originating author along with the article or parts of.

If people do find parts of this blog coming up in blogs/newsletters/communications then please contact me directly. I’m happy to connect on LinkedIn to discuss.

Disclaimer

The postings on this blog are my own and don't necessarily represent Vetasi's positions, strategies or opinions.

The materials on this site are provided "AS IS" and the author will not be liable for any direct, indirect or incidental damages arising out or relating to any use or distribution of them. Readers are advised to test any changes/recommendations thoroughly before use