Re: TKPROF question...

Zoran wrote:
>
> I've used SQL trace and tkprof to analyze some sql that some custom
> purchased software is throwing at the DB. I'm at home now and don't
> have a sample of the tkprof output to show you but...
>
> I have observed that many SQL statements have an execute count of 2
> and a row count of 2. Others have an execute count of 4 and a row
> count of 4. By looking at the select statement I can see that only 1
> row should have been retrieved with the particular select.
>
> My question; is this telling me that the select sql query was issued
> by the caller 2 times?
>
> I also see something along the lines of "library cache misses: 1". If
> indeed the select sql stmt was issued more than once, the first
> execute would result in a library cache miss, but the subsequent
> execute of the same select wouldn't. Am I interpreting this correctly?
>
> TIA
>
> Zoran.

hi,

I encountered the same behaviour, and it seems that the count=2 while returned rows=1 is due to the subject of Explicit/Implicit Cursors.
While with Explicit cursors (i.e. open -> fetch & close) the oracle server knows exactly how many rows were fetched in a particular fetch step (always 1 if not using array processing), the use of implicit cursors (such as select ... into :var from ...) forces oracle server to re-issue the command in order to find out wether one - and only one - row corresponds the select statement.

bye Received on Mon Mar 03 1997 - 00:00:00 CST