Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Question Unanswered: oracle query Tunning

    Kindly help to tune the below query. It was running for long time.

    Select a.* from (SELECT WEEK_ID,
    sum(nvl(DM_SALE_WEEK.BILL_BACK_CANADIAN_AMOUNT,0)) ,
    sum(nvl(DM_SALE_WEEK.BILL_BACK_US_AMOUNT,0)),
    sum(nvl(CANADIAN_AMOUNT,0)),
    sum(nvl(FACE_CANADIAN_AMOUNT,0)),
    sum(nvl(DM_SALE_WEEK.FACE_US_AMOUNT,0)),
    sum(nvl(DM_SALE_WEEK.GROSS_PROFIT_ADJ_CAN_AMOUNT,0 )),
    sum(nvl(DM_SALE_WEEK.GROSS_PROFIT_ADJ_US_AMOUNT,0) ),
    sum(nvl(DM_SALE_WEEK.GROSS_PROFIT_BASE_CAN_AMOUNT, 0)),
    sum(nvl(DM_SALE_WEEK.GROSS_PROFIT_BASE_US_AMOUNT,0 )),
    sum(nvl(DM_SALE_WEEK.MARKDOWN_UNIT_QTY,0)),
    sum(nvl(DM_SALE_WEEK.POST_OFF_CANADIAN_AMOUNT,0)),
    sum(nvl(DM_SALE_WEEK.POST_OFF_US_AMOUNT,0)),
    sum(nvl(DM_SALE_WEEK.PROMOTION_UNIT_QTY,0)),
    sum(nvl(DM_SALE_WEEK.PURCHASE_COST_CANADIAN_AMOUNT ,0)),
    sum(nvl(DM_SALE_WEEK.PURCHASE_COST_US_AMOUNT,0)),
    sum(nvl(DM_SALE_WEEK.TRANSACTION_COUNT,0)),
    sum(nvl(DM_SALE_WEEK.UNIT_QTY,0)),
    sum(nvl(DM_SALE_WEEK.US_AMOUNT ,0))
    FROM DM_SALE_WEEK group by week_id)A WHERE EXISTS( SELECT 1
    FROM dm_week b
    WHERE (A.Week_id = b.week_id) AND (b.CURRENT_WEEK_FLAG = 'Y' or b.PERIOD_DATE between
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW') - ((#CurrentYearWeeks# -
    1) * 7))
    AND
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW'))
    or b.PERIOD_DATE between
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW') - ((#PriorYearWeeks# -
    1) * 7) - 364)
    AND
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW') - 364)
    or b.PERIOD_DATE between
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW') -
    ((#PriorPriorYearWeeks# - 1) * 7) - (364 * 2)) AND
    (trunc(to_date(#SourceDate#,'YYYYMMDD'),'IW') - (364 * 2))))

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN
    Code:
    SELECT a.*
    FROM   (SELECT week_id,
                   SUM(Nvl(dm_sale_week.bill_back_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.bill_back_us_amount, 0)),
                   SUM(Nvl(canadian_amount, 0)),
                   SUM(Nvl(face_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.face_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_adj_can_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_adj_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_base_can_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_base_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.markdown_unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.post_off_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.post_off_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.promotion_unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.purchase_cost_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.purchase_cost_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.transaction_count, 0)),
                   SUM(Nvl(dm_sale_week.unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.us_amount, 0))
            FROM   dm_sale_week
            GROUP  BY week_id)a
    WHERE  EXISTS(SELECT 1
                  FROM   dm_week b
                  WHERE  ( a.week_id = b.week_id )
                         AND ( b.current_week_flag = 'Y'
                                OR b.period_date BETWEEN ( Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                                           - (
                                                           ( #currentyearweeks# - 1
                                                           )
                                                           * 7
                                                               ) ) AND (
                                                         Trunc(To_date(#sourcedate#,
                                                               'YYYYMMDD'
                                                               ),
                                                         'IW') )
                                OR b.period_date BETWEEN (
                                   Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                   -
                                                               (
                                   ( #prioryearweeks# - 1 )
                                   *
                                   7 ) - 364 ) AND (
                                                         Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'), 'IW'
                                                         )
                                                                             - 364 )
                                OR b.period_date BETWEEN ( Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                                           - (
                                                             (
                                                           #priorprioryearweeks# - 1
                                                             ) *
                                                             7 )
                                                           - ( 364 *
                                                           2 )
                                                         ) AND (
                                   Trunc(To_date(#sourcedate#
                                         ,
                                         'YYYYMMDD'),
                                   'IW')
                                   - (
                                   364 * 2 ) ) ))
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    5

    Hint

    Kindly suggest about Explain plan result and please provide necessary hint .

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 81 M 653 M
    SORT GROUP BY 81 M 18G 1129383
    FILTER
    PARTITION RANGE ALL 1 173
    TABLE ACCESS FULL DM.DM_SALE_WEEK_P 81 M 18G 399547 1 173
    VIEW DM.index$_join$_003 33 462 8
    HASH JOIN 33 462
    HASH JOIN 33 462
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX FULL SCAN DM.DM_WEEK_B06
    INDEX FAST FULL SCAN DM.DM_WEEK_N02 33 462 26.0000829541284
    INDEX FAST FULL SCAN DM.DM_WEEK_U01 33 462 26.0000829541284

  4. #4
    Join Date
    Apr 2009
    Posts
    5

    Post Suggestion

    Quote Originally Posted by anacedent View Post
    post EXPLAIN PLAN
    Code:
    SELECT a.*
    FROM   (SELECT week_id,
                   SUM(Nvl(dm_sale_week.bill_back_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.bill_back_us_amount, 0)),
                   SUM(Nvl(canadian_amount, 0)),
                   SUM(Nvl(face_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.face_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_adj_can_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_adj_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_base_can_amount, 0)),
                   SUM(Nvl(dm_sale_week.gross_profit_base_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.markdown_unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.post_off_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.post_off_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.promotion_unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.purchase_cost_canadian_amount, 0)),
                   SUM(Nvl(dm_sale_week.purchase_cost_us_amount, 0)),
                   SUM(Nvl(dm_sale_week.transaction_count, 0)),
                   SUM(Nvl(dm_sale_week.unit_qty, 0)),
                   SUM(Nvl(dm_sale_week.us_amount, 0))
            FROM   dm_sale_week
            GROUP  BY week_id)a
    WHERE  EXISTS(SELECT 1
                  FROM   dm_week b
                  WHERE  ( a.week_id = b.week_id )
                         AND ( b.current_week_flag = 'Y'
                                OR b.period_date BETWEEN ( Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                                           - (
                                                           ( #currentyearweeks# - 1
                                                           )
                                                           * 7
                                                               ) ) AND (
                                                         Trunc(To_date(#sourcedate#,
                                                               'YYYYMMDD'
                                                               ),
                                                         'IW') )
                                OR b.period_date BETWEEN (
                                   Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                   -
                                                               (
                                   ( #prioryearweeks# - 1 )
                                   *
                                   7 ) - 364 ) AND (
                                                         Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'), 'IW'
                                                         )
                                                                             - 364 )
                                OR b.period_date BETWEEN ( Trunc(
                                   To_date(#sourcedate#, 'YYYYMMDD'
                                   ), 'IW')
                                                           - (
                                                             (
                                                           #priorprioryearweeks# - 1
                                                             ) *
                                                             7 )
                                                           - ( 364 *
                                                           2 )
                                                         ) AND (
                                   Trunc(To_date(#sourcedate#
                                         ,
                                         'YYYYMMDD'),
                                   'IW')
                                   - (
                                   364 * 2 ) ) ))

    Kindly suggest about Explain plan result and please provide necessary hint .

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 81 M 653 M
    SORT GROUP BY 81 M 18G 1129383
    FILTER
    PARTITION RANGE ALL 1 173
    TABLE ACCESS FULL DM.DM_SALE_WEEK_P 81 M 18G 399547 1 173
    VIEW DM.index$_join$_003 33 462 8
    HASH JOIN 33 462
    HASH JOIN 33 462
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX FULL SCAN DM.DM_WEEK_B06
    INDEX FAST FULL SCAN DM.DM_WEEK_N02 33 462 26.0000829541284
    INDEX FAST FULL SCAN DM.DM_WEEK_U01 33 462 26.0000829541284
    Edit/Delete Message

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The EXPLAIN PLAN needs to be FORMATED using "code tags" as described below
    dBforums - BB Code List

    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;

    post DDL for table & all indexes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2009
    Posts
    5

    Reply

    Quote Originally Posted by anacedent View Post
    The EXPLAIN PLAN needs to be FORMATED using "code tags" as described below
    dBforums - BB Code List

    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;

    post DDL for table & all indexes
    --------------------------------------------------------------
    Please find the details below
    Version
    -------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    PL/SQL Release 9.2.0.4.0 - Production
    "CORE 9.2.0.3.0 Production"
    TNS for HPUX: Version 9.2.0.4.0 - Production
    NLSRTL Version 9.2.0.4.0 - Production

    DDL Pls find attachment.

    Index name =DM_SALE_WEEK_P_U01

    Unique Index
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What you are doing is summarising the whole of the dm_sale_week table (81 million rows by the looks of it) and then afterwards selecting the week/s you are after. Why not rewrite your query so you first figure out what weeks you are after and then only select from dm_sale_week for those weeks?

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •