How to identify the SQL_ID of a statement

 SELECT

    sql_id

  , hash_value

  , plan_hash_value

  , sql_text

FROM

  gv$sql

WHERE

  sql_text LIKE '%all_rows parallel%';

 

select inst_id, sql_id, plan_hash_value, substr(sql_text, 1 ,40) from gv$sql where sql_id = '1h6twft9b97sv';

 

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

FROM gv$sql

WHERE sql_text LIKE '%INSERT INTO apps.hhs_cta_ufms_tbl%' and module like '%e:FV:cp:hhs/HHSCARSEXT%';

 

If SQL is no longer available in v$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 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'8p9mw649qbzs1',format=>'ADVANCED'));