Generate SQL tkprof

Very often we need to generate tkprof output of sql/plsql to troubleshoot performance issue so I though of putting all steps together.

• Login as SQLPLUS.

SQL>connect apps/appsndev6 Connected.

• Set SQL Trace ON

SQL> ALTER SESSION SET SQL_TRACE = TRUE; SQL> alter session set events '10046 trace name context forever, level 8';

• Get SPID number to identify trace file generate at UDUMP location.

SQL> SELECT spid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE audsid=userenv('SESSIONID') ); SPID ------------ 18979

• Run SQL to generate trace

SQL> begin BIS_MV_REFRESH.refresh_wrapper ('FII_GL_BASE_MAP_MV', 'f', 0); end;

• Go to RDBMS udump location and list *SPID* return from previous query.

nysesapd:/local/oracle/admin/ndev6/udump $ls -l *18979* -rw-rw-r-- 1 oracle dba 238565 Jul 18 14:56 ndev6_ora_18979.trc

• Generate tkprof output as given below.

tkprof ndev6_ora_18979.trc ndev6_ora_18979.tkp sort=exeela,prsela,fchela mailto:explain=apps/appsndev6@ndev6