How to Get the sql_id of a Query in Oracle

You might need to get the sql_id of a Query in Oracle to troubleshoot some performance problem or just to check some stats.

This is the simplest way to do it.

You query v$sql with some text that you know your query contains.

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

If you don’t find it in the query above, you can use this other query that goes even more in the past.

It is a query that joins dba_hist_sqlstat and dba_hist_sqltext.

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;