Parallel queries

From: Ken Naim <kennaim_at_gmail.com>
Date: Fri, 25 Apr 2008 13:41:01 -0400
Message-ID: <BA045C98FDBE48C8AFFD62D2674C946C@KenPC>


I have an "Insert append nologging as select" query that joins a 71 million row table against a partition of a multi-billion row table which has 700 million rows which is directly specified using the extended syntax for partitions. Result set should be around 200 million rows. The explain plan is simple, full scan of the heap table and partition then a hash join of the two followed by a has group by and finally the insert. My test box is a sun box with 1 processor and 8 cores. I am explicitly issuing a parallel of 5 of each table (reduced form 10 each with same symptoms) and the full scans and hash join(s) complete within 1 hour based on v$session_longops. After 5 of slaves go to an inactive status and the other 5 have PX deq credit: send blk with a p1text/p2text/p3text of sleeptime/senderid, passes, qref. My parallel message size is 16k. The stats tab in toad shows PX:local messages received of just over 1 million and a slowly increasing PX:local messages sent of 600,000 an hour and a half after the hash join finished. Cpu utilization is currently at 2-3% 97% idle.I have searched through Metalink, google and have read all the papers on parallel execution (including Doug Burn's Suck it Dry) and am not sure where to go from here. Any ideas, theories, hints or links would be appreciated.  

As a side note. I was shot down for sub-partitioning or repartitioning to a lower level by the application vendor as it would hurt the performance of their engine. Currently the table is partitioned by quarter based on sales date and partitions vary between 30 million and 700 million records for reasons too numerous to explain.  

Thank you,

Ken Naim    

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 25 2008 - 12:41:01 CDT

Original text of this message