Random Slowdowns on Hash Joins
- From: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 5 Nov 2008 14:17:53 -0800 (PST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
