TKProf output file has mainly following sections:-
1. Tabular Statistics
2. Row Source Operations
3. Wait Event Information
1. Tabular Statistics in details:
Before understanding these statistics we should understand following terms:
All row names and meaning of Tabular Statistics:
PARSE: Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE: Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH: Retrieves rows returned by a query. Fetches are only performed for SELECT statements.
All column names and meaning of Tabular Statistics:
count: It is the number of times a call (parse/execute/fetch) was performed.
cpu: Total CPU time in seconds
elapsed: Total elapsed time in seconds
disk: Total number of data blocks physically read from the data files on disk.
query: Total number of buffers retrieved in consistent mode.This column is incremented if a buffer is read in Consistent mode.This will include counts of blocks read from the rollback segment in order to 'rollback' a block.
current: Total number of buffers retrieved in current mode.
rows: Total number of rows processed by the SQL statement.
***Sum of query & current columns is the total number of buffers accessed i.e. LIOs.
In this section,first we should look for if any operation that is taking high number of cpu times or if we have high number of block reads i.e. query column, specially if the block read LIOs are very high compared to number of rows fetched. Another important thing is to see if there is lot of hard parsing which can be seen from count of parse call and the number of times it was hard parsed i.e. misses in library cache. All these things give a sense if there is something wrong with query or not and if there is a problem then where ? I would try to include some examples of Tabular Statistics in subsequent posts for scenarios where we have some problem with library cache or buffer cache etc.
2. Row Source Operations:
In Tabular Statistics,we understand if there is any problem with the query and then we can refer to Row Source Operations to see what operations are costly.
Where cr is consistent reads, pr is physical reads, pw is physical writes, time is time in microseconds.
3. Wait Event Information:
This is another important section of TKProf output file as it gives us the details on the wait events on which the query is waiting.From this information we can either try to reduce the cause for the waits or from row source information see if any other alternate access method can be used.
We know if there is problem with a hint about the problem area from tabular statistics and the most costly operation from row source operation, now we can get the details where this query is waiting from wait event information.
TKProf Interpretation (9i and above) (Doc ID 760786.1)
1. Tabular Statistics
2. Row Source Operations
3. Wait Event Information
1. Tabular Statistics in details:
Before understanding these statistics we should understand following terms:
All row names and meaning of Tabular Statistics:
PARSE: Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE: Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH: Retrieves rows returned by a query. Fetches are only performed for SELECT statements.
All column names and meaning of Tabular Statistics:
count: It is the number of times a call (parse/execute/fetch) was performed.
cpu: Total CPU time in seconds
elapsed: Total elapsed time in seconds
disk: Total number of data blocks physically read from the data files on disk.
query: Total number of buffers retrieved in consistent mode.This column is incremented if a buffer is read in Consistent mode.This will include counts of blocks read from the rollback segment in order to 'rollback' a block.
current: Total number of buffers retrieved in current mode.
rows: Total number of rows processed by the SQL statement.
***Sum of query & current columns is the total number of buffers accessed i.e. LIOs.
In this section,first we should look for if any operation that is taking high number of cpu times or if we have high number of block reads i.e. query column, specially if the block read LIOs are very high compared to number of rows fetched. Another important thing is to see if there is lot of hard parsing which can be seen from count of parse call and the number of times it was hard parsed i.e. misses in library cache. All these things give a sense if there is something wrong with query or not and if there is a problem then where ? I would try to include some examples of Tabular Statistics in subsequent posts for scenarios where we have some problem with library cache or buffer cache etc.
2. Row Source Operations:
In Tabular Statistics,we understand if there is any problem with the query and then we can refer to Row Source Operations to see what operations are costly.
Where cr is consistent reads, pr is physical reads, pw is physical writes, time is time in microseconds.
3. Wait Event Information:
This is another important section of TKProf output file as it gives us the details on the wait events on which the query is waiting.From this information we can either try to reduce the cause for the waits or from row source information see if any other alternate access method can be used.
We know if there is problem with a hint about the problem area from tabular statistics and the most costly operation from row source operation, now we can get the details where this query is waiting from wait event information.
TKProf Interpretation (9i and above) (Doc ID 760786.1)
https://expertoracle.com/2017/11/24/db-tuning-basics-6-trace-and-tkprof-part-2-generating-tkprof/
No comments:
Post a Comment