Error - ORA-01555 caused by SQL statement

book

Article ID: 197229

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) CA Workload Automation Agent CA Workload Automation AE

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.

Cause

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

Environment

Release : 11.3

Component : CA Workload Automation AE (AutoSys)

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.