parallel query and direct path read

From: Ls Cheng <exriscer_at_gmail.com>
Date: Mon, 1 Jul 2013 13:51:25 +0200
Message-ID: <CAJ2-Qb-YoCDS94Q8OEehjmVTubCJ6+L0izwxVRXcSNxTtzfzqA_at_mail.gmail.com>

Hi
Does anyone know if direct path read cannot be used when the query is a join?

For example (dimension has parallel degree 8 so fact)

select *
from dimension d, fact f
where d.id = f.id;

Can direct path happen in this case? I am running 11.2.0.3 and I am pretty sure in 10gR2 this sort of query could use direct path reads.

The problem I am having is that to load 180 million of rows joined with 4 dimension tables it takes around 6 hours, in my past experience I did ETL processes which could load around 12 million of rows in a couple of minutes so 6 hours is definitely a lot IMHO. The only strange thing I have observed is that direct path is not used so the blocks lookup seems happening in the buffer cache which is slower in this particular case.

Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 01 2013 - 13:51:25 CEST