Components of a trace –
Sample of a TKPROF’ed file
PARSE – the phase where Oracle finds the query in the shared pool(called soft parse) or creates a new plan(hard parse)
EXECUTE – this is the work done by Oracle in the OPEN or EXECUTE part of the statement. It will be empty for SELECT stmt.
FETCH – will have the most part of processing in case of a SELECT stmt, but would be mostly empty in case of an UPDATE stmt.
COUNT – Gives count on how many times this phase of the query was performed. Ideally, the parse count of a query should be 1 and execute count can be 1 or more. In a properly written application, the parse count will be 1.
CPU – amount of cpu time spent in thousands of seconds.
ELAPSED – The actual clock time spent on the query. If the elapsed time is quite large than the value of cpu time, then it means oracle spent time waiting for something.
DISK – how many physical I/Os were performed on the query. Like we have 34 physical I/O reads in the above query.
QUERY – Number of logical I/Os performed to retrieve consistent mode blocks. Generally all physical I/Os result into a logical I/O.
CURRENT – number of logical I/Os performed to retrieve blocks as of now. This is used in cases when DML is performed in the query, like an UPDATE stmt. In this case, the query data must be retrieved in the current mode.
ROWS – The number of rows processed/ affected by this phase. For update, this value would be populated in the Execute phase, and in case of a SELECT stmt, this value would be in Parse phase.
