Re: Strange behaviour of Cramer query

thanks for your reply.

But there was only 1 plan in v$sql_plan.

dbms_xplan will look there too.

Regards Hans-Peter

On 26 jun, 17:45, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> HansPeter schreef:
>
>
>
> > Hello,
>
> > A lot has been said sofar.
>
> > To make things clearer.
> > The statement I execute has 2 bind variables. Every execution uses the
> > same bind variables.
> > The big difference in wait events is
> >   Event waited on                             Times   Max. Wait  Total
> > Waite
> >   direct path write temp                      53363
> > 0.18         14.53
> >   direct path read temp                      298016        0.61
> > 607.8
>
> > The direct path events are not present in the quick case.
>
> > But the main problem is why are these present in execution N but not
> > in execution N-1.
> > Some people suggest that this is due to caching. But that does not
> > make sence because the query is also slow after a quick execution a
> > second before.
> > Furthermore the caching does not apply to temp segments.
>
> > It just makes no sense that the same execution plan with the same bind
> > variable and the same rows that are yielded suddenly shows
> > 9 million processed rows for certain row source operations and next
> > time 0 rows and the next time 9 million rows again.
>
> > Looks like a bug to me.
>
> > regards HansP
>
> Watch out with the explain plans returned by tkprof.  They may not be the same plan as was used
> during execution.  Next time it is slow, use dbms_xplan, there is plenty of time ;-), just to
> make sure.
>
> About "direct path read temp", readhttp://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/i...
>
> Gerard
Received on Sat Jun 27 2009 - 10:09:10 CDT