Oracle Database Discussions

Hi all,

We are using Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production on
Linux 2.6.5-7.282-bigsmp #1 SMP box.

The following query is taking aproximately 1 hr to execute.

SELECT item.ap_id, item.ap_version_id, oa.customer_id,CI.CAPTION OFFER_ID, ITEM.BILL_START_DATE , ITEM.CTDB_CRE_DATETIME , oa.order_unit_id
FROM tbap_item item,
tbprice_plan pp,
tborder_action oa,
tborder_action_impl oaimpl,
TBCATALOG_ITEM CI
WHERE item.entity_type = 'amdocs.oms.sbcproduct.ApPricePlanImpl'
AND item.item_def_id = pp.cid
AND item.item_def_ver = pp.pcversion_id
AND pp.is_promote = '1'
AND item.order_action_id = oa.order_unit_id
AND oa.order_unit_id = oaimpl.order_unit_id
AND oaimpl.bypass_reason_id = 'MI'
AND oa.xml_id IS NOT NULL
AND item.state IN ('AS', 'OR')
AND item.status = 'AC'
AND CI.CID = ITEM.ITEM_DEF_ID
AND CI.PCVERSION_ID = ITEM.ITEM_DEF_VER
AND item.ap_version_id = '0'
AND EXISTS (SELECT 1 FROM TBAP_ITEM TEMP WHERE TEMP.AP_ID = ITEM.AP_ID AND TEMP.END_DATE > SYSDATE AND TEMP.STATE IN ('AS' , 'OR') AND TEMP.STATUS ='AC');

the execution plan before collecting the stats is:

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=120002
NESTED LOOPS SEMI Cost=120002 Card=98407 Bytes=18402109
MERGE JOIN Cost=80639 Card=98407 Bytes=16040341
SORT JOIN Cost=80607 Card=98407 Bytes=13481759
MERGE JOIN Cost=78626 Card=98407 Bytes=13481759
SORT JOIN Cost=76699 Card=119937 Bytes=14992125
MERGE JOIN Cost=76699 Card=119937 Bytes=14992125
SORT JOIN Cost=76682 Card=171602 Bytes=19219424
MERGE JOIN Cost=76682 Card=171602 Bytes=19219424
SORT JOIN Cost=74698 Card=990661 Bytes=82224863
TABLE ACCESS FULL TBAP_ITEM [ANALYZED] Cost=62069 Card=990661 Bytes=82224863

SORT JOIN Cost=1985 Card=89365 Bytes=2591585
TABLE ACCESS FULL TBORDER_ACTION [ANALYZED] Cost=1511 Card=89365 Bytes=2591585

SORT JOIN Cost=17 Card=195 Bytes=2535
TABLE ACCESS FULL TBPRICE_PLAN [ANALYZED] Cost=2 Card=195 Bytes=2535


SORT JOIN Cost=1928 Card=73323 Bytes=879876
TABLE ACCESS FULL TBORDER_ACTION_IMPL [ANALYZED] Cost=1698 Card=73323 Bytes=879876

SORT JOIN Cost=33 Card=3017 Bytes=78442
TABLE ACCESS FULL TBCATALOG_ITEM [ANALYZED] Cost=4 Card=3017 Bytes=78442

TABLE ACCESS BY INDEX ROWID TBAP_ITEM [ANALYZED] Cost=1 Card=2037512 Bytes=48900288
INDEX RANGE SCAN TBAP_ITEM_PK [ANALYZED] Cost=2 Card=2 Bytes=

after collecting the stats:
SELECT STATEMENT [CHOOSE] Cost=64972
NESTED LOOPS SEMI Cost=64972 Card=333 Bytes=62937
NESTED LOOPS Cost=64839 Card=333 Bytes=54945
MERGE JOIN Cost=64446 Card=1965 Bytes=300645
SORT JOIN Cost=64414 Card=1965 Bytes=249555
MERGE JOIN Cost=64414 Card=1965 Bytes=249555
SORT JOIN Cost=62429 Card=13393 Bytes=1312514
MERGE JOIN Cost=62429 Card=13393 Bytes=1312514
SORT JOIN Cost=2 Card=140 Bytes=1820
TABLE ACCESS FULL TBPRICE_PLAN [ANALYZED] Cost=2 Card=140 Bytes=1820

SORT JOIN Cost=62428 Card=26785 Bytes=2276725
TABLE ACCESS FULL TBAP_ITEM [ANALYZED] Cost=62072 Card=26785 Bytes=2276725

SORT JOIN Cost=1985 Card=89365 Bytes=2591585
TABLE ACCESS FULL TBORDER_ACTION [ANALYZED] Cost=1511 Card=89365 Bytes=2591585
SORT JOIN Cost=33 Card=3017 Bytes=78442
TABLE ACCESS FULL TBCATALOG_ITEM [ANALYZED] Cost=4 Card=3017 Bytes=78442
TABLE ACCESS BY INDEX ROWID TBORDER_ACTION_IMPL [ANALYZED] Cost=1 Card=1 Bytes=12
INDEX UNIQUE SCAN TBORDER_ACTION_IMPL_PK [ANALYZED] Cost=1 Card=1 Bytes=
TABLE ACCESS BY INDEX ROWID TBAP_ITEM [ANALYZED] Cost=1 Card=2972308 Bytes=71335392

INDEX RANGE SCAN TBAP_ITEM_PK [ANALYZED] Cost=2 Card=2 Bytes=

I am now taking the level 4 trace now.will post it once Its done. I found some "latchfree" timeouts while running it now...

I also find that few cols refered in where clause doesn't have indexes...So I am going to discuss this with the developers. also I want modify the query too..as below.

ELECT item.ap_id, item.ap_version_id, oa.customer_id,CI.CAPTION OFFER_ID, ITEM.BILL_START_DATE , ITEM.CTDB_CRE_DATETIME , oa.order_unit_id
FROM tbap_item item,
tbprice_plan pp,
tborder_action oa,
tborder_action_impl oaimpl,
TBCATALOG_ITEM CI
WHERE
item.entity_type = 'amdocs.oms.sbcproduct.ApPricePlanImpl'
AND item.status = 'AC'
AND item.state IN ('AS', 'OR')
AND item.ap_version_id = '0'
AND pp.is_promote = '1'
AND oaimpl.bypass_reason_id = 'MI'
AND oa.xml_id IS NOT NULL
AND item.item_def_id = pp.cid
AND item.item_def_ver = pp.pcversion_id
AND item.order_action_id = oa.order_unit_id
AND oa.order_unit_id = oaimpl.order_unit_id
AND CI.CID = ITEM.ITEM_DEF_ID
AND CI.PCVERSION_ID = ITEM.ITEM_DEF_VER
AND EXISTS (SELECT 1 FROM TBAP_ITEM TEMP WHERE
TEMP.STATE IN ('AS' , 'OR') AND TEMP.STATUS ='AC' AND TEMP.AP_ID = ITEM.AP_ID AND TEMP.END_DATE > SYSDATE );

Please suggest. what else I need to check to imporve the performace .

Thanks in advace for your help and suggestions.