Trace the SQL Query with SQL ID in Oracle

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