Search 800 + Posts

Apr 7, 2014

Oracle Performance : Sql Trace and how to enable Sql Trace

Sql Trace is facility/utility  when enabled, it capture performance information for executed SQL
statements and write out to a trace file until the SQL trace facility is disabled.

Command to Enable Sql Trace
dbms_session.set_sql_trace,
dbms_system.set_sql_trace_in_session ( usually used to enable trace in a specific session)

Example - 
Begin
dbms_system.set_sql_trace_in_session(SID,Serial #, SQL_TRACE)
End;

SQL_TRACE is Boolean parameter and has value TRUE/FALSE , and value for Parameter #1 and #2 can be retrieve by executing below query
SELECT  s.sid,
s.serial#
FROM           v$session s
WHERE          s.osuser = '&ENTER_OS_USER_NAME'
AND            s.username = '&ENTER_ORACLE_DB_USER_NAME'

we can enable the trace for current session by executing below command

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
Or
SQL> EXECUTE dbms_session.set_sql_trace(TRUE);

SQL> Execute SQL statements
SQL> EXECUTE dbms_session.set_sql_trace(FALSE);
Sql Trace always generate raw trace file and it is not very user friendly especially if you are first timers , we can use TKPROF utility to formats a raw SQL Trace file into a user-friendly file. TKPROF reads the Trace file and creates a file that has the following section:

  • Header
  • Body
  • Summary.


No comments:

Post a Comment