OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » TUNING

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TUNING [message #236086]

Wed, 09 May 2007 00:36

jayarago
Messages: 12
Registered: December 2006
Location: Chennai

Junior Member

Hi ,

I am working in oracle 9i and linux 2.4.

I am sending a query along with Explain plan.

The query takes 60 secs to fetch the result..

Query...

SELECT SFG_MDL.SFG_MODEL_NO MODEL,
SFG_MDL.SFG_MODEL_DESCRIPTION DESCRIP,
CMSF_POHD.PO_QUANTITY PO_QUANTITY,
SFG_MDL.SFG_CONTAINER_NO CONT_NO,
SFG_MDL.SFG_NO_MODELS_IN_CONTAINER INV_QTY,
SFG_MDL.SFG_INVOICE_NO INV_NO,
SFG_CONT.GE_FINAL_DEST,
SFG_CONT.GE_FINAL_DEST GE_FINAL_DEST,
NET.TRANSLEADTIME,
CMSF_PO.INVOICE_QUANTITY,
SFG_MDL.NO_MODELS_IN_CONTAINER_UNEXP UNEXPL_QTY_SHIP,
TO_CHAR(SFG_CONT.SFG_DESTINATION_PORT_ETA,'MM/DD/YYYY') SFG_DESTINATION_PORT_ETA,
SFG_CONT.GE_FINAL_DEST GE_FINAL_DEST,
TO_CHAR(SFG_CONT.SFG_DESTINATION_PORT_ETA + ((NET.TRANSLEADTIME + NET.LOADTIME) / 1440),'MM/DD/YYYY') ETATOGEDEST,
TO_CHAR(CMSF_POHD.PO_DUE_DATE,'MM/DD/YYYY') PO_DUE_DATE,
SUPP.SUPPLIER_NAME SUPPLIER_NAME,
CMSF_PO.PO_NO PO_NO,
SFG_CONT.LOT_NO LOT_NO,
CMSF_PO.INVOICE_AMOUNT_BILLED AMOUNT_BILLED,
SFG_MDL.SFG_INVOICE_NO SFG_INV_NO,
'SEA' TRNS_MODE,
SFG_CONT.FF_NAME FF_NAME,
SFG_CARR.SFG_VESSEL_NAME VESSEL_NAME,
SFG_CARR.SFG_OCEAN_CARRIER_NAME OCEAN_CARRIER_NAME
FROM
TF.TF_SFG_CONTAIN_MDL SFG_MDL,
TF.TF_SFG_CONTAINER SFG_CONT,
STSC.NETWORK NET,
TF.TF_SFG_CARRIER_VES SFG_CARR,
TF.TF_CMSF_PORHD CMSF_POHD,
TF.TF_CMSF_PO CMSF_PO,
TF.TF_CMSF_SUPPLIER SUPP
WHERE SFG_MDL.SFG_CONTAINER_NO = SFG_CONT.SFG_CONTAINER_NO
AND SFG_MDL.LOT_NO = SFG_CONT.LOT_NO
AND SFG_MDL.SFG_INVOICE_NO = SFG_CONT.SFG_INVOICE_NO
AND SFG_MDL.SFG_SUPPLIER_CODE = SFG_CONT.SFG_SUPPLIER_CODE
AND NET.TRANSMODE(+) = 'TRUCK'
AND NET.SOURCE(+) = TO_NUMBER(SFG_CONT.US_PORT_CODE)
AND NET.DEST(+) = TO_NUMBER(SFG_CONT.GE_FINAL_DEST)
AND SFG_CONT.SFG_VESSEL_NO = SFG_CARR.SFG_VESSEL_NO
AND SFG_CONT.SFG_OCEAN_CARRIER_CODE = SFG_CARR.SFG_OCEAN_CARRIER_CODE
AND CMSF_POHD.PO_NO = SFG_MDL.SFG_PO_NO
AND CMSF_PO.SUPPLIER_CODE=CMSF_POHD.SUPPLIER_CODE
AND CMSF_PO.INVOICE_NO = SFG_MDL.SFG_INVOICE_NO
AND CMSF_POHD.PO_NO = CMSF_PO.PO_NO
AND CMSF_PO.MODEL_NO=SFG_MDL.SFG_MODEL_NO
AND SUPP.SUPPLIER_CODE = CMSF_PO.SUPPLIER_CODE
AND (SFG_CONT.SFG_DESTINATION_PORT_ETA >= TO_DATE('3/2/2007','MM/DD/YYYY HH:MI:SS')
AND SFG_CONT.SFG_DESTINATION_PORT_ETA <= TO_DATE('5/2/2007','MM/DD/YYYY HH:MI:SS'))
AND SFG_MDL.SFG_MODEL_NO = '00010044'
UNION
SELECT SFG_MDL.SFG_MODEL_NO MODEL,
SFG_MDL.SFG_MODEL_DESCRIPTION DESCRIP,
CMSF_POHD.PO_QUANTITY PO_QUANTITY,
SFG_MDL.SFG_CONTAINER_NO CONT_NO,
SFG_MDL.SFG_NO_MODELS_IN_CONTAINER INV_QTY,
SFG_MDL.SFG_INVOICE_NO INV_NO,
SFG_CONT.GE_FINAL_DEST,
SFG_CONT.GE_FINAL_DEST GE_FINAL_DEST,
NET.TRANSLEADTIME,
CMSF_PO.INVOICE_QUANTITY,
SFG_MDL.NO_MODELS_IN_CONTAINER_UNEXP UNEXPL_QTY_SHIP,
TO_CHAR(SFG_CONT.SFG_DESTINATION_PORT_ETA,'MM/DD/YYYY') SFG_DESTINATION_PORT_ETA,
SFG_CONT.GE_FINAL_DEST GE_FINAL_DEST,
TO_CHAR(SFG_CONT.SFG_DESTINATION_PORT_ETA + ((NET.TRANSLEADTIME + NET.LOADTIME) / 1440),'MM/DD/YYYY') ETATOGEDEST,
TO_CHAR(CMSF_POHD.PO_DUE_DATE,'MM/DD/YYYY') PO_DUE_DATE,
SUPP.SUPPLIER_NAME SUPPLIER_NAME,
CMSF_POHD.PO_NO PO_NO,
SFG_CONT.LOT_NO LOT_NO,
CMSF_PO.INVOICE_AMOUNT_BILLED AMOUNT_BILLED,
SFG_MDL.SFG_INVOICE_NO SFG_INV_NO,
'SEA' TRNS_MODE,
SFG_CONT.FF_NAME FF_NAME,
SFG_CARR.SFG_VESSEL_NAME VESSEL_NAME,
SFG_CARR.SFG_OCEAN_CARRIER_NAME OCEAN_CARRIER_NAME
FROM
TF.TF_SFG_CONTAIN_MDL SFG_MDL,
TF.TF_SFG_CONTAINER SFG_CONT,
STSC.NETWORK NET,
TF.TF_SFG_CARRIER_VES SFG_CARR,
TF.TF_CMSF_PORHD CMSF_POHD,
TF.TF_CMSF_PO CMSF_PO,
TF.TF_CMSF_SUPPLIER SUPP
WHERE SFG_MDL.SFG_CONTAINER_NO = SFG_CONT.SFG_CONTAINER_NO
AND SFG_MDL.LOT_NO = SFG_CONT.LOT_NO
AND SFG_MDL.SFG_INVOICE_NO = SFG_CONT.SFG_INVOICE_NO
AND SFG_MDL.SFG_SUPPLIER_CODE = SFG_CONT.SFG_SUPPLIER_CODE
AND NET.TRANSMODE(+) = 'TRUCK'
AND NET.SOURCE(+) = TO_NUMBER(SFG_CONT.US_PORT_CODE)
AND NET.DEST(+) = TO_NUMBER(SFG_CONT.GE_FINAL_DEST)
AND SFG_CONT.SFG_VESSEL_NO = SFG_CARR.SFG_VESSEL_NO
AND SFG_CONT.SFG_OCEAN_CARRIER_CODE = SFG_CARR.SFG_OCEAN_CARRIER_CODE
AND CMSF_POHD.PO_NO = SFG_MDL.SFG_PO_NO
AND CMSF_PO.INVOICE_NO(+) = SFG_MDL.SFG_INVOICE_NO
AND CMSF_PO.MODEL_NO(+)=SFG_MDL.SFG_MODEL_NO
AND SUPP.SUPPLIER_CODE = CMSF_POHD.SUPPLIER_CODE
AND CMSF_POHD.SUPPLIER_CODE = SFG_MDL.SFG_SUPPLIER_CODE
AND SFG_MDL.SFG_SUPPLIER_CODE LIKE 'EURAA%'
AND (SFG_CONT.SFG_DESTINATION_PORT_ETA >= TO_DATE('5/2/2007','MM/DD/YYYY HH:MI:SS')
AND SFG_CONT.SFG_DESTINATION_PORT_ETA <= TO_DATE('5/2/2008','MM/DD/YYYY HH:MI:SS'))
AND SFG_MDL.SFG_MODEL_NO = '00010044'

EXPLAIN PLAN

query plan
--------------------------------------------------------------------------------
SELECT STATEMENT cost = 296
UNION-ALL
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL T101433
TABLE ACCESS BY INDEX ROWID T101431
INDEX RANGE SCAN X1014311
TABLE ACCESS BY INDEX ROWID T101436
INDEX RANGE SCAN X1014361
TABLE ACCESS FULL T101460
TABLE ACCESS BY INDEX ROWID T101462
INDEX UNIQUE SCAN X1014621
TABLE ACCESS BY INDEX ROWID T101478
INDEX UNIQUE SCAN X1014781
TABLE ACCESS FULL SNAP$_NETWORK
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL T101436
TABLE ACCESS BY INDEX ROWID T101433
INDEX UNIQUE SCAN X1014331
TABLE ACCESS BY INDEX ROWID T101431
INDEX RANGE SCAN X1014311
TABLE ACCESS FULL T101462
TABLE ACCESS BY INDEX ROWID T101460
INDEX UNIQUE SCAN X1014601
TABLE ACCESS BY INDEX ROWID T101478
INDEX UNIQUE SCAN X1014781
TABLE ACCESS FULL SNAP$_NETWORK

Please provide me the solution to increase the performance

Report message to a moderator