Error - ORA-01555 caused by SQL statement

Products

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

Issue/Introduction

The below query has caused the alerts to trigger on the database side:

ORA-01555 caused by SQL statement below (SQL ID: bkv1w4yn95dfz, Query Duration=2422036 sec, SCN: 0x0000.73ecbfd0):
Mon Jul 13 17:54:31 2020
SELECT J.JOID,J.JOB_VER,J.OVER_NUM, I.ALARM_IF_FAIL,S.APPL_NTRY,J.AS_APPLIC,J.AS_GROUP,I.AUTO_DELETE, I.AUTO_HOLD,J.BOX_JOID,J2.JOB_NAME,J.BOX_TERMINATOR,I.DATE_CONDITIONS, I.DAYS_OF_WEEK,J.DESCRIPTION,S.EVT_NUM,I.EXCLUDE_CALENDAR,S.EXIT_CODE, J.EXTERNAL_APP,J.HAS_BLOB,J.HAS_BOX_FAILURE,J.HAS_BOX_SUCCESS,J.HAS_CONDITION,J.HAS_NOTIFICATION,J.HAS_OVERRIDE, J.HAS_RESOURCE, J.HAS_SERVICE_DESK,S.JC_PID,I.JOB_LOAD,J.JOB_NAME, J.JOB_TERMINATOR,J.JOB_TYPE,S.LAST_END,S.LAST_HEARTBEAT,S.LAST_START, J.MACH_NAME,J.MAX_EXIT_SUCCESS,I.MAX_RUN_ALARM,I.MIN_RUN_ALARM, J.N_RETRYS,S.NEXT_PRIORITY,S.NEXT_START,S.NTRY,J.NUMERO,J.OWNER, J.PERMISSION,S.PID,I.PRIORITY,J.PROFILE,S.QUE_NAME,I.RUN_CALENDAR, S.RUN_MACHINE,S.RUN_NUM,S.RUN_PRIORITY,I.RUN_WINDOW,S.RUN_WINDOW_END,J.SEND_NOTIFICATION,J.SERVICE_DESK, I.START_MINS,I.START_TIMES,I.MUST_START,I.MUST_COMPLETE,S.STATUS,S.STATUS_TIME, I.TERM_RUN_TIME,I.TIMEZONE,J.WF_JOID,W.WF_NAME FROM UJO_JOB_STATUS S JOIN UJO_JOB J ON S.JOID = J.JOID JOIN UJO_SCHED_INFO I ON I.JOID = J.JOID AND I.JOB_VER = J.JOB_VER AND I.OVER_NUM = J.OVER_NUM JOIN UJO_WORKFLOW W ON J.WF_JOID = W.JOID JOIN UJO_JOB J2 ON J.BOX_JOID = J2.JOID AND J2.IS_CURRVER=1 WHERE J.JOID = :B2 AND J.JOB_VER = :B1 AND J.OVER_NUM = -1

Increasing UNDO_RETENTION does not seem to eliminate these errors from occurring periodically.

Nothing is logged in the AutoSys logs.

Environment

Release: All Supported Versions
Component: Workload Automation AE (AutoSys Edition):

Cause

The use of a Global Cursor in the AutoSys DB stored procedures.

Resolution

A few of our AEDB  stored procedures use a global cursor for AppServers/Schedulers sessions. This is a performance consideration and saves us from constantly having to allocate/deallocate a cursor for these particular stored procedures. If the global cursor has not been used in a while (i.e. we haven’t made a call to that particular stored procedure using that connection) the database server can consider the rows/data it pointed to as stale. if our application tries to use the cursor Oracle returns the ORA-01555 to our stored procedure, at which point we just close the old one and reallocate at that moment (within the stored procedure itself) and continue the operation requested by the AppServer/Scheduler.

This ORA code is not returned to either Autosys component and so that was why it wasn’t shown in the product logs.
We consider ORA-01555 errors harmless and are a routine and expected cost of our global cursor usage.