RE: SQL tuning help

From: Kenneth Naim <kennethnaim_at_gmail.com>
Date: Fri, 19 Mar 2010 16:55:59 -0400
Message-ID: <03c001cac7a6$95865db0$c0931910$_at_com>

To properly tune that query, you'll need to provide a lot more information, starting with the full explain plan, how many rows it returns, how many rows are in each table, what indexes are available on each table etc. But I'd start by rewriting the query to use an analytical function to replace the self join of g to g_ed.  

Ken  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram K
Sent: Friday, March 19, 2010 4:41 PM
To: oracle-l
Subject: SQL tuning help    

Listers

Oracle 10.2. Can any of you help me with SQL tuning. The following SQL times out when run via reports. When run from SQL Plus it takes more than 10 minutes. The statistics are upto date.  

SELECT

A.PO_ID, TO_CHAR(A.PO_DT,'YYYY-MM-DD'), 
C.INV_ITEM_ID, C.DESCR254_MIXED, F.QTY_PO, C.UNIT_OF_MEASURE, 
E.MERCHANDISE_AMT,  E.MERCHANDISE_AMT/ F.QTY_PO, A.VENDOR_ID, B.NAME1,
C.ITM_ID_VNDR, C.MFG_ID, C.PRICE_CAN_CHANGE, C.MFG_ITM_ID, DECODE(
H.QTY_VCHR, 0,  H.MERCHANDISE_AMT,  H.MERCHANDISE_AMT/ H.QTY_VCHR), D.DESCR,
E.ACCOUNT, E.DEPTID, G.DESCR, H.QTY_VCHR, H.UNIT_OF_MEASURE,
H.MERCHANDISE_AMT, I.INVOICE_ID, TO_CHAR(I.INVOICE_DT,'YYYY-MM-DD'),

TO_CHAR(I.ACCOUNTING_DT,'YYYY-MM-DD'), TO_CHAR(I.ENTERED_DT,'YYYY-MM-DD'), I.MATCH_STATUS_VCHR,
H.INV_ITEM_ID,B.SETID,B.VENDOR_ID,G.SETID,G.DEPTID,TO_CHAR(G.EFFDT,'YYYY-MM- DD')
FROM

PS_PO_HDR A, 
PS_VENDOR B, 
PS_PO_LINE C, 
PS_MANUFACTURER D, 
PS_PO_LINE_DISTRIB E, 
PS_VOUCHER_LINE H, 
PS_VOUCHER I, 
PS_PO_LINE_SHIP F, 
PS_DEPT_TBL G 
  WHERE B.VENDOR_ID = A.VENDOR_ID 
     AND B.SETID = 'SHARE' 
     AND A.BUSINESS_UNIT = C.BUSINESS_UNIT 
     AND A.PO_ID = C.PO_ID 
     AND C.MFG_ID =  D.MFG_ID(+) 
     AND I.ENTERED_DT >= '2010-02-01' 
     AND I.ENTERED_DT <= '2010-02-28'
     AND C.BUSINESS_UNIT = E.BUSINESS_UNIT 
     AND C.PO_ID = E.PO_ID 
     AND C.LINE_NBR = E.LINE_NBR 
     AND C.BUSINESS_UNIT = H.BUSINESS_UNIT 
     AND C.PO_ID = H.PO_ID 
     AND C.LINE_NBR = H.LINE_NBR 
     AND H.BUSINESS_UNIT = I.BUSINESS_UNIT 
     AND H.VOUCHER_ID = I.VOUCHER_ID 
     AND C.BUSINESS_UNIT = F.BUSINESS_UNIT 
     AND C.PO_ID = F.PO_ID 
     AND C.LINE_NBR = F.LINE_NBR 
     AND G.DEPTID = E.DEPTID 
     AND G.EFFDT = 
        (SELECT MAX(G_ED.EFFDT) FROM PS_DEPT_TBL G_ED 
        WHERE G.SETID = G_ED.SETID 
          AND G.DEPTID = G_ED.DEPTID 
          AND G_ED.EFFDT <= SYSDATE) 
     AND E.DEPTID = '7141'

--

Thanks,
Ram.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 19 2010 - 15:55:59 CDT