Re: SQL tuning help
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: lambu999@xxxxxxxxx
- Date: Fri, 19 Mar 2010 22:14:14 +0100
Look at the input you provide : a range of dates for I, and a DEPTID for
E (I assume the SETID for B is not very selective).
What Ken told you about using an analytical function is very sensible.
Remove everything from your query but I, E, G, and any table that is
MANDATORY to join them together. Try to write a query that returns a
number of rows as close as you can to the number of rows you expect.
Once you have this, join the other tables. Everything should fall in
place pretty easily.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Ram K wrote:
>
> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
