How to tune/trace sql statements ?

Trace Commands

alter system set timed_statistics=true; -- Enable trace
alter session set sql_trace=true;
alter session set tracefile_identifier = testtrace;

run sql query
eg:select * from emp;--statements

alter session set sql_trace=false; -- Disable trace

find trace file name from current session :

select
u_dump.value || '/' ||
db_name.value || '_ora_' ||
v$process.spid ||
nvl2(v$process.traceid, '_' || v$process.traceid, null )
|| '.trc' "Trace File"
from
v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
u_dump.name = 'user_dump_dest' and
db_name.name = 'db_name' and
v$session.audsid=sys_context('userenv','sessionid');

tkprof commands :

tkprof testtrace.trc testtrace.trcprf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n sort=execpu

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

After getting .trc file we change into readable format by using following command :

tkprof file_name_time_stamp.trc file_name_time_stamp.prf sort=(PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)

Note : We can read by many options , above is one option.