Re: Why Statistics doesn't match reponse time?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 Sep 2005 06:04:02 +0000 (UTC)
Message-ID: <dg5q4i$7s7$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Bin" <wangbinlxx_at_gmail.com> wrote in message news:1126573393.114555.65700_at_g14g2000cwa.googlegroups.com...
>I test it when enable '_rowsource_execution_statistics'. Get the same
> result as enable sql_trace.
> After 'flush shared_pool', both queries use the same plan.
>
> Thanks again,
> Bin
>

True,

Turning on sql_trace automatically enables _rowsource_execution_stats (from 9.2.0.3 onwards, I think). That's how Oracle can give you the w= r= etc. values on the execution plan labelled 'rowsource' when you use tkprof against a trace file..

If you keep checking v$sql after flushing the shared pool, I think you should find that

Query without sql_trace -> child number 0 query with sql_trace -> child number 1

flush pool

Query without sql_trace -> child number 0 Query with _row_source_execution -> child number 1

flush pool

        Query without sql_trace -> child number 0
        query with sql_trace -> child number 1
        Query with _row_source_execution -> no new child.

When I'm testing something like this, I often add a 'fake' hint to the sql each time I run it so that I can be sure that it will generate it's own cursor and a new plan, e.g.

/*+ test007 */ Received on Tue Sep 13 2005 - 01:04:02 CDT