Results 1 to 4 of 4

Thread: Query To Tune

  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: Query To Tune

    Hi,

    The Below query is taking lot of time. I want to reduce CPU Cost. It is taking 6380348. Please help me out. The Columns I am using in this query will have Indexes and Primary keys.

    SELECT DISTINCT ADDR.ADDR_1_DSC, ADDR.ADDR_2_DSC, ADDR.US_CITY_ID, CITY.CITY_NAM, COUNTY.COUNTY_NAM,
    STATE.ADDR_STATE_CD, ADDR.ADDR_POSTAL_CD, ADDR.ADDRESS_UPDATE_DT, ADDR.DATA_SOURCE_TYPE
    FROM TB_CMA006_CUSTOMER_ADDRESSES ADDR, TB_CMA040_CUSTOMER_ADDRESS_USE ADDR_USE, TB_CMA086_US_CITY CITY,
    TB_CMA085_US_COUNTY COUNTY, TB_CMA004_US_STATE STATE
    WHERE ADDR.CUSTOMER_MASTER_NUM = '1603246' AND ADDR_USE.CUSTOMER_MASTER_NUM = ADDR.CUSTOMER_MASTER_NUM
    AND ADDR_USE.CUSTOMER_ADDRESS_CONTACT_NUM = ADDR.CUSTOMER_ADDRESS_CONTACT_NUM
    AND ADDR_USE.SITE_USE_CD = 'BILL_TO' AND ADDR_USE.SITE_TYPE_PRIMARY_FLG = 'Y'
    AND CITY.US_CITY_ID = ADDR.US_CITY_ID AND COUNTY.ADDR_COUNTRY_ID = CITY.ADDR_COUNTRY_ID
    AND COUNTY.ADDR_STATE_ID = CITY.ADDR_STATE_ID AND COUNTY.COUNTY_ID = CITY.COUNTY_ID
    AND STATE.ADDR_COUNTRY_ID = COUNTY.ADDR_COUNTRY_ID AND STATE.ADDR_STATE_ID = COUNTY.ADDR_STATE_ID


    Description Obj Owner Obj Name Cost Cardinality Bytes CPU Cost Optimizer
    SELECT STATEMENT, GOAL = ALL_ROWS 11 1 151 6380348 ALL_ROWS
    HASH UNIQUE 11 1 151 6380348
    NESTED LOOPS 10 1 151 82245
    NESTED LOOPS 9 1 139 73864
    NESTED LOOPS 8 1 116 64632
    NESTED LOOPS 7 1 86 55361
    TABLE ACCESS BY INDEX ROWID CMADBA TB_CMA040_CUSTOMER_ADDRESS_USE 5 1 24 38128 ANALYZED
    INDEX RANGE SCAN CMADBA CI_CMA040_ID1 3 3 22164 ANALYZED
    TABLE ACCESS BY INDEX ROWID CMADBA TB_CMA006_CUSTOMER_ADDRESSES 2 1 62 17233 ANALYZED
    INDEX UNIQUE SCAN CMADBA CN_CMA006_PK 1 1 9021 ANALYZED
    TABLE ACCESS BY INDEX ROWID CMADBA TB_CMA086_US_CITY 1 1 30 9271 ANALYZED
    INDEX UNIQUE SCAN CMADBA CN_CMA086_PK 0 1 1900 ANALYZED
    TABLE ACCESS BY INDEX ROWID CMADBA TB_CMA085_US_COUNTY 1 1 23 9231 ANALYZED
    INDEX UNIQUE SCAN CMADBA CN_CMA085_PK 0 1 1900 ANALYZED
    TABLE ACCESS BY INDEX ROWID CMADBA TB_CMA004_US_STATE 1 1 12 8381 ANALYZED
    INDEX UNIQUE SCAN CMADBA CN_CMA004_PK 0 1 1050 ANALYZED


    SQL> select count(*) FROM TB_CMA006_CUSTOMER_ADDRESSES;

    COUNT(*)
    ----------
    5191531

    SQL> select count(*) FROM TB_CMA040_CUSTOMER_ADDRESS_USE;

    COUNT(*)
    ----------
    10166747

    SQL> select count(*) FROM TB_CMA086_US_CITY;

    COUNT(*)
    ----------
    79440

    SQL> select count(*) FROM TB_CMA085_US_COUNTY;

    COUNT(*)
    ----------
    3282

    SQL> select count(*) FROM TB_CMA004_US_STATE;

    COUNT(*)
    ----------
    60


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=15
    1)

    1 0 HASH (UNIQUE) (Cost=11 Card=1 Bytes=151)
    2 1 NESTED LOOPS (Cost=10 Card=1 Bytes=151)
    3 2 NESTED LOOPS (Cost=9 Card=1 Bytes=139)
    4 3 NESTED LOOPS (Cost=8 Card=1 Bytes=116)
    5 4 NESTED LOOPS (Cost=7 Card=1 Bytes=86)
    6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CMA040_CUST OMER_ADDRESS_USE' (TABLE) (Cost=5 Card=1 Bytes=24)

    7 6 INDEX (RANGE SCAN) OF 'CI_CMA040_ID1' (INDEX) (Cost=3 Card=3)

    8 5 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CMA006_CUSTOMER_ADDRESSES' (TABLE) (Cost=2 Card=1 Bytes=62)

    9 8 INDEX (UNIQUE SCAN) OF 'CN_CMA006_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

    10 4 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CMA086_US_CITY' (TABLE) (Cost=1 Card=1 Bytes=30)

    11 10 INDEX (UNIQUE SCAN) OF 'CN_CMA086_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

    12 3 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CMA085_US_COUNTY' (TABLE) (Cost=1 Card=1 Bytes=23)

    13 12 INDEX (UNIQUE SCAN) OF 'CN_CMA085_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

    14 2 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CMA004_US_STATE' (TABLE) (Cost=1 Card=1 Bytes=12)

    15 14 INDEX (UNIQUE SCAN) OF 'CN_CMA004_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

    Statistics
    ----------------------------------------------------------
    22 recursive calls
    0 db block gets
    24 consistent gets
    0 physical reads
    0 redo size
    849 bytes sent via SQL*Net to client
    496 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    I tried with some advanced tuning techniques, but there is no use.
    Please help me out to tune this query...It is very urgent.

  2. #2
    Join Date
    Dec 2007
    Location
    India
    Posts
    5

    May I help You

    Hi ,
    why don't you create views for each table you are using and use those views for your query. The reason is instead of looking on to physical database which will take much time to allocate space in memory ,use logical views to write the SQL statements . This clue might help you in future . Try it to see whether the problem is sorted out .
    Regards ,
    Experts.

  3. #3
    Join Date
    Dec 2007
    Posts
    4

    Hi,

    Hi,

    I dont have privilages to create view. Otherwise I could have tried it. Could u please help me out to tune this query.

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    Example you gave makes it difficult to see the indexes on your tables and how they have been defined. The largest tables (TB_CMA040_CUSTOMER_ADDRESS_USE and TB_CMA006_CUSTOMER_ADDRESSES) should be the focal point for tuning.

    The only filter criteria that you use for these 2 large tables is

    ADDR.CUSTOMER_MASTER_NUM = '1603246' and
    ADDR_USE.SITE_USE_CD = 'BILL_TO' AND ADDR_USE.SITE_TYPE_PRIMARY_FLG = 'Y'

    so with info above, I would ensure I have a single column index on ADDR.CUSTOMER_MASTER_NUM and a concatenated index on (ADDR_USE.SITE_USE_CD , ADDR_USE.SITE_TYPE_PRIMARY_FLG )

    Since there are most likely very few distinct values for the SITE_USE_CD and the SITE_TYP_PRIMARY_FLG columns , I would consider the use of a BITMAP INDEX concatenated across these 2 columns -- although bitmap indexes don't do very well with tables that have heavy insert, delete, update activity --- This looks like a datawarehouse -- so if your doing this correctly , you would have partitioned the large tables and the data loaded into those tables should be via exchange partition.

    ALso - investigate forcing a hash join over the nested loops in your explain plan

    Brendan

Posting Permissions

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