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.
Make DBA Life Easy 