Tuning

[gnagesh]

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.

[gnagesh]

Hi All,

Problem Solved. One Index missed. Now working fine.


Thanks & Regards