Tkprof: analyzes ORACLE tracking files

Tkprof: an executable tool that analyzes the ORACLE tracking file and generates a more user-friendly and clear output result. Step 1. set the parameter file to set the three parameters timed_staticsticesuser_dump_destmax_dump_file_sizetimed_staticstices to start or disable timed statistics (such as the CUP time and time used)

Tkprof: an executable tool that analyzes the ORACLE tracking file and generates a more user-friendly and clear output result. Step 1. set the parameter file to set the three parameters timed_staticstices user_dump_dest max_dump_file_size timed_staticstices for starting or disabling timed statistics (such as the CUP time and time used)

Tkprof: an executable tool for analyzing ORACLE tracking files and generating more user-friendly and clear output results

Steps for using TKPROF

1. Set the parameter file

Set the three parameters timed_staticstices user_dump_dest max_dump_file_size.

Timed_staticstices is used to start or disable the collection of timed statistics (such as CUP time and time used) and multiple statistical information in the dynamic performance table.

Alter session set timed_statistics true;
Alter system set timed_statistics false;

MAX_DUP_FILE_SIZE when SQL TRACE is enabled at the instance layer, a text line is generated in the TRACE file each time the server is requested. The maximum size of these files is limited by the setting of initialization parameters. The default value is 500 (blocks ). If the data is truncated, the SIZE is increased. If it is UNLIMITED, There is no upper limit.
USER_DUMP_DEST sets the storage location of the trace file. The default value is admin/user/udump;

Alter system set user_dump_dest = newdir
2. Start the SQL TRACE Utility
Start SQL TRACE for sessions
Alter session set SQL _trace = true;
Alter session set SQL _trace = false;
SYS. DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION = (SID, SERIAL #, TRUE );

SID and SERIAL # can be obtained from the V $ SESSION view.
ALTER SESSION SET EVENTS
Alter session set events '10046 trace name context forever, level ';
Alter session set events '10046 trace name context off ';
Alter system set events '10046 trace name context forever, level 1'
Alter system set events '10046 trace name context off'

N = 1 --------- activate the standard SQL _TRACE tool, which is no different from setting SQL _TRACE = TRUE;
N = 4 --------- activate the standard SQL _TRACE and obtain the Bind Variable in the trace file.
N = 8 ----------- activate the standard SQL _TRACE and obtain the wait events in the trace file at the query level.
N = 12 -------- activate the standard SQL _TRACE and include flat the bound variables and wait events.
Start SQL TRACE for the user instance
Alter system set SQL _trace = true;
Alter system set SQL _trace = false;

3. Use tkprof to format the trace file
Usage: tkprof tracefile outputfile [explain =] [table =] [print =] [insert =] [sys =] [sort =]
Formatted output file
Table = schema. tablename Use 'schema. tablename' with 'explain = 'option.
Used to specify the schema and name of the table used by TKPROF to temporarily store the execution plan before it is written into the output file.
Explain = user/password Connect to ORACLE and issue explain plan.
Print = integer List only the first 'integer' SQL statements. only the SQL statements of the first integer in the output file are listed. If this parameter is ignored, TKPROF lists all the SQL statements of the trail.
Aggregate = yes | no if you specify AGGREGATE = NO, TKPROF will not summarize multiple users with the same SQL text
Insert = filename List SQL statements and data inside INSERT statements. This is a type of SQL script used to store the motivation information of the tracking file to the database.
Sys = no TKPROF does not list SQL statements run as user SYS. to start or disable the SQL statement list published by the user SYS to the output file, including recursive SQL statements (to execute the user's SQL statement, ORACLE must also execute some additional statements) statement. YES by default
Record = filename Record non-recursive statements found in the trace file. For non-recursive SQL statements used in the trace file, TKPROF creates an SQL script with the specified name. Used to replay the user time in the tracking File
Waits = yes | no Record summary for any wait events found in the trace file.
Sort = option Set of zero or more of the following sort options: Before outputting the list of SQL statements to the trail file, first, sort the sorting by the descending relationship of the specified sorting option. If multiple sorting options are specified, sort them based on the descending relationship of the values specified by the sorting option; if this parameter is ignored, TKPROF lists the statements to the output file in the order of use.
Prscnt number of times parse was called number of statements parsed
Cpu time consumed by prscpu CPU time parsing statement parsing
The time occupied by parsing the prsela elapsed time parsing Statement (always greater than or equal to the CPU time );
Prsdsk number of disk reads during parse number of physical reads from the disk during parsing
Number of consistent mode block reads during parsing of the prsqry number of buffers for CONSISTENT read during parse statement
Prscu number of buffers for current read during the number of READ (current mode block read) in the current MODE during parse statement Parsing
Prsmis number of misses in library cache during number of database cache failures during parse statement Parsing

Execnt number of execute was called statement execution count
Execpu cpu time the CPU time occupied by executing the spent executing statement
Exeela elapsed time executing statement execution time (always greater than or equal to the CPU time)
Exedsk number of The number of physical reads from the disk during execution of the disk reads during execute statement
Exeqry number of buffers for consistent read during the number of consistent mode block read during execution of the execute statement
Execu number of buffers for current read during the number of READ (current mode block read) in the current MODE during execution of the execute statement
Exerow number of rows processed during execution of the rows processed during execute statement
Exemis number of library cache misses during execution of the during execute statement

Fchcnt number of times fetch was called
Cpu usage of fchcpu CPU time spent fetching data
The time used by fchela elapsed time fetching to fetch data (always greater than or equal to the CPU time)
Fchdsk number of disk reads during fetch the number of physical reads from the disk
Fchqry number of buffers for consistent read during fetch the number of consistent mode block reads
Fchcu number of buffers for current read during fetch the number of reads in the CURRENT MODE
Fchrow number of rows obtained by rows fetched
Userid of user that parsed the cursor

4. Reading the tkprof File

============================

Steps for using ORACLE TKPROF

1. Tkprof is an executable tool for analyzing ORACLE tracking files and generating more user-friendly and clear output results. C: \ oracle \ ora92 \ bin \ tkprof.exe

2. Full name of tkprof

TKPROF stands for transient kernel profiler.

3. Basic Steps

1) SQL> alter system set timed_statistics = true;

2) user-level self-tracking:

SQL> alter session set SQL _TRACE = TRUE;

SQL> alter session set SQL _TRACE = FALSE;

User-level DBA tracking: (for example, sys tracking test, You need to log on with sysdba)

. A). SQL> select s. USERNAME, s. SID, s. SERIAL #, s. COMMAND from v $ session s

Where s. USERNAME = 'colm ';

B). SQL> exec sys. dbms_system.set_ SQL _trace_in_session (9, 7, true );

C). SQL> exec sys. dbms_system.set_ SQL _trace_in_session (9, 7, false );

Ps: 9 is SID, 7 is SERIAL #

3) C:> cd C: \ oracle \ admin \ COLM \ udump

4) C: \ oracle \ admin \ COLM \ udump> tkprof colm_ora_2056.trc trace.txt print = 100 record1_ SQL .txt sys = no

5) trace.txt File

Example:

**************************************** ***************************************

SELECT *

FROM

Col_case

Call count cpu elapsed disk query current rows

-----------------------------------------------------------------------

Parse 1 0.00 0.00 0 0 0 0

Execute 0 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

-----------------------------------------------------------------------

Total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 62

The statements to be adjusted meet the following requirements:

(1). Excessive CPU usage

(2). Parse, Execute, Fetch takes too much time

(3) Too many DISK reads, and too few data blocks are read in query/current (SGA ).

(4). Access Multiple blocks and return only 2 rows

======================================
Use TKPROF to convert the trace file to readable format

Oracle can use alter session to set an underlying trace. If this trace is enabled, Oracle uses the top-level
PL/SQL calls are recorded in the trace file (udump/*. trc) on the server ). This trail file includes not only SQL and PL/SQL calls, but also timed information,
Wait for the event information, the number of logical I/O and physical I/O executed, CPU and wall clock time, number of lines processed, query plan with a line count, etc., but. trc
The file is hard to read. We can use TKPROF to convert it to a readable format.
First, we need to enable SQL _TRACE at the session level to generate an effective. trc file for the purpose of tracing and enabling Oracle:
SQL code
Alter session set timed_statistics = true;
Alter session set events '2017 trace name context forever, level 12 ';
In the preceding SQL statement 1, the timing system is set, and in the second sentence, the tracing is Enabled: The level The description is as follows:
1. Enable the standard SQL _TRACE tool, which is equivalent to SQL _TRACE = true.
4. Enable SQL _TRACE and capture bind variables in the trace file
8. Enable SQL _TRACE and capture the wait events of the trace file
12. Enable standard SQL _TRACE and capture bind variables and wait events

If you need to confirm the number of. trc files in a database, here is a query that can help you (Oracle9i) debug through:

SQL code
Select rtrim (c. value, '/') | '/' | d. instance_name | '_ ora _' | ltrim (to_char (. spid) | '. trc 'from v $ process a, v $ session B, v $ parameter c, v $ instance d
Where a. addr = B. paddr and B. audsid = sys_context ('userenv', 'sessionid ')
And c. name = 'user _ dump_dest ';
The test results are roughly as follows:
Absolute_path \ dbname \ udump/dbname_ora_384.trc
Absolute_path \ dbname \ udump/dbname_ora_2000.trc
Next we will use TKPROF to convert dbname_ora_2000.trc to a readable format (because I am doing business on the win system, so convert it
. Txt format ):
> Tkprof dbname_ora_2000.trc traceview.txt
After the conversion is completed, a traceview.txt file is generated in the current directory (the content is described in the specific tracking statement ):
Execute a query statement as needed:

SQL code
Select count (*) from test_trace_an;
Exit; -- exit SQL plus

Assume that the preceding dbname_ora_2000.trc is the current trace result: In the result file converted using TKPROF, we can find that:

Select count (*) from test_trace_an

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
-----------------------------------------------------------------------
Total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
----------------------------------------------------------
1 SORT AGGREGATE
1 table access full obj # (0, 30327)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ----------------------
SQL * Net message to client 2 0.00 0.00
SQL * Net message from client 2 0.00 0.00
How to read and view the TXT file, and how to process the data it provides.