AAI 24.4.1 Upgrade: Performance and Database Indexing Guidelines

Products

• Automation Analytics & Intelligence

Issue/Introduction

The upgrade to Automation Analytics & Intelligence (AAI) version 24.4.1 introduces critical schema changes designed to reduce application startup time and improve overall performance. This is achieved by creating new SQL indexes on two of the largest tables in the AAI database: RecentStateType and JobRun.

Because these tables are typically very large, the indexing process requires careful pre-upgrade planning to avoid timeouts or performance degradation during the upgrade script execution.

 

Symptoms of Stale Statistics or Insufficient Space

• Upgrade script appears to hang or take multiple hours to complete.
• Inefficient execution plans where the Oracle Optimizer selects sub-optimal indexes (e.g., selecting XIE9JOBRUN instead of XIE14JOBRUN).
• QueryTimingService warnings in jaws.log showing elapsed times significantly higher than expected (e.g., 90+ seconds for standard queries).

Environment

AAI Upgrades to 24.4.0-1 and later

Resolution

Pre-Upgrade Checklist

1. Database Statistics Refresh

Ensure that database statistics are current. Stale metadata can cause the optimizer to choose slow indexing methods. Run the following PL/SQL block as the AAI schema owner:

sql

-- Refresh statistics for the primary tablesEXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'JOBRUN', estimate_percent => 10);EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'RECENTSTATETYPE', estimate_percent => 100);

2. Tablespace Capacity Verification

Index creation requires significant physical and temporary space. Verify these requirements with your DBA:

• Index Tablespace: Free GB must be ≥ 20% of the current JobRun table size.
• TEMP Tablespace: Oracle must sort the full table to build these indexes. Ensure free TEMP space is ≥ 1.5× the estimated size of the JobRun table.

3. Backup & Data Protection

• Full Backups: Perform a full database export and a complete backup of the AAI application directory.
• Scheduler Data Export: Export scheduler connection strings as a precaution before initiating the upgrade:
  sql

  SELECT JOBSCHEDULERID, SCHEDULERNAME, SCHEDULERCONNECTIONCONFIG FROM jobscheduler;

---

Upgrade Process Guidelines

1. Stop AAI Services: Ensure all AAI services are fully stopped before running the database upgrade script.
2. Monitor Index Creation: Do not attempt to start the AAI application until the SQL schema upgrade script has fully finished. Starting the application prematurely during index creation can lead to severe database locking.
3. Post-Upgrade Verification: After the upgrade, verify that queries on the JobRun table are utilizing the new composite indexes for optimal performance.