DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: tuning group by

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    tuning group by

    Hi

    Can somebody help me tune this query

    SELECT AL1.FISCAL_MONTH_CODE,AL5.FISCAL_QUARTER_CODE,
    AL5.FISCAL_WEEK_CODE,AL1.FROM_PLANT_ID,AL1.DEBIT_CREDIT_IND,
    TRUNC(AL1.POSTING_DATE) POSTING_DATE,AL1.INVENTORY_MOVEMENT_TYPE_CODE,
    AL1.SUPPLYING_PLANT_ID,AL1.VENDOR_CODE,AL1.SALES_DOCUMENT_TYPE_CODE,
    AL3.REGION_ID,AL3.CPS_PLANT,SUM(AL1.MOVEMENT_AMOUNT_USD)
    MOVEMENT_AMOUNT_USD,SUM(MOVEMENT_AMOUNT)
    MOVEMENT_AMOUNT,
    SUM(DECODE(DEBIT_CREDIT_IND,'H',-1 * MOVEMENT_AMOUNT_USD ,MOVEMENT_AMOUNT_USD))
    MOVEMENT_AMOUNT_DRCR,AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,
    AL2.VALUE_CODE,SYSDATE,USER, NULL,AL1.PO_LINE_CATEGORY_CODE,AL1.DIVISION_CODE
    FROM IBSS.CPS_INS_OUTS AL1,IBSS.CPS_PLANT AL3,IBSS.FISCAL_CALENDAR AL5,
    IBSS.I2_MATERIAL_CLASS AL2
    WHERE AL1.FROM_PLANT_ID = AL3.PLANT_ID
    AND AL1.POSTING_DATE =AL5.CALENDAR_DAY
    AND AL1.MATERIAL_NUMBER IS NOT NULL AND
    AL1.MATERIAL_NUMBER= AL2.MATERIAL_NUMBER (+)
    GROUP BY
    AL1.FISCAL_MONTH_CODE,AL5.FISCAL_QUARTER_CODE,
    AL5.FISCAL_WEEK_CODE,AL1.POSTING_DATE,AL1.INVENTORY_MOVEMENT_TYPE_CODE,AL1.FROM_PLANT_ID,
    AL1.SUPPLYING_PLANT_ID,AL1.VENDOR_CODE,AL1.DEBIT_CREDIT_IND,
    AL1.SALES_DOCUMENT_TYPE_CODE,AL3.REGION_ID,AL3.CPS_PLANT,
    AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,AL1.PO_LINE_CATEGORY_CODE,
    AL2.VALUE_CODE,AL1.DIVISION_CODE

    the plan is like

    Plan

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 14M| 1G| 431491 | | |
    | SORT GROUP BY | | 14M| 1G| 431491 | | |
    | HASH JOIN | | 14M| 1G| 40177 | | |
    | TABLE ACCESS FULL |CPS_PLANT | 290 | 3K| 1 | | |
    | HASH JOIN | | 14M| 1G| 40143 | | |
    | TABLE ACCESS FULL |FISCAL_CA | 3K| 82K| 4 | | |
    | HASH JOIN OUTER | | 14M| 1G| 39726 | | |
    | TABLE ACCESS FULL |CPS_INS_O | 14M| 968M| 24938 | | |
    | TABLE ACCESS FULL |I2_MATERI | 187K| 2M| 56 | | |


    Plan seems to be okay but i need to somehow make the group by less expensive and analytical function trick that can help ?

    regards
    Hrishy

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Hi,

    Check if the query has to write to temp tbs. If yes increase the size of sort/hash area. In case you are using workarea_size_policy=auto you may either: change it to manuel and define a larger sort/hash area size OR increase the size of _pga_max_size.

    It will probably not reduce the costs but increase the speed of the query.
    Last edited by mike9; 12-20-2004 at 02:03 PM.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you try parallelism b/c of all full table scans?

    Tamil

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    you may also try to make a pre aggregation of CPS_INS_O in a subquery. This subquery would be something like:

    select /*+ no_merge */
    AL1.POSTING_DATE
    AL1.MATERIAL_NUMBER ,
    AL1.INVENTORY_MOVEMENT_TYPE_CODE,
    AL1.FROM_PLANT_ID,
    AL1.SALES_DOCUMENT_TYPE_CODE,
    AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,AL1.PO_LINE_CATEGORY_CODE,
    AL1.DIVISION_CODE,
    SUM(...), SUM(...)
    from IBSS.CPS_INS_OUTS
    where
    AL1.MATERIAL_NUMBER IS NOT NULL
    group by
    AL1.POSTING_DATE
    AL1.MATERIAL_NUMBER ,
    AL1.INVENTORY_MOVEMENT_TYPE_CODE,
    AL1.FROM_PLANT_ID,
    AL1.SALES_DOCUMENT_TYPE_CODE,
    AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,AL1.PO_LINE_CATEGORY_CODE,
    AL1.DIVISION_CODE

    but this is only helpfull if it reduces a lot the amout of rows which has to be joined with the 4 other tables.

    HTH
    Mike

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by tamilselvan
    Did you try parallelism b/c of all full table scans?

    Tamil
    Hi Ia m using 8.1.7

    The query is not using parallel query even after hinting


    SELECT /* + parallel (AL1, 12) */
    AL1.FISCAL_MONTH_CODE,AL5.FISCAL_QUARTER_CODE,
    AL5.FISCAL_WEEK_CODE,AL1.FROM_PLANT_ID,AL1.DEBIT_CREDIT_IND,
    TRUNC(AL1.POSTING_DATE) POSTING_DATE,AL1.INVENTORY_MOVEMENT_TYPE_CODE,
    AL1.SUPPLYING_PLANT_ID,AL1.VENDOR_CODE,AL1.SALES_DOCUMENT_TYPE_CODE,
    AL3.REGION_ID,AL3.CPS_PLANT,SUM(AL1.MOVEMENT_AMOUNT_USD)
    MOVEMENT_AMOUNT_USD,SUM(MOVEMENT_AMOUNT)
    MOVEMENT_AMOUNT,
    SUM(DECODE(DEBIT_CREDIT_IND,'H',-1 * MOVEMENT_AMOUNT_USD ,MOVEMENT_AMOUNT_USD))
    MOVEMENT_AMOUNT_DRCR,AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,
    AL2.VALUE_CODE,SYSDATE,USER, NULL,AL1.PO_LINE_CATEGORY_CODE,AL1.DIVISION_CODE
    FROM IBSS.CPS_INS_OUTS AL1,IBSS.CPS_PLANT AL3,IBSS.FISCAL_CALENDAR AL5,
    IBSS.I2_MATERIAL_CLASS AL2
    WHERE AL1.FROM_PLANT_ID = AL3.PLANT_ID
    AND AL1.POSTING_DATE =AL5.CALENDAR_DAY
    AND AL1.MATERIAL_NUMBER IS NOT NULL AND
    AL1.MATERIAL_NUMBER= AL2.MATERIAL_NUMBER (+)
    GROUP BY
    AL1.FISCAL_MONTH_CODE,AL5.FISCAL_QUARTER_CODE,
    AL5.FISCAL_WEEK_CODE,AL1.POSTING_DATE,AL1.INVENTORY_MOVEMENT_TYPE_CODE,AL1.FROM_PLANT_ID,
    AL1.SUPPLYING_PLANT_ID,AL1.VENDOR_CODE,AL1.DEBIT_CREDIT_IND,
    AL1.SALES_DOCUMENT_TYPE_CODE,AL3.REGION_ID,AL3.CPS_PLANT,
    AL1.SHIP_TO_CUSTOMER_ID,AL1.CATEGORY,AL1.INS_OUTS_FLAG,AL1.PO_LINE_CATEGORY_CODE,
    AL2.VALUE_CODE,AL1.DIVISION_CODE

    SQL> @utlxpls.sql

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 14M| 1G| 431491 | | |
    | SORT GROUP BY | | 14M| 1G| 431491 | | |
    | HASH JOIN | | 14M| 1G| 40177 | | |
    | TABLE ACCESS FULL |CPS_PLANT | 290 | 3K| 1 | | |
    | HASH JOIN | | 14M| 1G| 40143 | | |
    | TABLE ACCESS FULL |FISCAL_CA | 3K| 82K| 4 | | |
    | HASH JOIN OUTER | | 14M| 1G| 39726 | | |
    | TABLE ACCESS FULL |CPS_INS_O | 14M| 968M| 24938 | | |
    | TABLE ACCESS FULL |I2_MATERI | 187K| 2M| 56 | | |
    --------------------------------------------------------------------------------

    NAME TYPE VALUE
    ------------------------------------ ------- ------------------------------
    fast_start_parallel_rollback string LOW
    optimizer_percent_parallel integer 0
    parallel_adaptive_multi_user boolean FALSE
    parallel_automatic_tuning boolean FALSE
    parallel_broadcast_enabled boolean FALSE
    parallel_execution_message_size integer 2148
    parallel_instance_group string
    parallel_max_servers integer 5
    parallel_min_percent integer 0
    parallel_min_servers integer 0
    parallel_server boolean FALSE
    parallel_server_instances integer 1
    parallel_threads_per_cpu integer 2
    recovery_parallelism integer 0

    regards
    Hrishy

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    what kind of server do you have (hardware/os) ?
    how many DBs are runing on this server?

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    also learn how to post properly using the code tags (as a moderator you should know that already)

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    http://download-uk.oracle.com/docs/c...1125.htm#66146

    OPTIMIZER_PERCENT_PARALLEL specifies the amount of parallelism that the optimizer uses in its cost functions.

    The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans.

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    parallel_max_servers integer 5
    and u request 12... not possible..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Mike

    Thanks for your help..i tried setting these values

    OPTIMIZER_PERCENT_PARALLEL to 100

    and i also tried the parallel hint with 4

    But optimizer is still stubborn :-)
    and its not using the paralllel query

    Any ideas

    regards
    Hrishy

Posting Permissions

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


Click Here to Expand Forum to Full Width