Query Tuning Help

We've been trying to tune this query below, but to no avail.  The table 
call_detail below is a very large hourly partitioned table with each partition 
being about 2-4GB!  The other table (call_detail_cost_vero) is relatively 
small.  
   
  The two inner selects with minus runs fine alone in about 6-8 minutes, but 
the entire query doesn't finish even after running it for many hours!  We've 
tried running it in parallel/no parallel and hasn't helped.  Any 
recommendations would be appreciated.  The explain plan is at the bottom of 
this e-mail also.
   
  Thanks,
  Abdul
   
  Environment: Sun Solaris, Oracle 10.2.0.3.0 on RAC (4 node)
   
  SELECT /*+ parallel(a,10) */
         billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value),
         SUM (rated_cost_amt)
    FROM cdrw.call_detail a
             WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
                        AND (record_typ='STOP' OR call_duration_value>0)
     AND guid IN (
            SELECT
                   guid
              FROM cdrw.call_detail c
             WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
                        AND (record_typ='STOP' OR call_duration_value>0)
            MINUS
            SELECT
                   guid
              FROM cdrw.call_detail_cost_vero d
             WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss'))
GROUP BY billing_act_nbr, final_tg_nbr;
   
   
  EXPLAIN PLAN
  QUERY_PLAN
------------------------------------------------------------------------------------------------------------------------
 SELECT STATEMENT   Cost = 32
  2.1 HASH GROUP BY
    3.1 FILTER
      4.1 PX COORDINATOR
        5.1 PX SEND QC (RANDOM) ":TQ10000"
          6.1 PX BLOCK ITERATOR
            7.1 TABLE ACCESS FULL "CALL_DETAIL" TABLE
      4.2 MINUS
        5.1 SORT UNIQUE NOSORT
          6.1 PARTITION RANGE SINGLE
            7.1 TABLE ACCESS BY LOCAL INDEX ROWID "CALL_DETAIL" TABLE
  QUERY_PLAN
------------------------------------------------------------------------------------------------------------------------
              8.1 INDEX RANGE SCAN "CALL_DETAIL_UK" INDEX (UNIQUE)
        5.2 SORT UNIQUE NOSORT
          6.1 PARTITION RANGE SINGLE
            7.1 INDEX RANGE SCAN "CALL_DETAIL_COST_VERO_PK" INDEX (UNIQUE)

   

 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Other related posts: