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

Today, the editor will bring you an article about Oracle Tkprof. The editor thinks it's pretty good, so I'll share it for you as a reference. Let's follow the editor and have a look.

Tkprof is an executable tool for analyzing Oracle trace files and producing a clearer and more reasonable output. If the execution efficiency of a system is relatively low, a better way is to find out the problematic SQL statements by tracking the user's session and using the Tkprof tool to format the output using the sorting function.

one。 TKPROF command syntax:

TKPROF filename1, filename2 [SORT = [opion] [, option]]

[PRINT = integer]

[AGGREGATE = [YES | NO]]

[INSERT = filename3]

[SYS = [YES | NO]]

[[TABLE = schema.table] | [EXPLAIN = user/password]]

[RECORD = filename]

Related instructions:

The input file specified by filename1, which can be linked by multiple files.

Filename2 formats the output file.

SORT sorts the processes before exporting to the output file. If omitted, it is output to the file in the order in which it is actually used. There are several sorting options:

Prscnt number of times parse was called

Prscpu cpu time parsing

Prsela elapsed time parsing

Prsdsk number of disk readsduring parse

Prsqry number of buffers forconsistent read during parse

Prscu number of buffers forcurrent read during parse

Prsmis number of misses inlibrary cache during parse

Execnt number of execute wascalled

Execpu cpu time spent executing

Exeela elapsed time executing

Exedsk number of disk readsduring execute

Exeqry number of buffers forconsistent read during execute

Execu number of buffers forcurrent read during execute

Exerow number of rows processedduring execute

Exemis number of library cachemisses during execute

Fchcnt number of times fetch wascalled

Fchcpu cpu time spent fetching

Fchela elapsed time fetching

Fchdsk number of disk readsduring fetch

Fchqry number of buffers forconsistent read during fetch

Fchcu number of buffers forcurrent read during fetch

Fchrow number of rows fetched

Userid userid of user that parsedthe cursor

PRINT lists only the SQL statement of the first integer in the output file. The default is all SQL statements.

AGGREGATE if = NO, then multiple identical SQL are not summarized.

An INSERT SQL statement that stores statistics for trace files in a database. After TKPROF creates the script, the results are entered into the database.

SYS disables or enables the listing of SQL statements issued by SYS users to the output file.

TABLE is used to hold the user name and table name of the temporary table before exporting to the output file.

EXPLAIN determines the execution plan for each SQL statement. And write the execution plan to the output file.

One of the more useful sorting options is fchela, which sorts the results of the analysis by elapsed time fetching (remember to set the initialization parameter timed_statistics=true), and the resulting file will display the most time-consuming sql first. Another useful parameter is sys, which is set to no to prevent all sql executed by sys users from being displayed, which reduces the complexity of the parsed file and makes it easy to view.

two。 Interpretation of the Tkprof command output:

First explain the meaning of the columns in the output file:

CALL: the processing of each SQL statement is divided into three parts

Parse: this step converts the SQL statement into an execution plan, which includes checking for the correct authorization and the existence of tables, columns, and other referenced objects.

Execute: this step is really for Oracle to execute the statement. For insert, update, delete operations, this step modifies the data, and for select operations, it simply determines the selected record.

Fetch: returns the records obtained in the query statement, in which only the select statement will be executed.

COUNT: the number of times this statement has been parse, execute, fetch.

CPU: this statement is the time in seconds of cpu consumed by all parse, execute, and fetch.

ELAPSED: all the total time spent in parse, execute, fetch in this statement.

DISK: the number of blocks physically read from a data file on disk. Generally speaking, you want to know more about the data being read from the cache than from the disk.

QUERY: the number of buffer obtained by all parse, execute, fetch in consistent read mode. Buffer in consistency mode is used to provide a snapshot of a consistent read to a long-running transaction, and the cache actually stores the state in the header.

CURRENT: the number of buffer obtained in current mode. Generally, buffer is obtained when performing insert, update or delete operations in current mode. In current mode, if a new cache is found in the cache to be sufficient for the current transaction, the buffer will be read into the cache.

ROWS: the number of records returned by all SQL statements, excluding the number of records returned in the subquery. For select statements, the record is returned at the fetch step, and for insert, update, and delete operations, the record is returned at the execute step.

three。 The steps for using Tkprof basically follow the following steps:

1. Set TIMED_STATISTICS to True, either at the session level or at the instance level.

Session level:

SQL > alter session settimed_statistics=True

Instance level:

SQL > alter system settimed_statistics=True scope=both

2. Set SQL_TRACE, either at the session level or at the database level.

Session level:

SQL > alter session set sql_trace=true

Instance level:

SQL > alter system set sql_trace=truescope=both

This is the end of the introduction about Oracle Tkprof. I hope the above content can be of certain reference value to you and can be applied. If you like this article, you might as well share it for more people to see.

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.