How to Trace SQL Queries with SQL ID in Oracle

This guide explains how to trace SQL queries using SQL ID in Oracle. Steps include identifying the SQL ID, enabling tracing with specific commands, executing the query, disabling the trace, and locating the trace file in the alert log. The method helps optimize SQL performance analysis effectively.

1. Identify your SQL ID of SQL Query which need to trace.

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM  v$sql
WHERE sql_text like 'SQL QUERY%';

2. Enable the trace of SQL with using SQLID in following statements:

-- Enable the events with replacing SQL_ID
alter system set events 'sql_trace [sql:] wait=true,bind=true';
alter system set events 'trace[sql_optimizer.*] [sql:]';

Example

alter system set events 'sql_trace[sql: 800hwktjz3zuc]'; 

3. Run the SQL Query.

Run SQL Query which need to trace.
Select * from hr.employees;

4. After executing the SQL Query, disable the trace.

-- to disable the events:
alter system set events 'sql_trace off';
alter system set events 'trace[sql_optimizer.*] off'

5. Get the trace file in alert log location.

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Example of Tracing SQL Query:

1. Identify the SQL id for SQL

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM  v$sql
WHERE sql_text like 'select * from scott.emp%'

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- -----------------------
ggqns3c1jz86c 3956160932 select * from scott.emp;

2. Enable the trace for SQL ID : ggqns3c1jz86c

alter system set events 'sql_trace [sql:ggqns3c1jz86c] wait=true,bind=true';

alter system set events 'trace[sql_optimizer.*] [sql:ggqns3c1jz86c]';

3. Run the SQL Query.

select * from scott.emp

4. Disable the trace

alter system set events 'sql_trace off';
alter system set events 'trace[sql_optimizer.*] off';

5. Check the trace location and find the file having _ORA_SPID.trc in it like xe_ora_5864.trc

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
VALUE
----------------------------------------------
D:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace