NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

TKPROF Overview

No comments
TKPROF is used for diagnosing performance issues.  It formats a trace file into readable format for performance analysis.

Syntax:
tkprof tracefile_name.trc  tracefileoutput.txt sys=no  sort='(prsela,exeela,fchela)'  explain=apps/apps
  
Options for tkprof
 print – Lists only the first n SQL statements in the output file.  If nothing is specified, all statements will be listed.  Use this option when the list needs to be limited to the 'Top n' statements.  This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.
aggregate – When 'Yes', tkprof will combine the statistics from multiple user executions of the same SQL statement.  When 'No', the statistics will be listed each time the statement is executed.
insert – Creates a file that will load the statistics into a table in the database for further processing.  Choose this option if you want to perform any advanced analysis of the tkprof output.
sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements.  Default=enable. 
table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table.  The user must specify the schema and table name for the plan table.  If the table exists all rows will be deleted otherwise tkprof will create the table and use it.
record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file.  Log the SQL statements in a separate *.sql file.
explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan provides the predicted optimizer execution path without actually executing the statement.  tkprof shows you the actual execution path and statistics after the statement is executed.
sort – Sorts the SQL statements in the trace file by the criteria required.It provides SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers.

Sort options are as below
  •  prscnt – The number of times the SQL was parsed.
  •  prscpu – The CPU time spent parsing.
  •  prsela – The elapsed time spent parsing the SQL.
  •  prsdsk – The number of physical reads required for the parse.
  •  prsmis – The number of consistent block reads required for the parse.
  •  prscu - The number of current block reads required for the parse.
  •  execnt – The number of times the SQL statement was executed. 
  •  execpu – The CPU time spent executing the SQL. 
  •  exeela – The elapsed time spent executing the SQL.
  •  exedsk – The number of physical reads during execution.
  •  exeqry – The number of consistent block reads during execution.
  •  execu – The number of current block reads during execution.
  •  exerow – The number of rows processed during execution.
  •  exemis – The number of library cache misses during execution.
  •  fchcnt – The number of fetches performed.
  •  fchcpu – The CPU time spent fetching rows.
  •  fchela – The elapsed time spent fetching rows.
  •  fchdsk – The number of physical disk reads during the fetch.
  •  fchqry – The number of consistent block reads during the fetch.
  •  fchcu – The number of current block reads during the fetch.
  •  fchrow – The number of rows fetched for the query.
 

No comments :

Post a Comment