How to Determine the SQL_ID for a SQL Statement (Doc ID 1627387.1)

How to identify the SQL_ID of a statement

The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view.

If the SQL can be identified with a particular identifiable string or by some kind of unique comment such as: /* TARGET SQL */ then this will make it easier to locate.

For example:

SELECT /* TARGET SQL */ * FROM dual;
SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'


SQL_ID        SQL_TEXT
------------- ----------------------------------------
0xzhrtn5gkpjs SELECT /* TARGET SQL */ * FROM dual

The hash_value is included here for convenience.

 You can also find the SQL_ID in the V$SQL view using a substitution variable:

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