Re: TKPROF: Long CPU time on Execute

Fair enough; ORDER BY, GROUP BY would count during execute. But that isn't happening here.

I have an example of a trace of the same statement on another database (same execution plan) where the statement is executed 16753 times. In this case the CPU time for the fetch is slightly more at 1.42 seconds, but the CPU time for the execute is only 1.28 seconds, as opposed to the 1484 (nearly 25 minutes) seconds in the first example.

I have tried opening, fetching, and closing this cursor on a copy of the database (the high CPU example is from a client's database) 8700 times and it took 6.8 seconds after flushing the SGA, and then about 0.6 seconds on subsequent executions.

As I said in the original post, this statement was a simple example from the trace file, but others in there also have very high CPU times on the execute phase (e.g. 8845 seconds for 8453 executes; that's nearly 2.5 hours! - but only 20 seconds for 17154 fetches). Again this is for a statement with no ORDER BY, GROUP BY etc.

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<ir40805l80nli57f2uhdfcn8tm52a7bgnv_at_4ax.com>...

>
> That 1484 seconds for a statement which is called 8701 times, and 1.33
> seconds for 8701 fetches returning 0 rows.
> Doesn't look like unreasonable to me.
>
> Also: you are wrong in assuming only INSERT and UPDATE consume CPU
> during the execute phase. ORDER BY, GROUP BY and the like are executed
> on in the database, so they also count during execute. A range scan,
> as you can see counts during execute.
Received on Fri Apr 16 2004 - 18:29:48 CDT