How To Identify Sessions Which Cause PL/SQL LOCK TIMER Wait Event:

SELECT vs.sid,vs.sql_id, vs.inst_id,vs.osuser,vw.event,vw.p1,vw.p2,vw.p3 ,vt.sql_text , vs.program FROM gv$session_wait vw, gv$sqltext vt , gv$session vs

WHERE vw.event = 'PL/SQL lock timer'

AND vt.address=vs.sql_address

AND vs.inst_id = vw.inst_id

AND vs.sid = vw.sid;

 How to Determine the SQL_ID for a SQL Statement:

a. How to identify the SQL_ID of a statement:

SELECT /* TARGET SQL - Ram */ * FROM dual;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  

FROM  gv$sql 

WHERE sql_text like 'SELECT /* TARGET SQL - Ram */%'

 

b. Find the SQL_ID in the V$SQL view using a substitution variable:

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text

FROM gv$sql

WHERE sql_text LIKE '%TARGET SQL - Ram%';  -- An_Identifiable_String

 

c. If SQL is no longer available in gv$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :

SELECT

    s.sql_id,

    s.plan_hash_value,

    t.sql_text,

    s.snap_id

FROM

    dba_hist_sqlstat   s,

    dba_hist_sqltext   t

WHERE s.dbid = t.dbid

AND   s.sql_id = t.sql_id

AND   sql_text LIKE '%Ram%'

ORDER BY

    s.sql_id;

d. How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block:

select sql_id, sql_text from gv$sql where sql_text like '%declare%fnd_concurrent.wait_for_request%';