how to get sql execution information

the basic steps to find out sql exectuion information for tuning

1. explain plan
This will give you how sql is executed.

explain plan for <sql statement >
select * from table (dbms_xplan.display);

2. autotrace
Autotrace can be set on to display explain plan and statistics automatically

set autotrace on


3. trace file
Trace file can be generated to provide more execution information. User need alter session privilege.

alter session set sql_trace true

find out trace file name
select value from v$diag_info where name = 'Default Trace File';

user tkprof to generate readable file from trace file
tkprof <trace file> a.txt sys=no sort=prsela exeela fetchela

4. user dbms_profiler to check plsql code



reference:
https://forums.oracle.com/forums/thread.jspa?threadID=501834&start=0&tstart=0
http://www.articles.freemegazone.com/oracle-trace-file.php
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:458240723799