Tkprof and Explain plan and traceanalyzer

Tkprof and Explain plan

SHHO PARAMETER user_dump_dest(copy and save this parameter)


ALTER SYSTEM SET user_dump_dest='/ngs/app/rsrt/personal/';


ALTER SESSION SET timed_statistics=TRUE;


ALTER SESSION SET sql_trace=TRUE;


EXEC


RUN SQL Statements


ALTER SYSTEM SET user_dump_dest=;


ALTER SYSTEM SET timed_statistics=FALSE;


ALTER SESSION SET sql_trace =FALSE;


N.B : ORADBA ACCESS/ALTER SYSTEM Privelege Required for ALTER SYSTEM

trace will have spid in file name, from sid of session can get spid from v$process

tkprof filename.trc filename.txt sys=no explain=username/password

or

tkprof filename.trc output_filename.out sys=no explain=apps/password
sort='(prsela,exeela,fchela)'

The above mentioned command sorts the SQL by placing the worst performing
SQL statement at the top of the output file


USING AUTOTRACE
SET AUTOT ON -- Runs the SQL and gives the execution plan and statistics

SET AUTOT TRACE -- Runs the SQL but does not show the result and gives the execution plan and statistics

SET AUTOT TRACE EXP -- Only gives the execution plan without running the Query

SET AUTOT OFF -- Switching off Autotrace facility


To get trace file from a concurrent request id :
select oracle_process_id from fnd_concurrent_requests where request_id=38156196;

ORACLE_PROCESS_ID
------------------------------


trace file name will have oracle process id in its name


Create a TraceAnalyzer File
A trace file is a raw set of data produced by the Oracle Database.
TraceAnalyzer reformats the raw data so that it is easier to review. It is
a more powerful tool than TKPROF. It translates things like bind
variables and make them easy to find.
a. Download TraceAnalyzer from Note.224270.1
b. Install trace analyzer in SQL: @TRCACREA.sql;
c. Retrieve the trace file.
d. In SQL*Plus, issue a command like the following to create a TraceAnalyzer
version of the trace file: @TRCANLZR.sql UDUMP vis015_ora_22854.trc