Oracle Performance Analysis 3:tkprof Introduction

Tkprof is a command-line tool provided by Oracle, the primary role is to convert the original trace file to a formatted text file, the simplest way to use the following:

Tkprof LY_ORA_128636.TRC Ly_ora_128636.txt

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

Tkprof parameters

If you run Tkprof without any parameters, it will print out a complete list of parameters with a simple description. The following is a description of the parameters:

Explain
Provide an execution plan for each SQL statement. This parameter requires the user, password, or database connection string to be specified, such as explain=user/[email protected]_string or Explain=user/password.
Table
The tray explain parameter is used together to specify the tables that are used by the build execution plan, which is typically not required and is required only if the user cannot create tables (such as the lack of CREATE TABLE permissions).
Print
Used to limit the number of SQL statements generated by the output file, for example: print=10.
Aggregate
A SQL statement that specifies whether to handle the same content separately, by default, not handled separately. Specify for Aggregate=no, see each SQL statement separately.
Insert
Generate SQL scripts, SQL scripts can be used to store information into the database, the SQL script name is specified by the parameters, such as: Insert=load.sql.
Sys
Specifies whether the SQL statement run by the SYS user is also written to the output file, by default yes.
Record
Generates a SQL script that contains all the non-recursive statements found in the trace file, with the name of the script specified by the parameter itself, for example: Record=replay.sql.
Waits
Whether to add information for the wait event, added by default.
Sort
Specifies the order in which the SQL statements are written to the output file. The default is the SQL order found in the trace file.

Here is an example:

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

Prsela: Time spent in first cursor parsing
Exeela: Time spent on cursor execution
Fchela: The amount of time the cursor takes to get a data row

Tkprof output

The output file has a header with a description of 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 physic Al reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers Gotte N in current mode (usually to update) rows     = number of rows processed by the fetch or execute call******************** **************************************************

Count: Number of database calls executed
CPU: The CPU time, in seconds, spent processing data calls
Elapsed: The total amount of time, in seconds, spent processing database calls
disk: The number of physical read blocks, if greater than the number of logical reads (Disk>query+current), represents the use of temporal tablespace.
Query: A fast number that is read from the cache logic in consistent mode and is used for queries.
Current: The number of blocks read from the cache logic in this mode for operations such as INSERT, delete, merge, and update.
rows: The number of data rows processed. The query represents the number of rows fetched, while insert, delete, merge, and update represent the number of rows affected.

Let's look at 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

It corresponds to the 3 stages of parse, execute, and Fetch respectively, performing 501 fetches in the fetch phase, fetching 50001 rows of data, and fetching 100 rows of data per fetch.
Next is:

The first two lines represent the number of hard-resolved quantities that occur during the parsing and executing call stages, and do not exist if there is no hard parsing.
"Optimizer Mode" represents the optimizer pattern.
The "Parsing user ID" is the user who resolves the SQL statement.
After that, you can see the execution plan, and here's just a brief 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 logically read in consistent mode
PR: Number of data blocks physically read from disk
PW: Number of data blocks physically written to disk
Time: A subtle representation of the overall cost-taking, noting that the data is inaccurate
Cost: Evaluation overhead for operations (only 11g)
size: The amount of pre-estimated data returned by the operation (in bytes) (only 11g is available)
Card: The number of pre-estimated rows returned by the operation (only 11g is available)

The next step is to wait for the 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           0.00          0.01

Times waited: Waiting time takes time
Max. wait: The maximum wait time for a single wait event, in seconds
Total waited: Number of wait seconds for a wait event, imprecise

Here you can see the wait events encountered during execution, through the analysis of these wait events, to help you understand what resources are waiting for, the bottleneck of the query, to make the optimization. A short description of most common wait events can be found in the appendix to the Oracle Database reference manual.