Re: TKPROF output. Help, please!!!

Suresh,

Now I see...
The numbers in the explain plan are adequate. The number of rows returned during fetch is adequate. The number of rows returned during execute is bogus. I have seen this more often. AFAIK the number will be adequate if an ORDER BY or GROUP BY is involved, as in those cases the complete resultset is in cache.

Hth,

--
Sybrand Bakker, Oracle DBA
<suchandr_at_my-deja.com> wrote in message news:85l0m3$70j$1_at_nnrp1.deja.com...
> Sybrand,
>
> Here is the actual TKPROF output. It is a very small table with about
> 150 records.
>
> ************************************************************************
> ********
>
> SELECT /*+ CACHE(T) */ASL FROM
> T WHERE CLASS_ID = :b1
>
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 317 0.02 0.03 0 0 0
> 364641539
> Fetch 317 0.01 0.03 10 757
> 0 123
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 635 0.03 0.06 10 757 0
> 364641662
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 47 (SUMMA) (recursive depth: 1)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 123 TABLE ACCESS (BY INDEX ROWID) OF 'T_CLASS'
> 317 INDEX (UNIQUE SCAN) OF 'PMJRCL00' (UNIQUE)
>
> ************************************************************************
> ********
>
> Thnx,
> -Suresh.
>
> In article <947711136.1957.0.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> > Aren't you looking at explain plan like output. The number of rows is
> > specified for each step in the plan, so you might have millions of
> rows
> > selected, which are filtered away later on.
> > If this doesn't answer your question please post the applicable part
> of the
> > tkprof output.
> >
> > Hth,
> >
> > --
> > Sybrand Bakker, Oracle DBA
> > <suchandr_at_my-deja.com> wrote in message
> news:85im7n$gvr$1_at_nnrp1.deja.com...
> > > In the TKPROF output, for rows column in the
> > > execute row is showing millions of rows for a
> > > select statement, which is fetching only about
> > > 100 rows.
> > >
> > > The select stmt in question is being executed in
> > > a loop many times (each time it feches a single
> > > row), and CACHE hint is in place for this stmt.
> > >
> > > Does anyone know, what this enormous number of
> > > rows mean...(I was under the impresion, execute-
> > > >rows will be seen for insert/delete/update. Am I
> > > wrong?). Elapsed time for this is only 6.00
> > > seconds though!
> > >
> > > Any help would be much appreciated.
> > >
> > > Thanks,
> > > -Suresh.
> > >
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 13 2000 - 12:32:14 CST