Oracle Performance Analysis 3: TKPROF introduction, oracletkprof

Oracle Performance Analysis 3: TKPROF introduction, oracletkprof

Tkprof is a command line tool that comes with Oracle. It is mainly used to convert the original trace file to a formatted text file. The simplest way to use it is as follows:

tkprof ly_ora_128636.trc ly_ora_128636.txt

Tkprof has many parameters. In most cases, using these parameters will be helpful for your analysis.

Tkprof Parameters

If you run tkprof without any parameters, it prints a complete list of parameters with a simple description. The following describes the parameters:

Explain
Provides an execution plan for each SQL statement. This parameter must specify the user and password. You can also specify the database connection string, for example, explain = user/password @ connect_string or explain = user/password.
Table
The bin explain parameter is used together to specify the table used to generate the execution plan. It is usually not required. It is required only when the user cannot create the table (for example, the create table permission is missing ).
Print
Limit the number of SQL statements generated by the output file, for example, print = 10.
Aggregate
Specifies whether to process SQL statements of the same content separately. It is not processed independently by default. Specify it as aggregate = no. Each SQL statement is used separately.
Insert
Generate an SQL script. The SQL script can be used to store information in the database. The name of the SQL script is specified by parameters, such as insert = load. SQL.
Sys
Whether the SQL statement run by the sys user is also written to the output file. The default value is yes.
Record
Generate an SQL script that contains all non-recursive statements found in the trace file. The script name is specified by parameters, for example, record = replay. SQL.
Waits
Whether to add the wait event information. It is added by default.
Sort
Specifies the order of SQL statements written to the output file. By default, the SQL sequence found in the trace file is used.

 

The following is an example:

tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela

Prsela: the time spent parsing the first cursor
Exeela: time spent on executing a cursor
Fchela: time spent by the cursor to obtain data rows

Tkprof output

The output file contains a header, which describes the parameters as follows:

**********************************************************************count    = number of times OCI procedure was executedcpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executingdisk     = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows     = number of rows processed by the fetch or execute call**********************************************************************

Count: Number of executed database calls
Cpu: CPU time spent processing data calls, in seconds
Elapsed: Total time spent processing database calls, in seconds
Disk: Number of physical read data blocks. If the number is greater than the number of logical reads (disk> query + current), a temporary tablespace is used.
Query: The number of fast reads from the cache logic in consistency mode, which is used for query.
Current: Number of blocks read from the cache logic in the current mode, used for insert, delete, merge, update, and other operations.
Rows: Number of data rows processed. Query indicates the number of rows obtained, while insert, delete, merge, and update indicate the number of rows affected.

Here is a specific example:

call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch      501      0.03       0.15          0       1465          0       50001------- ------  -------- ---------- ---------- ---------- ----------  ----------total      503      0.03       0.15          0       1465          0       50001

The above three stages correspond to parse, execute, and fetch respectively. In the fetch stage, 501 fetch is executed, 50001 rows of data are obtained, and 100 rows of data are obtained each fetch.
Next:

Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 5 

The first two lines indicate the number of hard resolutions that occur in the parsing and execution call phases. If there is no hard resolution, it does not exist.
"Optimizer mode" indicates the Optimizer mode.
"Parsing user id" is the user who parses SQL statements.
Then we can see the execution plan. Here we will just give a simple explanation:

Rows     Row Source Operation-------  ---------------------------------------------------  50001  COUNT STOPKEY (cr=1465 pr=0 pw=0 time=300125 us)  50001   VIEW  (cr=1465 pr=0 pw=0 time=200088 us)  50001    INDEX FULL SCAN IDX_HISTORYALARM$CLEAR (cr=1465 pr=0 pw=0 time=100049 us)(object id 53743)

Cr: Number of data blocks read logically in consistency mode
Pr: Number of data blocks read from the disk physically
Pw: Number of data blocks physically written to the disk
Time: The total time consumed in a subtle representation. Note that the data is not accurate.
Cost: Operation Evaluation overhead (only 11 GB)
Size: Estimated amount of data returned by the Operation (in bytes) (only 11 GB)
Card: The estimated number of rows returned by the Operation (only 11 GB)

The following is the waiting event:

  Event waited on                             Times Waited   Max. Wait  Total Waited  ----------------------------------------------------------------------------------  SQL*Net message to client                        502           0.00          0.00  SQL*Net message from client                      502           0.08         15.42  SQL*Net more data to client                      500           0.00          0.01

Times Waited: Wait time used
Max. Wait: Maximum waiting time for a single Wait event, in seconds
Total Waited: The total number of seconds waiting for an event is inaccurate.

Here you can see the waiting events encountered during execution. The analysis of these waiting events helps you understand the resources waiting for, query bottlenecks, and targeted optimization. You can find a brief description of most common wait events in the appendix of the Oracle Database Reference manual.

How Does oracle install tkprof? 112030 prompt not an internal command system oracle-linux57 with tkprof directly

Tkprof does not need to be installed. It is available in the bin directory under $ ORACLE_HOME on the machine where the oracle database is installed.

If oracle performs tkprof parsing on a trace file locally?

Tkprof needs to read environment variables to ensure that the path where tkprof is located is added to the path.