Random Slowdowns on Hash Joins
Date: Wed, 5 Nov 2008 14:17:53 -0800 (PST)
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)