search cancel

AutoSys 12.0 performance with Oracle 19c using Parallel Queries

book

Article ID: 200008

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - System Agent (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) Workload Automation Agent CA Workload Automation AE

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.

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;

Environment

Release : 12.0

Component : CA Workload Automation AE (AutoSys)

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