TKPROF report

TKPROF report

1.      Transient kernel profiler is a report which show details like time taken, cpu utilization in every phase (parse, execution and fetch) of sql execution

Steps to take TKPROF report

SQL> grant alter session to scott;

SQL> alter session set sql_trace=TRUE;

SQL> select * from emp;

SQL> alter session set sql_trace=FALSE;

The above steps will create a trace file in udump location

[oracle@dbaserver udump]$ tkprof prod_ora_7824.trc tkprof_report.lst

2.      From TKPROF report if we observe that statement is getting parsed everytime and if it is frequently executed query, reason could be statement flushing out from shared pool because of less size. So increasing shared pool size is the solution

3.      If we observe fetching is happening everytime, it could be because of data flushing from buffer cache for which increasing the size is the solution

4.      If the size of database buffer cache is enough to hold the data bit still data is flushing out, in such cases we can use keep & recycle caches