AutoSys 12.0 performance with Oracle 19c using Parallel Queries

Products

• CA Workload Automation AE - Scheduler (AutoSys)
• Autosys Workload Automation

Issue/Introduction

After switching an AutoSys r12.0 instance from using an Oracle 12.2 AutoSys DB (AEDB) to an Oracle 19c AEDB, performance is much slower. Memory consumption is very high on the Oracle 19c server and many Parallel queries are observed. The same queries are seen to be sequential while using Oracle 12.2.

Environment

Release : 12.0

Component : CA Workload Automation AE (AutoSys)

Cause

The AEDB indexes use a degree of parallelism of 1 by default. If this is set to any value higher than 1, this will enable Parallelism in Oracle 19c (unlike Oracle 12.2). Many parallel queries can cause performance issues and increased memory consumption on the Oracle server.

You can check the degree of parallelism that is set on all AEDB indexes with this query:

select index_name,degree from dba_indexes
where owner='AEDBADMIN'

This additional query will help you identify any indexes with a degree of parallelism higher than 1:

select index_name,degree from dba_indexes
where owner='AEDBADMIN' and degree > 1;

Resolution

If you identify any AEDB indexes with a degree of Parallelism set to a value higher than '1', your DBA will need to change it back to '1' to overcome the performance issues.

You should stop the AutoSys Scheduler and Application Server, set the degree to 1 and start again.

Additional Information

Oracle 19c Parallelism:
https://www.oracle.com/database/technologies/datawarehouse-bigdata/parallel-execution.html