hash join extremely slow

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
--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 18 2006 - 10:19:50 CST