How to Use the TKPROF command in oracle to view Tracefile

How to Use the TKPROF command in oracle to view Tracefile

Trace file is a subsequent text file of trc, which records various SQL operations and the time consumed, based on the trace file, we can understand which sqls cause system performance bottlenecks and take appropriate optimization methods.

When we operate the oracle database, a session is generated each time. All operations are recorded in the session, and these operations are recorded in the trace file.

 

How to Use the TKPROF command to view Trace file:

 

SQL> alter session set SQL _trace = true;

 

SQL> select count (*) from all_objects;

COUNT (*)
----------
49880

 

SQL> select. spid from v $ process a, v $ session B where. addr = B. paddr and B. audsid = userenv ('sessionid'); ----- query that the current session spid is 13052

SPID
----------
13052

SQL> SELECT VALUE FROM V $ PARAMETER WHERE NAME = 'user _ dump_dest '; -- view the default path of the trace file

VALUE
----------
F: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ ADMIN \ ORCL \ UDUMP

You can see the newly produced file orcl_ora_13052.trc in this path. However, this trace file is not easy to read and can be converted to readable by using the TKPROF command.

 

Run the command in the Command window. Run cmd to open the command window:

C: \> tkprof F: \ oracle \ product \ 10.2.0 \ db_1 \ admin \ orcl \ udump \ orcl_ora_13052.trc output = F: \ orcl_trc_13052.txt;

Open the file and you will see the following information:

 

COUNT: The number of times this statement is run by parse, execute, and fetch.
CPU: the cpu time consumed by this statement for all parse, execute, and fetch, in seconds.
ELAPSED: the total time consumed by this statement in parse, execute, and fetch.
DISK: the number of physical I/O executions, the number of physical reads from the data files on the DISK. In general, what you want to know is the data that is being read from the cache rather than the data that is being read from the disk.
QUERY: the number of logical I/O executions when the block is obtained by consistent search. In consistent read mode, the number of buffers obtained by all parse, execute, and fetch. The buffer in consistency mode is used to provide a consistent read snapshot for a long-running transaction. The cache actually stores the status in the header.
CURRENT: Number of logical I/O times, the number of buffers obtained in current mode. Generally, buffer is obtained when the insert, update, and delete operations are performed in current mode. In current mode, if a new cache is found to be sufficient for the current transaction in the cache area, the buffer will be read into the cache area.
ROWS: number of ROWS processed or affected at this stage, the number of records returned by all SQL statements, but not the number of records returned by subqueries. For select statements, the returned records are in the fetch step. For insert, update, and delete operations, the returned records are in the execute step.