TKPROF And Oracle Trace Analysis

What is the TKPROF ??!!
The TKPROF program :
converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. 
But first you need to enable this tools since its unactivated in oracle  to start using it follow the below steps :
1-ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
2-we need table called PLAN_TABLE if its dosen’t found create it by following steps :
@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;
3-after doing the Previous steps , now you can use the amazing tools for example :
ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM dual;

ALTER SESSION SET SQL_TRACE = FALSE;
 
OR Another Way to trace file :
 
TKPROF   
explain=user/password@service table=sys.plan_table
 
Output will be like :
 
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT COUNT(*)
FROM dual

call count cpu elapsed disk query current rows
------- ----- ----- ------- ------- ------- ------- -------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 4 1
------- ----- ----- ------- ------- ------- ------- -------
total 4 0.02 0.02 0 1 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL DUAL
 
Thank you 
Osama mustafa  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.