ORMB Batch Scheduling Job Hangs At F1-FLUSH with “PL/SQL Lock Timer” Wait Event

Overnight ORMB Batch Scheduling Job has been running for more than twenty hours, user wants to know why ?

1) Check which job at what step is still running and hanging.

SQL> select OWNER,JOB_NAME,JOB_SUBNAME,SESSION_ID,ELAPSED_TIME,CPU_USED 
     from dba_scheduler_running_jobs;

OWNER   JOB_NAME   JOB_SUBNAME  SESSION_ID ELAPSED_TIME  CPU_USED
------- ---------- ------------ ---------- ----------- ---------------
SCHADM  DAILY_JOBS F1_FLUSH_JOB 1006       +000 21:47:00.08 +00000:00:22.15

2) Check session status.

SQL> select USERNAME,STATUS,EVENT,sql_id,LAST_CALL_ET 
      from gv$session where sid=1006;

USERNAME  STATUS EVENT               SQL_ID           LAST_CALL_ET
--------- ------ ------------------  ---------------- -----------
SCHADM    ACTIVE PL/SQL lock timer   fnfa105xssacx    78949

3) Check which sql is the running with ‘PL/SQL lock timer’.

SQL> set long 20000
SQL> select SQL_FULLTEXT from v$sql where sql_id='fnfa105xssacx';

SQL_FULLTEXT
---------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
 ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window
_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(
14) := :chainid; credential_owner varchar2(30) := :credown; credential_name
varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destinat
ion_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_i
d number := :log_id; BEGIN DECLARE
 BATCH_CODE CHAR(8);
 USER_ID CHAR(8);
 THREAD_COUNT NUMBER;
 BUSINESS_DATE DATE;
 MAX_TIMEOUT_MINS NUMBER;
 RERUN_NUMBER NUMBER;
 THREAD_POOL VARCHAR2(254);
 MAIL_ID VARCHAR2(254);
 MIN_RUN_STATUS CHAR(2);
 RAISE_ERROR BOOLEAN;
 THREAD_NOTIFICATIONS BOOLEAN;
 SOFT_PARM_MAP CISADM.OUAF_BATCH.PARM_MAP_T;
 SOFT_PARM_LIST CISADM.OUAF_BATCH.PARM_LIST_T;
BEGIN
 BATCH_CODE := 'F1-FLUSH';
 USER_ID := 'JAMES';
 --THREAD_COUNT := 0;
 --BUSINESS_DATE := to_date('2016-01-31','yyyy-MM-dd');
 MAX_TIMEOUT_MINS := 0;
 RERUN_NUMBER := 0;
 THREAD_POOL := 'ORMB_BATCH';
 --MIN_RUN_STATUS := NULL;
 --RAISE_ERROR := NULL;
 -- THREAD_NOTIFICATIONS := true;
 --SOFT_PARM_MAP := NULL;
 --SOFT_PARM_LIST := CISADM.OUAF_BATCH.PARM_LIST_T('DIST-THD-POOL','');
 OUAF_BATCH.SUBMIT_JOB(
 BATCH_CODE => BATCH_CODE,
 USER_ID => USER_ID,
 THREAD_COUNT => THREAD_COUNT,
 BUSINESS_DATE => BUSINESS_DATE,
 MAX_TIMEOUT_MINS => MAX_TIMEOUT_MINS,
 RERUN_NUMBER => RERUN_NUMBER,
 THREAD_POOL => THREAD_POOL,
 MIN_RUN_STATUS => MIN_RUN_STATUS,
 RAISE_ERROR => RAISE_ERROR,
 THREAD_NOTIFICATIONS => THREAD_NOTIFICATIONS,
 SOFT_PARM_MAP => SOFT_PARM_MAP,
 SOFT_PARM_LIST => SOFT_PARM_LIST
 );
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

4) ‘PL/SQL Lock Timer’ event ( Doc ID 1476088.1 )

"PL/SQL Lock Timer"

This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure. This event is most likely to come from user written stored procedures. The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.
While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event.

This event is called through the DBMSLOCK.SLEEP procedure or 
USERLOCK.SLEEP procedure. This event is most likely to come from 
user written stored procedures.

5) Sent the details to vendor why F1-Flush has “PL/SQL lock timer’  wait event forever.