Home » RDBMS Server » Performance Tuning » TUNING
TUNING [message #236086] |
Wed, 09 May 2007 00:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: TUNING [message #236246 is a reply to message #236086] |
Wed, 09 May 2007 07:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
And do you really think that we can understand it without knowing view <-> table correspondance and index definitions?
Michael
|
|
|
|
Re: TUNING [message #236314 is a reply to message #236086] |
Wed, 09 May 2007 09:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote: | Please provide me the solution to increase the performance
|
Try
ALTER SESSION SET DB_FAST_QUERY = TRUE
|
|
|
Goto Forum:
Current Time: Wed Aug 21 05:47:15 CDT 2024
|