Re: hash join extremely slow
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Mon, 18 Dec 2006 22:47:43 +0200
Look if the hash join isn't actually doing something behind the scenes.
All long operations like Table scan, Hash join, Sort output can do
many other things behind the scenes.
For example for hash joins you can write a cursor that is doing hash
join and you see the hash join in v$session_longops. But in the cursor
you can do infinite other things.
So the simple example is
CURSOR c IS
SELECT *
FROM a, b
WHERE a.id = b.id;
FOR i in c LOOP
do whatever you like here that takes hours and hours
END LOOP;
I doubt that your box is soooooooooooooo overloaded that a simple hash
join without any other extra work can work so long. And BTW these
sequential reads point that you have probably some other logic inside
the cursor.
Gints Plivna
http://www.gplivna.eu
2006/12/18, LS Cheng <exriscer@xxxxxxxxx>:
Hi
To my surprise it is not reading the temporary tablespace, it is doing db
file sequential read.
However my point is why almos 5 hours is needed to perform a 75789 block
hash join!
TIA
--
LSC
On 12/18/06, Michael McMullen <ganstadba@xxxxxxxxxxx> wrote:
> I would guess it's writing out to disk. What does v$sort_usage show?
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
