Re: Strange behaviour of Cramer query
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Fri, 26 Jun 2009 17:45:56 +0200
Message-ID: <4a44ed34$0$2854$ba620e4c_at_news.skynet.be>
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
>
Date: Fri, 26 Jun 2009 17:45:56 +0200
Message-ID: <4a44ed34$0$2854$ba620e4c_at_news.skynet.be>
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", read
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref955
Gerard Received on Fri Jun 26 2009 - 10:45:56 CDT