AAI Scheduler Deletion Process is Slow or Hangs

Products

• Automation Analytics & Intelligence

Issue/Introduction

When deleting a scheduler in Automation Analytics & Intelligence (AAI), the process may appear to hang or take several days to complete. In the AAI logs, the DeleteSchedulerHelper may show it is stuck on specific steps, such as Step 3 (JobRunPredJobRun) or Step 13 (JobRun). Additionally, if the AAI server is restarted or rebooted during this process, the deletion will roll back, requiring the process to start over.

Environment

AAI 24.0

Cause

• Large Data Volume: Tables like JobRun_PredJobRun and JobRun are often the largest in the AAI database. Deleting millions of rows from these tables is a resource-intensive operation Determine Scheduler Delete Status.
• Database Performance: Index fragmentation, stale database statistics, or blocking sessions can significantly slow down DELETE statements Determine Scheduler Delete Status.
• Process Interruptions: Restarts of the AAI service during a deletion trigger a rollback of the database transaction, losing all progress made up to that point Determine Scheduler Delete Status.

Resolution

1. Monitor Progress

You can monitor the progress of the deletion by checking the row counts of the large tables for the specific scheduler. 

The jaws.log will show messages with which step is is currently working on and the associated table like below:

INFO [DeleteSchedulerHelper] Step 3>Deleting JobRunPredJobRun

You can query the associated table and filter by the Scheduler Name to see the progress.

 

Below is the list of all steps that AAI must get through to finish a scheduler deletion.

Deletion Sequence

Step	Target Entity	Action
1	JobCondition	UPDATE
2	JobStreamRun_JobRun	DELETE (×2)
3	JobRun_PredJobRun	DELETE (×2)
4	Alert	DELETE
5	Alert	DELETE
6	TempEvent	DELETE
7	DuplicateEoids	DELETE
8	JobStreamAverage	DELETE
9	SLACondition	DELETE
10	JobStreamSLA	DELETE
11	JobStreamRun	DELETE
12	UnresolvedJobRunCausedBy	DELETE
13	JobRun	DELETE
14	JobStatisticsModelAttr	DELETE
15	JobStatisticsModel	DELETE
16	JawsJobAverage	DELETE
17	TrendDetail	DELETE
18	RecentStateType	DELETE
19	AlertActivation_DistType	DELETE
20	AlertActivation	DELETE
21	JobCondConj_JobCond	DELETE
22	JobCondition	DELETE
23	GlobalVariable	DELETE
24	JobStream_StartJob	DELETE (×2)
25	JobStream_Job	DELETE (×2)
26	PredictedDuration	DELETE
27	JobProperty	DELETE
28	JobTriggerDate	DELETE
29	JobTrigger	DELETE
30	CalendarPattern	DELETE
31	JawsCalendar	DELETE
32	BusinessDomain_JobStream	DELETE
33	JobStreamPerformance	DELETE
34	JobStream	DELETE
35	Candidate	DELETE
36	CrossInstanceJobCondition	DELETE
37	JobEvent	DELETE
38	RecommendedStartTime	DELETE
39	UniversalJobProperties	DELETE
40	JawsJob	DELETE
41	JawsResource	DELETE
42	JawsJobType	DELETE
43	JawsMachineMetadata	DELETE
44	JawsMachine	DELETE
45	UniversalResourceHistory	DELETE
46	UniversalResourceAction	DELETE
47	UniversalResource	DELETE
48	CrossInstanceReference	UPDATE then DELETE
49	WCCAutosysServer	UPDATE (×2)
50	WaitingRoomEvent	DELETE
51	CrossSchedulerResolutionInfo	DELETE then UPDATE
52	JobScheduler	DELETE

DBA Action Items

It would be worth having your DBA check the following three items. (Note: You will need DBA-level rights to run these queries. If you get a message like "Table or View does not exist", it is a permissions issue.)

1. Check for index fragmentation

Run this periodically to confirm the count is decreasing Determine Scheduler Delete Status.

2. Database Maintenance

Have a Database Administrator (DBA) perform the following checks to ensure optimal performance:

• Index Fragmentation: Check the health of indexes for AAI tables and rebuild them if fragmented Determine Scheduler Delete Status.
• Stale Statistics: Ensure that database statistics are up to date. Stale statistics can cause the Oracle optimizer to choose inefficient execution plans Determine Scheduler Delete Status.
• Blocking Sessions: Check for other database sessions that might be blocking the AAI deletion process Determine Scheduler Delete Status.

3. Best Practices

• Set Scheduler Offline: Before starting the deletion process, set the scheduler to Offline to prevent new data from being processed while the system is trying to delete existing data Determine Scheduler Delete Status.
• Avoid Restarts: Ensure that no maintenance tasks or automated reboots will interrupt the AAI service during the deletion window Determine Scheduler Delete Status.