AWA: SQL Query running with a very high CPU cost

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

In reviewing performance of the system, a DBA may note queries with a larger than expected CPU cost. For example:

select *
from (
select AH.*, coalesce(EH.EH_Status, AH.AH_Status) as EH_Status
from AH left join EH on EH.EH_AH_Idnr = AH.AH_Idnr where AH_Client = :A0001
and AH_OH_Idnr = :A0002
order by AH_TimeStamp1 desc) where rownum < :A0003

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Cause

Improper indexing on the related tables.

Resolution

Please make sure the following is occuring (this may require a DBA):

1.) The execution plan shows that it is utilizing indexes and not a full table scan.
2.) The indexes are created as per our schema 
https://docs.automic.com/documentation/webhelp/english/AA/12.3/AE/12.3/DB%20Schema/db/_structure/HTML/index.htm
3.) There are no self-generated indexes or indexes created without the correct structure.