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.