Re: Query Tuning Help

Do you have the option of separating it into two queries by creating a
temporary table, something like this (the parenthesis may be off, you'll
have to check).  I have seen this help quite often:

create table worktemp as select * from cdrw.call_detail where
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'));

SELECT
         billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value),
         SUM (rated_cost_amt)
    FROM worktemp a
             WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
                        AND (record_typ='STOP' OR call_duration_value>0)
GROUP BY billing_act_nbr, final_tg_nbr;




On 10/31/07, A Ebadi <ebadi01@xxxxxxxxx> 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
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Other related posts: