Re: Random Slowdowns on Hash Joins

Charlotte,

  Since you insist one the fact that the elapsed time is much higher, I presume that the CPU time is about the same. What do the trace file says about waits? That's probably where you'll find the difference. What kind of storage have you at the back? If it's a shared SAN, it's quite possible that some other application flushes the cache - same nominal number of physical I/Os for Oracle, but very different time since instead of hitting the SAN cache you really have to get the data from disk. It should show somewhere. If someone is randomly computing statistics in your back in another database on an other machine that shares the SAN storage, it can happen ...

HTH Stéphane Faroult

Charlotte Hammond wrote:
> Hi All!
>
> We're running a batch job on test. After each run everything is reset using an RMAN restore. So each time it runs with exactly the same data, execution plans etc.
>
> However sometimes (occassionally) its about 2-3 times as slow as normal. Comparing a slow trace file with a normal trace file I find that execution plans are the same (as expected) and the biggest difference is that HASH JOINs all have (say) e=80000 instead of (say) e=20000.
>
> Hash Joins are small but very frequent in this job, so this does add up to the time difference observed. But why is this slow down happening?
>
> The "server" is a Sun Solaris LDOM on a T2 processor with 32 CMTs masquerading as 32 virtual processors. This may not be relevant but that's where my suspicion is at the moment. The load is fairly consistent during each test - a few (maybe 6 or 7) application connections doing minor bits of work concurrently but the overall load is low. The batch job is single threaded (no parallel queries etc.)
>
> Any ideas or suggestions for further diagnostics? (This is Oracle 10.2.0.3 EE on Solaris 10)
> (BTW - it's frustrating trying to get it to be slow when you want it, it just sort of happens from time to time unpredictably)
>
> Many thanks!
> Charlotte
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Coming speaking engagements:
Beijing, Shanghai 
<http://www.oracle.com/global/cn/education/promotions/stephanefaroult_cele_seminar_cn.htm>, 
Hong-Kong 
<http://www.oracle.com/education/images/apac_newsletter/seminar/hk_stephanefaroult.html> 
and Singapore 
<http://www.oracle.com/education/images/apac_newsletter/seminar/sg_stephanefaroult.html>. 


--
http://www.freelists.org/webpage/oracle-l