hash join extremely slow
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Dec 2006 17:19:50 +0100
Hi
I have a large query running and right now it is doing a hash join, from
v$session_longops it shows this:
select opname, sofar, totalwork, units, time_remaining, elapsed_seconds from
v$session_longops where sid = 631
and time_remaining > 0
OPNAME SOFAR TOTALWORK UNITS
TIME_REMAINING ELAPSED_SECONDS
---------- ---------- ---------- --------------------------------
-------------- ---------------
Hash Join 5922 75789 Blocks
14523 1231
after 30 seconds
OPNAME SOFAR TOTALWORK UNITS
TIME_REMAINING ELAPSED_SECONDS
---------- ---------- ---------- --------------------------------
-------------- ---------------
Hash Join 6363 75789 Blocks
15526 1423
I wonder what is going on and this hash join needs 4.5 hours to finish?
Oracle 9.2.0.4, HP-UX
TIA
--
LSC
Other related posts:
