Trace the SQL Query with SQL ID in Oracle

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:]';

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:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.