Home » RDBMS Server » Performance Tuning » TUNING
TUNING [message #236086] Wed, 09 May 2007 00:36 Go to next message
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 #236110 is a reply to message #236086] Wed, 09 May 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Look at your post, do you think we can read it?
Read Performance Tuning Sticky and How to format your posts.

Regards
Michel
Re: TUNING [message #236246 is a reply to message #236086] Wed, 09 May 2007 07:08 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #236256 is a reply to message #236246] Wed, 09 May 2007 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't even read his post.
I already know that I can't understand anything without a good formatting.

Regards
Michel
Re: TUNING [message #236314 is a reply to message #236086] Wed, 09 May 2007 09:58 Go to previous message
pablolee
Messages: 2835
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


Previous Topic: Re-creating partitioned tablespaces from backup.
Next Topic: Long Running SQL -V$session_longops
Goto Forum:
  


Current Time: Thu Dec 08 12:25:41 CST 2016

Total time taken to generate the page: 0.15043 seconds