Performance: High disk reads on OH table during Lucene index updates

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

An Oracle database hosting the Automation Engine (AE) experience high disk reads and performance degradation. Analysis of Oracle AWR reports identifies the following SQL (sql_id: f88qp2c4sf5ua) as a top contributor:

sql

SELECT OH_Idnr, OH_Client, OH_OType, COALESCE(OH_ModDate, OH_CrDate) AS OH_ModDate, OFS_ModDate, OH_LastDate FROM OH LEFT JOIN OFS ON OH_Idnr = ofs_oh_idnr_o WHERE OH_DELETEFLAG = :0 AND (OH_ModDate > :1 OR OH_CrDate > :2 OR OFS_ModDate > :3 OR OH_LastDate > :4)

The execution plan shows a TABLE ACCESS FULL on the OH table and a hash join that may spill to the temporary tablespace.

 

Environment

• Automation Engine 12.x, 21.x, 24.x
• Oracle Database

Cause

The query is issued by the Java Work Process (JWP) with the indexing (IDX) role to update the Lucene search index. The OR predicate spans four different columns across two tables (OH and OFS). Due to the structure of this query, the Oracle optimizer cannot use a single index range scan and is forced to perform a full table scan of the OH table on every execution.

Resolution

Short-term Mitigation

To reduce the frequency of the full table scans, increase the polling interval for the search indexer.

1. Navigate to UC_CLIENT_SETTINGS in Client 0.
2. Locate the key INDEXSEARCH_INTERVAL.
3. Increase the value from the default of 10 seconds to a higher value, such as 60 or 120 seconds.
   • Note: Increasing this value means newly created or modified objects may not appear in search results for up to the configured number of seconds.

Maintenance

Ensure that the OH table size is managed by regularly purging deleted objects. Use the DB Reorg utility (ucybdbre) to remove records flagged for deletion, as the cost of the full scan scales linearly with the total row count of the table.

Additional Information

For more information on Lucene indexing roles and expected behaviors, see Length of time for building search / lucene indexes.