How to use the Tkprof command to view trace file in Oracle

The trace file is a text file followed by TRC, which records the various SQL operations and the time spent, and, depending on the trace file, we can understand which SQL is causing the system's performance bottlenecks and then take the appropriate approach to tuning.

When we operate the Oracle database, a session is generated each time, and all actions are recorded in the session, which 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 a.spid from v$process a,v$session b where a.addr=b.paddr and b.audsid=userenv (' SessionID '); -----Query Current session SPID number is 13052

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

Sql>select VALUE from v$parameter WHERE NAME = ' user_dump_dest ';--View trace file default path

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

The newly produced file orcl_ora_13052.trc can be seen under this path, but this trace file is not easy to read and is converted to an easy-to-read file via the tkprof command

To execute in the command window, CMD opens 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 to see the following information:

Count: The number of times this statement was parse, execute, Fetch.
CPU: The time, in seconds, of the CPU consumed by this statement for all parse, execute, and fetch.
ELAPSED: This statement is all consumed in the total time of parse, execute, Fetch.
Disk: The number of physical I/O times that are physically read from the data file on disk. In general, you want to know what data is being read from the cache instead of the data that is being read from the disk.
QUERY: The number of logical I/O times to be performed when a block is taken into a consistent retrieval mode, and the amount of buffer obtained by all parse, execute, and fetch in consistent read. The buffer of the consistency mode is used to provide a consistent read snapshot of a long-running transaction, and the cache actually stores the state in the head.
Current: The number of logical I/O times that the buffer is obtained in current mode. In general, the INSERT, update, and delete operations in current mode will get buffer. In current mode, if a new cache is found in the cache area that is sufficient to present the transaction, these buffers will be read into the buffer.
Rows: This stage, the number of rows that are processed or affected, all SQL statements return records, but do not include the number of records returned in the subquery. For the SELECT statement, the return record is the fetch step, and for the INSERT, UPDATE, delete operation, the return record is the Execute step.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

How to use the Tkprof command to view trace file in Oracle