Home » Trace the SQL Query with SQL ID in Oracle

Trace the SQL Query with SQL ID in Oracle

by tuanlp

 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

You may also like