How to Identify SQL_ID for SQL Statements in Oracle Database

n Oracle Database, identifying the SQL_ID of a query is essential for performance tuning, troubleshooting, and execution plan analysis. This guide explains how to retrieve the SQL_ID using V$SQL, AWR, and ASH views.

🔍 Finding SQL_ID Using V$SQL

The V$SQL view contains real-time information about executed SQL statements. If you know a unique keyword or comment inside your SQL, you can locate its SQL_ID easily.

📝 Example Query:


SELECT /* PERFORMANCE CHECK */ emp_name 
FROM employees WHERE department_id = 10;

To find the SQL_ID, execute:


SELECT sql_id, plan_hash_value, substr(sql_text,1,50) sql_text  
FROM v$sql 
WHERE sql_text LIKE 'SELECT /* PERFORMANCE CHECK */%';

🔹 Sample Output:

SQL_ID	PLAN_HASH_VALUE	SQL_TEXT
`5abcn9xyz3`	`123456789`	`SELECT /* PERFORMANCE CHECK */ emp_name ...`

🔹 Plan Hash Value: This helps in identifying the execution plan of the SQL.

🔍 Identifying SQL_ID Using Custom Keyword

If you want to dynamically search for a SQL statement containing a specific keyword:


SELECT sql_id, plan_hash_value, substr(sql_text,1,50) Text
FROM v$sql
WHERE sql_text LIKE '%&Enter_Search_Keyword%';

This allows you to search for statements dynamically at runtime.

🔍 Finding SQL_ID from AWR History

If the SQL statement is no longer in V$SQL, you can find it in AWR history views (DBA_HIST_SQLTEXT & 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 /* PERFORMANCE CHECK */%'
ORDER BY s.sql_id;

🔹 How This Helps:

✔ SQL_ID Retrieval: Finds old queries no longer in memory
✔ Execution Time Tracking: Use snap_id to check when the query ran
✔ Execution Plan Comparison: Identify changes in SQL performance

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How to Identify SQL_ID for SQL Statements in Oracle Database