Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Tuning

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

    I have following statement, where USAGE_RECORD is VIEW.

    select call_id ,record_id ,pers_product_id ,product_node_id ,amount, charge_traits_id ,other_party ,volume ,unit_id ,call_stamp ,
    assigned ,remark ,other_country ,rate_code
    from USAGE_RECORD
    where ((contract_id=:b1 and assigned>=:b2)
    and assigned<:b3)
    order by call_id,record_id;

    The original Source of View is :

    CREATE OR REPLACE VIEW USAGE_RECORD ( CALL_ID,
    RECORD_ID, CONTRACT_ID, PERS_PRODUCT_ID, PRODUCT_NODE_ID,
    CHARGE_TRAITS_ID, OTHER_PARTY, AMOUNT, VOLUME,
    UNIT_ID, CALL_STAMP, ASSIGNED, REMARK,
    OTHER_COUNTRY, RATE_CODE ) AS
    select cal.calllog_id CALL_ID, 1 RECORD_ID, am2.ASSOCIATE_ID CONTRACT_ID,
    am1.associate_id PERS_PRODUCT_ID, 2 PRODUCT_NODE_ID,
    1 CHARGE_TRAITS_ID, cal.DNIS OTHER_PARTY, CALL_CHARGE AMOUNT,
    CALL_DURATION VOLUME, 1 UNIT_ID, cal.START_TIME CALL_STAMP,
    RECORD_TIME ASSIGNED, 'OSB' Remark, con1.COUNTRY_NAME OTHER_COUNTRY,
    calc.RATE_CODE
    from ASSOCIATE_MAP am1, PPMS5_SSP1.CALLLOG cal,
    PPMS5_SSP1.CALLLOG_CALC calc, ASSOCIATE_MAP am2,
    PPMS5_SSP1.CODE_TRANSLATION ct1, PPMS5_SSP1.COUNTRY con1
    where am1.PPMS_ID = cal.user_no and
    am1.PPMS_TYPE = 'u' and
    am1.SCHEMA_ID = 'SCHEMA_NAME' and
    calc.cust_id = am2.ppms_id and
    am2.ppms_type = 'c' and
    am2.SCHEMA_ID = am1.schema_id and
    calc.CALLLOG_ID = cal.CALLLOG_ID and
    cal.DIRECTION = 'O' and
    calc.ANI_TRANSLATION_CODE = ct1.TRANSLATION_CODE and
    ct1.COUNTRY_CODE = con1.COUNTRY_CODE
    with read only;

    When run the below query it is running very slow. Here is the explain results. Can you Please tell me How to tune this statement.

    select call_id ,record_id ,pers_product_id ,product_node_id ,amount, charge_traits_id ,other_party ,volume ,unit_id ,call_stamp ,
    assigned ,remark ,other_country ,rate_code
    from USAGE_RECORD
    where ((contract_id=:b1 and assigned>=:b2)
    and assigned<:b3)
    order by call_id,record_id;


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.01 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 29.17 29.28 407250 1608186 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 29.18 29.28 407250 1608186 0 0

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 45 (OSB)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 SORT ORDER BY
    0 NESTED LOOPS
    1 NESTED LOOPS
    1 NESTED LOOPS
    323942 NESTED LOOPS
    5846 NESTED LOOPS
    2 TABLE ACCESS BY INDEX ROWID ASSOCIATE_MAP
    2 INDEX UNIQUE SCAN (object id 8037)
    5846 TABLE ACCESS BY INDEX ROWID ASSOCIATE_MAP
    5846 INDEX RANGE SCAN (object id 8039)
    329786 TABLE ACCESS BY INDEX ROWID CALLLOG
    329786 INDEX RANGE SCAN (object id 8167)
    323941 TABLE ACCESS BY INDEX ROWID CALLLOG_CALC
    647882 INDEX UNIQUE SCAN (object id 8578)
    0 TABLE ACCESS BY INDEX ROWID CODE_TRANSLATION
    0 INDEX RANGE SCAN (object id 8182)
    0 TABLE ACCESS BY INDEX ROWID COUNTRY
    0 INDEX UNIQUE SCAN (object id 8488)


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    0 SORT (ORDER BY)
    0 NESTED LOOPS
    1 NESTED LOOPS
    1 NESTED LOOPS
    323942 NESTED LOOPS
    5846 NESTED LOOPS
    2 TABLE ACCESS (BY INDEX ROWID) OF 'ASSOCIATE_MAP'
    2 INDEX (UNIQUE SCAN) OF 'PK_ASSOCIATE_MAP' (UNIQUE)

    5846 TABLE ACCESS (BY INDEX ROWID) OF 'ASSOCIATE_MAP'
    5846 INDEX (RANGE SCAN) OF 'UK_ASSOCIATE_MAP' (UNIQUE)
    329786 TABLE ACCESS (BY INDEX ROWID) OF 'CALLLOG'
    329786 INDEX (RANGE SCAN) OF 'IDX_CALLLOG2' (NON-UNIQUE)
    323941 TABLE ACCESS (BY INDEX ROWID) OF 'CALLLOG_CALC'
    647882 INDEX (UNIQUE SCAN) OF 'PK_CALLLOG_CALC' (UNIQUE)
    0 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_TRANSLATION'
    0 INDEX (RANGE SCAN) OF 'PK_TRANSLATION_CODE' (NON-UNIQUE)

    0 TABLE ACCESS (BY INDEX ROWID) OF 'COUNTRY'
    0 INDEX (UNIQUE SCAN) OF 'PK_COUNTRY' (UNIQUE)

    What is exactly here nested loops.

    Please guide me

    Thanks In Advance.


    Nagesh

  2. #2
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi All,

    Problem Solved. One Index missed. Now working fine.


    Thanks & Regards
    Nagesh

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