Shulou(Shulou.com)06/02 Report--

TKPROF (Transient Kernel Profiler): TKPROF is an executable tool for analyzing oracle trace files and producing a clearer and more reasonable output. Or it can be interpreted as Trace Kernel Profile.

Let's look at the example first:

Tsgoooo$tkprof epd3_ora_23315.trc trace.txt explain=sfis1/sfis1 print=50 record=sql.txt

Get help: enter tkprof and enter directly to see the help.

Tsgoooo$tkprof

Usage: tkprof tracefile outputfile [explain=] [table=]

[print=] [insert=] [sys=] [sort=]

Table=schema.tablename Use 'schema.tablename' with' explain=' option.

Explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.

Print=integer List only the first 'integer' SQL statements.

Aggregate=yes | no

Insert=filename List SQL statements and data inside INSERT statements.

Sys=no TKPROF does not list SQL statements run as user SYS.

Record=filename Record non-recursive statements found in the trace 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:

Prscnt number of times parse was called

Prscpu cpu time parsing

Prsela elapsed time parsing

Prsdsk number of disk reads during parse

Prsqry number of buffers for consistent read during parse

Prscu number of buffers for current read during parse

Prsmis number of misses in library cache during parse

Execnt number of execute was called

Execpu cpu time spent executing

Exeela elapsed time executing

Exedsk number of disk reads during execute

Exeqry number of buffers for consistent read during execute

Execu number of buffers for current read during execute

Exerow number of rows processed during execute

Exemis number of library cache misses during execute

Fchcnt number of times fetch was called

Fchcpu cpu time spent fetching

Fchela elapsed time fetching

Fchdsk number of disk reads during fetch

Fchqry number of buffers for consistent read during fetch

Fchcu number of buffers for current read during fetch

Fchrow number of rows fetched

Userid userid of user that parsed the cursor

Explanation of the usage of several important parameters

The sys parameter, if not specified, defaults to yes. This parameter means whether the output file contains sql statements run as the SYS user. This parameter is quite useful. When we execute sql statements, the backend often executes a lot of recursive statements. For example, if you enter SELECT * FROM TEST;, if this statement is hard parsed, it will generate a lot of recursive SQL, recursive query table statistics, column statistics, index statistics, etc., of course, recursive more than that. These recursive sql are run as SYS users, and if you don't want to see these recursive SQL, add this parameter sys=no.record parameter, which specifies a file under a path that is used to generate all the non-recursive SQL found in the trace file. For example, if you execute three statements in SQLPLUS, select * from a select * from b select * from c *, then if you specify this parameter such as: record=c:\ test.log, then after you format the trace file with tkprof, the three SQL will be recorded in this test.log. This feature is useful in some cases, because trace files tend to be large and difficult to find. We can get a general idea of what non-recursive SQL are in the trace file by specifying this parameter. And this feature also helps us to repeat the SQL statement (not allowed to bind variables). Aggregate parameter, which specifies whether tkprof will aggregate the sql of the same text content, for example, if you execute select * from a ten times, if you specify this parameter as no (default), then the resulting output file will have ten execution information of such statements, if you specify yes, then tkprof will summarize the execution information of the ten times. How to specify this parameter depends on your needs, personally, I think it is still a very useful parameter. Sort parameter, which is often used to specify the sort of sql statements in the tkprof output file. By default, it is sorted according to the order of execution. We can specify that it is sorted in other ways, such as disk readings, CPU time, and so on. The most common way to use this parameter is: sort=prsela,exeela,fchela, in fact, the three values add up to the response time, that is, sorted by response time. Don't get me wrong here, tkprof sorts according to the sum of the three values of prsela,exeela,fchela, not one by one like the SQL statement. The print parameter, which is often used with the sort parameter, is used to specify the number of tkprof output sql statements. These two parameters work well together. For example, if you want to know a SQL with the top 10 response times in a trace file, you can use sort=prsela,exeela,fchela print=10 to match it. The explain parameter, which means to provide an execution plan for each SQL. The method used is explain= user name / password, in fact, the principle is very simple, that is, log in to the database through your specified user name and password, and then execute the following explain plan for sql for each sql, output to plan_table, and finally add to the output file. Note that since the explain plan for command requires the user performing the operation to have execution permission on all the objects contained in the sql statement, if the view is included, it must also have execution permission on the underlying table on which the view is based, otherwise the execution plan will not be generated. Note that when this parameter is added, the execution of tkprof will be slow. The wait parameter, which specifies whether wait events are included in the output file, which is included by default. The default value is usually taken.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.