job stuck in RAC , Process and locks in Oracle RAC

1. Check for blocking sessions in DB.
2. Check for wait events in the system at time of job execution.
3. Execution plan of sql queries.
4. Stats/Fragmentation in the tables.

 select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=75 order by time_waited desc;

 select count(*),event from v$active_session_history where session_id=158 group by event order by count(*) desc;


select PROGRAM,sid,status,last_call_et/(60*60) ,sql_id from gv$session where last_call_et/(60*60) > 3 and status='ACTIVE' AND PROGRAM LIKE '%J00%';

select * from dba_hist_sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$active_session_history where session_id = 72 order by sample_id desc;
select * from dba_tab_statistics  where owner like '<user_name>'AND table_name like '<table_name>';
select * from gv$system_event where wait_class !='Idle' order by time_waited desc ;
select * from gv$session where blocking_session is not null;


select sid, process, event, blocking_session, seq# from gv$session where sid=2194;

 SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
    || '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
    FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
    WHERE V.TYPE = 'JQ'
    AND J.JOB = V.ID2
    AND S.SID = V.SID;


SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
|| '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
WHERE V.TYPE like 'CJ%'
AND J.JOB = V.ID2
AND S.SID = V.SID;

select SCHEMA_USER ||','||job||','||WHAT||',' || INTERVAL ||','||BROKEN||','||LAST_DATE||','||round((sysdate-next_date)*1440)||','||NEXT_DATE  from dba_jobs where job in (310,265,266,267,268,645,281,285,286) order by job;

select inst_id,event,time_waited,average_wait,wait_class from (select inst_id,event,time_waited,average_wait,wait_class, 

row_number() over (partition by inst_id order by time_waited desc, average_wait desc) row_num

from gv$system_event 

where wait_class!='Idle' )

where row_num<=10

order by inst_id,row_num;

select chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "lock",
to_char( bitand(p1, 65535) )    "mode", sid
from gv$session_wait
where lower(event) = 'dfs lock handle';


SELECT b.name||'|'|| b.value_string
FROM
gv$sql_bind_capture b
WHERE   b.sql_id = '4tsv2myfxsr12';

SELECT event AS "Event|Name",
       total_waits "Total|Waits",
       round(time_waited / 100, 0) "Seconds|Waiting",
       total_timeouts "Total|Timeouts",
       average_wait / 100 "Average|Wait|(in secs)"
  FROM sys.v_$system_event e
  ORDER BY time_waited DESC;

select nvl(s.username,s.program) username, s.sid sid, s.serial# serial, s.sql_hash_value sql_hash_value, 

substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event , w.p1 p1, w.p2 p2, w.p3 p3 from v$session s, v$session_wait w

where w.sid=s.sid and s.status='ACTIVE'and s.type='USER';