SQL Trace / TKPROF

To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;

ALTER SESSION SET sql_trace = true;
or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);


ALTER SESSION SET tracefile_identifier = mysqltrace;

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:
select sid, serial# from sys.v_$session where .....


SID SERIAL#
---------- ----------
8 13607

--------------------

Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:

SQL> ALTER SYSTEM SET timed_statistics = true;

--------------------

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);


Ask user to run just the necessary to demonstrate his problem.
Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(8,13607, false);


Look for trace file in USER_DUMP_DEST


ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;


Identifying trace files

Trace output is written to the database's UDUMP directory.

The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:

* INSTANCE is the name of the Oracle instance,
* PID is the operating system process ID (V$PROCESS.OSPID); and
* TRACEID is a character string of your choosing.


---http://www.ordba.net/Tutorials/OracleUtilities~TKPROF.htm---

Trace output is quite unreadable. However, Oracle provides a utility, called TKProf, that can be used to format trace output.


SET ORACLE_SID=SNS6


tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

C:\> tkprof C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.trc C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.prf explain = ldbo/ldbo sys=no sort = (PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)




Some of the things to look for in the TKPROF output are listed in this table:

Problems	Solutions
High numbers for the parsing	The SHARED_POOL_SIZE may need to be increased.
The disk reads are very high	Indexes are not used or may not exist.
The "query" and/or "current" (memory reads) are very high	Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance.
The parse elapse time is high	There may be a problem with the number of open cursors.
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows	This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement.
If the number of misses in the library cache during parse is greater than 1	This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora.

----------------