Monday, 3 March 2025

How to Find the SQL ID in Oracle Developer

 

How to Find the SQL ID in Oracle Developer

SQL ID is a unique identifier assigned by Oracle to each SQL statement executed in the database. It is essential for performance tuning, troubleshooting, and analyzing query execution plans. In this blog post, we will explore various methods to find the SQL ID in Oracle Developer and SQL*Plus.

1. Using V$SQL to Find SQL ID

Oracle provides the V$SQL view, which contains details about executed SQL statements, including the SQL ID. The following query retrieves the SQL ID for a specific SQL text:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_query_pattern%';

Replace %your_query_pattern% with a part of your SQL statement to filter the results.

2. Finding SQL ID for a Running Query in V$SESSION

If a query is currently executing, you can find its SQL ID from the V$SESSION view:

SELECT sid, serial#, sql_id, sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE';

This query lists active sessions and their corresponding SQL IDs.

3. Using SQL Developer’s Autotrace or Explain Plan

Steps to Find SQL ID in Oracle SQL Developer:

  1. Open Oracle SQL Developer and execute your query.
  2. Enable Autotrace:
    • Click on Query Builder > Enable Autotrace.
    • Run your SQL statement.
    • The SQL ID will be displayed in the execution plan output.
  3. Alternatively, run: 
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALL'));
    This retrieves the SQL ID of the last executed statement in your session.

4. Using AWR to Retrieve SQL ID for Historical Queries

Oracle's Automatic Workload Repository (AWR) stores SQL execution history, which is useful when identifying SQL IDs for past queries:

SELECT sql_id, sql_text, elapsed_time_total
FROM dba_hist_sqlstat
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

This retrieves the top 10 longest-running SQL queries along with their SQL IDs.

5. Finding SQL ID from an Active Session in ASH

Oracle’s Active Session History (ASH) provides detailed SQL execution information:

SELECT sql_id, session_id, sql_text
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE;

This helps track recent SQL statements executed in the database.

Conclusion

Knowing how to find the SQL ID in Oracle is crucial for diagnosing performance issues and analyzing execution plans. Whether using V$SQL, V$SESSION, ASH, or AWR reports, you can quickly locate the SQL ID and use it for further tuning or investigation.


Posted by at