Re: Query Tuning Help

From: A Ebadi <ebadi01_at_yahoo.com>
Date: Thu, 1 Nov 2007 08:30:47 -0700 (PDT)
Message-ID: <653011.84483.qm@web51109.mail.re2.yahoo.com>

Thanks to everyone who replied. The version below from Finn (adding hash hint) ran in 16 minutes!

Finn Jorgensen <finn.oracledba_at_gmail.com> wrote:

The biggest problem for this query is that the IN results in a FILTER operation, which is very slow, and I'm guessing the inner "MINUS" query returns a lot of rows.

I'm guessing there's no index on "guid" in call_detail?

Try this version (it's hard tuning queries without access to the database so a little testing can be done) :

SELECT /*+ parallel(a,10) use_hash(a b) */

         billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value),
         SUM (rated_cost_amt)
    FROM cdrw.call_detail a,
  (            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')) b
    WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') 
                        AND (record_typ='STOP' OR call_duration_value>0)
     AND a.guid = b.guid

GROUP BY billing_act_nbr, final_tg_nbr;

Finn

On 10/31/07, A Ebadi <ebadi01_at_yahoo.com> wrote: 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

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

Received on Thu Nov 01 2007 - 10:30:47 CDT