Need Desperate help in Query Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Need Desperate help in Query Tuning

  1. #1
    Join Date
    May 2007
    Posts
    7

    Need Desperate help in Query Tuning

    can some one please help me to tune this query.

    Previously it was consuming 90% of CPU resources we have tuned ti to some extent and to my sourprise by removing the hint we got the very gud speed but its still consuming 60% of CPU resoruces.

    We have tested it on 3.4 million records and its updating 28K records in the custom table per minute.

    We have removed the ordered hint which was present earlier and also indexed one column called package_id which is in the IN condition( in red color)

    Any help on this will be highly appreciated.

    Here are some statistics..
    Below is the querry that still needs to be tuned further. Consuming 60% of the database resources.
    FYI - This query is bascially for Audit of Migrated data on a Billing System - Telecom Billing


    SELECT DISTINCT A.CUSTOMER_REF, ATTRIBUTE_VALUE FROM G42PRODUCTATTRIBUTE E, G42CUSTHASPACKAGE A, G42CUSTHASPRODUCT C, G42CUSTPRODUCTDETAILS B, G42CUSTPRODUCTATTRDETAILS D WHERE A.CUSTOMER_REF = :B2 AND B.ACCOUNT_NUM =:B1 AND A.CUSTOMER_REF = B.CUSTOMER_REF AND B.CUSTOMER_REF = C.CUSTOMER_REF AND A.PACKAGE_SEQ = C.PACKAGE_SEQ AND B.PRODUCT_SEQ = C.PRODUCT_SEQ AND A.CUSTOMER_REF = D.CUSTOMER_REF AND C.PRODUCT_SEQ = D.PRODUCT_SEQ AND D.PRODUCT_ID+0 = 1 AND D.PRODUCT_ATTRIBUTE_SUBID = E.PRODUCT _ATTRIBUTE_SUBID AND D.PRODUCT_ID+0 = E.PRODUCT_ID AND E.ATTRIBUTE_UA_NAME = 'CUST_ORD_NUMBER' AND A.PACKAGE_ID IN (1, 14, 15, 16, 19, 22, 23, 24, 26, 37)


    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    ---------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 103 | 2068 |
    | 1 | SORT UNIQUE | | 1 | 103 | 2068 |
    | 2 | TABLE ACCESS BY INDEX ROWID | G42CUSTPRODUCTATTRDETAILS | 1 | 27 | 1 |
    | 3 | NESTED LOOPS | | 1 | 103 | 2067 |
    | 4 | NESTED LOOPS | | 1 | 76 | 2066 |
    | 5 | NESTED LOOPS | | 4 | 236 | 2065 |
    | 6 | MERGE JOIN CARTESIAN | | 4 | 148 | 2064 |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 7 | TABLE ACCESS BY INDEX ROWID| G42PRODUCTATTRIBUTE | 2 | 42 | 1 |
    | 8 | INDEX RANGE SCAN | G42PRODUCTATTRIBUTE_AK2 | 6 | | 1 |
    | 9 | BUFFER SORT | | 2 | 32 | 2063 |
    | 10 | INDEX FULL SCAN | G42CUSHASPKG_CUSREF_PKSQ_PKID | 2 | 32 | 6875 |
    | 11 | TABLE ACCESS BY INDEX ROWID | G42CUSTPRODUCTDETAILS | 1 | 22 | 1 |
    | 12 | INDEX RANGE SCAN | G42CUSTPRODUCTDETAILS_AK3 | 1 | | 1 |
    | 13 | TABLE ACCESS BY INDEX ROWID | G42CUSTHASPRODUCT | 1 | 17 | 1 |
    | 14 | INDEX RANGE SCAN | G42CUSTHASPRODUCT_PK1 | 1 | | 0 |
    | 15 | INDEX RANGE SCAN | G42CUSTPRODUCTATTRDETAILS_PK1 | 1 | | 1 |
    ---------------------------------------------------------------------------------------------------

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1. What is the significance of D.PRODUCT_ID+0 = 1 ?

    2. MERGE JOIN CARTESIAN | | 4 | 148 | 2064 |
    The kernel developers of Oracle do not know any thing about "MERGE JOIN CARTESIAN" operation. Get rid of this operation either by using HINT such as USE_HASH or USE_NL. Enable 10076 events to trace the SQL. You will learn more about MERGE JOIN.

    3. Post the values of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ.

    4. Do you collect system statistics?
    Last edited by tamilselvan; 05-12-2007 at 09:21 AM.

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    You need to decide upon a driving table.

    This query has 5 tables which is gives about a 5! = 120 combinations for the optimizer to consider.... You have 4 choices for a driving table. Select the one that has the fewest rows returned after applying filtering criteria.

    Drawing a directed graph helps with tremendously with querry tuning.

    Run these 3 querries:

    select count(*) from G42CUSTHASPACKAGE A
    where A.CUSTOMER_REF = :B2 and A.PACKAGE_ID IN (1, 14, 15, 16, 19, 22, 23, 24, 26, 37)

    Substitute a customer ref value for :b2...

    Select count(*) from G42PRODUCTATTRIBUTE E
    where E.ATTRIBUTE_UA_NAME = 'CUST_ORD_NUMBER'

    select count(*) from G42CUSTPRODUCTDETAILS B
    where B.ACCOUNT_NUM =:B1

    Then pick the table from above that has the fewest rows as the "driving table".

    Then tune out the MERGE JOIN CARTESIAN.

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