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%';