Differences

This shows you the differences between two versions of the page.

--- dba:oracle:howtos:howto_trace_sql [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1
+++ dba:oracle:howtos:howto_trace_sql [2023/01/31 08:26] (current) – ↷ Page moved from dba:oracle:howto_trace_sql to dba:oracle:howtos:howto_trace_sql dodger
@@ Line 1: / Line 1: @@
+====== [HOWTO] Trace any sql ======
+====== Original documentation ======
+[[https://smarttechways.com/2018/12/10/trace-the-sql-query-with-sql-id-in-oracle/|Trace the SQL Query with SQL ID in Oracle]]
+====== Instructions ======
+Identify your SQL ID of SQL Query which need to trace.
+<code sql>
+SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
+FROM v$sql
+WHERE sql_text like 'SQL QUERY%';
+</code>
+Enable the trace of SQL with using ''<SQLID>'' in following statements:
+<code sql>
+alter system set events 'sql_trace [sql:<SQLID>] wait=true,bind=true';
+alter system set events 'trace[sql_optimizer.*] [sql:<SQLID>]';
+</code>
+\\
+Disable tracing:
+<code sql>
+alter system set events 'sql_trace off';
+alter system set events 'trace[sql_optimizer.*] off';
+</code>