Oracle Database Discussions

Hi there,

(DB= 11.2.0, OS= OL 5)

I have a query which it takes approximately 20 seconds if I run it manually against the server via a tool like PL/SQL Developer and takes about 7 minutes if it runs via a reporting tool like BI Publisher. I dont have this problem with other queries. the query is against a huge table containing 800 million records. through monitoring EM the problem seems to be the wait event "DIRECT PATH WRITE TEMP". But when I execute it manually that will not be an issue. I'm sure the queries are the same in both environments. Application Server (where the BIP runs on it) access to DB server is through a LAN and my access is through a VPN over the net. By the way, the query has no order by clause. The memory_target_parameter is 20g and sort_area_size is 65536.

Please help me to understand why two behaviors of DB against a same query and why the wait event "DIRECT PATH WRITE TEMP" occurs and how I can resolve it.

THANKS
SMSK