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.
SQL Query which need to trace.
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:ORACLEXEAPPORACLEdiagrdbmsxexetrace
