Re: TKPROF Output Explanation

Dusan Bolek wrote:
> Hello,
>
> I'm trying to interpret SQL traces generated by users. There is about
> 120MB of them in more than thousand of files so it is not possible to
> process them in raw format. So I have used TKPROF instead of it. The
> reason why I'm doing this is to prepare as much as possible authentic
> test scenario representing a real production load. However, I have
> problem how to interpret numbers like this:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1105 0.05 0.09 0 106 0
> 0
> Execute 0 0.00 0.00 0 0 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 1105 0.05 0.09 0 106 0
> 0
>
> How it can happen that some statement was parsed many times without
> *ANY* actual executions (never used cursor?) and how it is possible to
> represent this in a simple test case?
>
> --
> Dusan Bolek
> http://www.db-support.com
>
> Email: spambin_at_seznam.cz
> Pls add "Not Guilty" to the subject, otherwise your email will face an
> unpleasant end as SPAM.
>

ODBC/JDBC connections involved? There are drivers that by default will parse a query before execution to get a 'describe' of the columns returned by the query.

Now if the programmer was aware of the fact that Oracle likes preparsed queries (parse once, execute many), she might have tried to create all possible cursors up front in a separate module, but never got around to use those cursors for what reason ever.

I was never able to get rid off all of these parses with ODBC so I threw away those applications where possible or made them use the native driver (those where my own applications).

In short: I'd consider this a bug in the application.

And to simulate this: DBMS_SQL is your friend.

HTH
Holger Received on Tue Mar 22 2005 - 03:06:24 CST