Introduction to Oracle Performance Analysis 3:tkprof

Tkprof It is Oracle it comes with a command plug-in tool whose primary function is to convert the original trace file to the type of the text file, for example, the simplest method, using the following:

Tkprof LY_ORA_128636.TRC Ly_ora_128636.txt

Tkprof has a very large number of parameters, and in most cases, using these parameters will be very helpful for your analysis.

Tkprof Number of references

Assuming that the tkprof is executed without any parameters, it will print out a complete list of references with a simple descriptive narrative. The following is a description of the number of references:

Explain
Provides a run schedule for each SQL statement.

This parameter requires a user and password to be specified. You can also specify a database connection string, such as: explain=user/[email protected]_string or Explain=user/password.

Table
The tray explain is used together to specify the tables used by the build run plan, usually without specifying, only if the user cannot create the table (such as the lack of CREATE TABLE permissions).
Print
Used to limit the number of SQL statements generated by the output file, such as: print=10.
Aggregate
An SQL statement that specifies whether the same content is processed separately. The default is not handled separately. Specify as Aggregate=no, looking at each individual SQL statement.

Insert
Generates SQL scripts that can be used to store information in the database, with the name of the SQL script specified by the parameters, such as: Insert=load.sql.
Sys
Specifies whether the SQL statement executed by the SYS user is also written to the output file, by default yes.

Record
Generate SQL scripts. It includes all the non-recursive statements found in the trace file, and the name of the script is specified by the parameters themselves. For example: Record=replay.sql.

Waits
Whether to join the wait event information, 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's a sample:

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

Prsela: Time spent in first cursor parsing
Exeela: Time spent on cursor operation
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 number of references. For example, the following:

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 running
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 data blocks that are physically read, assuming a greater than the number of logical reads (Disk>query+current), indicates that a temporal tablespace is used.

Query: The fast number of reads from the fast cache logic in consistent mode. Used as a query.
Current: The number of blocks read from the fast 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. Insert, delete, merge, and update indicate the number of rows affected.

Here is a detailed 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 3 phases of parse, execute, and fetch are respectively corresponding to the above. 501 fetches were run 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 occurrences that occurred during the parse and run invocation phases. Suppose there is no hard parsing. is not present.
"Optimizer Mode" represents the optimizer pattern.

The "Parsing user ID" is the user who resolves the SQL statement.
After that you can see the run plan, 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-projected 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. Not accurate

Here you can see the wait events encountered during the run, through the analysis of these wait events. Helps you understand what resources are waiting for, the bottleneck of the query, and the optimizations that are targeted to make.

The manual describing the most common wait events can be found in the appendix of the Oracle Database reference in a short narrative.

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

Introduction to Oracle Performance Analysis 3:tkprof