How to take TKPROF for the sql query in oracle:

 
Open sqlplus session with database, login to db user.

 1. Select distinct sid from v$mystat.

 2. alter session set tracefile_identifier = 'Module_name';

 3. ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

 4. Execute the procedure or sql.

 5.  ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

 6. Disconnect from session and  repeat above steps for other modules/sql's.

 Then to check the TRACE FILES use this query

SQL> select value from v$parameter where name = 'user_dump_dest';

logout sqlplus

in os prompt cd /tracefile destination "output of  "select value from v$parameter where name = 'user_dump_dest'; "


then in trace file check the module_name which was given in 2nd step.

in os prompt
typo

1. tkprof  orcl_ora_10589_BHU.trc translated.txt

 2.cat translated.txt

 or
1.tkprof orcl_ora_27714_SOM.trc orcl_ora_27714_SOM.trf

2 cat orcl_ora_27714_SOM.trf

 This output we can check in terminal itself no need todo scp or bbedit.