Wednesday, July 6, 2011

Trace Files and TKPROF in Oracle Apps

How to enable Trace:

  • Enable the SQL Trace facility for the session by using one of the following DBMS_SESSION.SET_SQL_TRACE procedure
ALTER SESSION SET SQL_TRACE = TRUE|FALSE;
  • Diagnostics Link > "Set Trace Level" > Go button > Choose the Trace Level.
  • In situations where you cannot invoke an ALTER SESSION command from the session you wish to trace—as with prepackaged applications, for example—you can connect to the database as a DBA user and invoke the dbms_system built-in package in order to turn on or off SQL trace in another session. You do this by querying v$session to find the SID and serial number of the session you wish to trace and then invoking the dbms_system package with a command of the form:EXECUTE SYS.dbms_system.set_sql_trace_in_session (, , TRUE|FALSE);
Where to get Trace File
When you enable SQL trace in a session for the first time, the Oracle server process handling that session will create a trace file in the directory on the database server designated by the user_dump_dest initialization parameter. As the server is called by the application to perform database operations, the server process will append to the trace file.

select name,value from V$PARAMETER where name = 'user_dump_dest';


Using TKPROF

TKPROF Example#1
----------------------
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

TKPROF Example#2
----------------------
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:

TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)


For more details, users should visit following link:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/sqltrace.htm

No comments:

Post a Comment