RE: SQL tuning help

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <kennethnaim@xxxxxxxxx>, <lambu999@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 19 Mar 2010 17:42:58 -0400

What Kenneth said, and "are you kiddin' me?"  I'm betting entered_dt is a
date field. Please give the optimizer a layup (hey, the NCAA trournament is
on) by doing the to_date function on your literals.

 

Is deptid actually a varchar? If it is numeric you probably want 7141 not
'7141', and you might profit from joining e and g in the where clause as
inline views, since you only want rows for one deptid with those two tables
matching and for a specific maximum date at that.

 

Sorry. It's seventy degrees (F) in NH and we decided it is summer to the gin
and tonic's have arrived. Good luck!

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kenneth Naim
Sent: Friday, March 19, 2010 4:56 PM
To: lambu999@xxxxxxxxx; 'oracle-l'
Subject: RE: SQL tuning help

 

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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.

Other related posts: