Slow Rendering of AAI Job Run History Due to Stale Oracle Statistics
search cancel

Slow Rendering of AAI Job Run History Due to Stale Oracle Statistics

book

Article ID: 429279

calendar_today

Updated On:

Products

Automation Analytics & Intelligence

Issue/Introduction

Users experience sluggish performance or timeouts when attempting to view the Jobs > Run History page in the Automic Automation Intelligence (AAI) interface. Data retrieval may take 90+ seconds.

Logs indicate that specific database queries on the JobRun table are taking over 90 seconds to execute. This issue persists even after upgrading the AAI server.

jaws.log Message: WARN [QueryTimingService] <1,593,246> done; elapsed: 0:01:31.3; expected: 0:00:15.0

 

The specific query was like below:

SELECT 
    jr.jobRunId, 
    jr.startTime, 
    jr.endTime, 
    jr.lastJobStateType, 
    jr.runType, 
    jr.extRunId, 
    jr.jawsNumberRetrys, 
    jr.jobRunContextId, 
    jr.runningTime, 
    jr.exitCode, 
    jr.numberRetrys, 
    jr.latency1, 
    jr.latency2, 
    jr.latency3, 
    jr.latency4, 
    jr.machine, 
    jr.triggerName, 
    jr.jobId, 
    jj.jobName, 
    jr.resourceWaitTime 
FROM 
    JobRun jr 
    INNER JOIN JawsJob jj ON jr.jobId = jj.jobId 
WHERE 
    isFutureRun = 0 
    AND jr.jobSchedulerId = '<schedulerId>' 
    AND jr.jobId IN ('<jobId>') 
    AND endTime >= 1767900413705 
    AND endTime <= 1767986813705 
    AND endTime IS NOT NULL;

Symptoms

  • Slow UI Load: The "Run History" tab for jobs loads extremely slowly (9+ minutes).

  • Inefficient Execution Plans: Database execution plans show the Oracle Optimizer selecting an inefficient index (e.g., XIE9JOBRUN) instead of the specific composite index (e.g., XIE14JOBRUN).

  • Data Mismatch: Discrepancies exist between the row counts in the database metadata and the actual table row counts.

Cause

This issue occurs in environments using an Oracle database where the database statistics have become stale.

  • Primary Cause: The Oracle Scheduler responsible for collecting and storing database statistics was not running.

  • Technical Detail: Because statistics were not updated, the Oracle Optimizer utilized stale metadata. This led it to select an inefficient index (XIE9JOBRUN) for queries filtering by Job ID and End Time, rather than the optimal index (XIE14JOBRUN).

Resolution

Prerequisites:

  • Access to the Oracle Database with sufficient privileges to gather statistics (e.g., DBA or Schema Owner).

  • Ability to stop AAI services if required by internal maintenance policies (Note: Statistics can often be gathered online without downtime).

Step 1: Identify the Root Cause

Verify if the row counts in the metadata match the actual data. Significant differences between num_rows and count(*)indicate stale statistics.

Run the following SQL queries:

SQL
 
-- Check metadata statistics
SELECT table_name, last_analyzed, num_rows, stale_stats 
FROM dba_tab_statistics 
WHERE owner = 'AAI' 
AND table_name IN ('JawsJob', 'JobRun');

-- Check actual row counts
SELECT COUNT(*) FROM JawsJob; 
SELECT COUNT(*) FROM JobRun;

Step 2: Refresh Database Statistics Manually

Ask your DBA to Run the following PL/SQL block to force a full update of the schema statistics.

Note: Replace 'AAI' with the correct schema owner name if different.

SQL
 
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'AAI',
    cascade          => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;
/

Expected Result: The command completes successfully, updating the optimizer statistics.

Step 3: Verify Performance Improvement

  1. Navigate to Jobs > Run History in the AAI interface.

  2. Reload the page.

  3. Expected Result: The page should load within a normal timeframe (seconds, not minutes).

Step 4: Permanent Fix

Engage your Database Administrator (DBA) to investigate why the automatic statistics collection jobs are failing.

  • Action: Ensure the Oracle Scheduler responsible for updating statistics is enabled and running correctly to prevent recurrence.

Verification of Success

  • [x] Run History loads quickly in the UI.

  • [x] Oracle Explain Plans for the affected queries now utilize the optimal index (e.g., XIE14JOBRUN)

Additional Information

  • Prevention: Ensure that standard Oracle maintenance tasks, specifically the automatic gathering of optimizer statistics, are scheduled and executing successfully.
Powered by Wolken Software