Christian Bilien’s Oracle performance and tuning blog

February 22, 2007

Trace analyzer vs Tkprof : beware of the recursive calls

Filed under: Oracle — christianbilien @ 10:02 am

Metrology (the science of measurement) is often underestimated. I came across a splendid example of ‘know what you are measuring’ today: I always use the trace analyzer in place of tkprof nowadays. However, a customer had already traced a session and came to me with tkprof metrics to work on. I spotted a huge difference in cpu and elapsed time on a few calls between statspack and the tkprof results.  Tkprof indeed does NOT account for recursive statements, and the time spent performing triggers is subtracted out of the statement.

An insert is triggering a select.

Here is the trace analyzer vs tkprof output, both for the insert and the underlying select. “Per insert” are the result of the total cpu and elapsed time divided by the number of calls.

Trace analyzer:

Per insert

elapsed (ms)

cpu (ms)

Insert

59

14

select (trigger)

26

6

Tkprof:

Per insert

elapsed (ms)

cpu (ms)

Insert

5,74

2,01

select (trigger)

26,14

5,84

Advertisements

Blog at WordPress.com.