Re: Query tuning help
- From: Ram K <lambu999@xxxxxxxxx>
- To: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>, Niall Litchfield <niall.litchfield@xxxxxxxxx>, Ric Van Dyke <Ric.Van.Dyke@xxxxxxxxxx>
- Date: Fri, 8 Feb 2013 11:14:55 -0600
Thanks Iggy, Ric and Niall and others who replied. I got busy with other
production emergency work, I am going to test it today soon.
On Fri, Feb 8, 2013 at 6:32 AM, Iggy Fernandez
<iggy_fernandez@xxxxxxxxxxx>wrote:
>
> Ram,
>
> Any update on tuning this SQL statement?
>
> -- Iggy
>
>
> > In the query plan that was posted by Ram, 42 minutes are spent to
> retrieve 133,000 rows from WFCTOTAL and then all but one are thrown away by
> joining to PAYCODEIMMFLAT.
> >
> > Based on the rowsource statistics in the query plan that was posted,
> here's what I think the query is trying to do:
> >
> > SELECT [fields] FROM TKCSOWNER.PAYCODE A2 -- a unique row is retrieved
> >
> > INNER JOIN TKCSOWNER.PAYCODE1MMFLAT A3 ON (A3.GRANDPAYCODEID =
> A2.PAYCODEID) -- 1:1 FK join
> > INNER JOIN TKCSOWNER.WFCTOTAL A7 ON (A7.PAYCODEID = A3.PAYCODEID) -- 1:N
> FK join; most records are filtered out
> > INNER JOIN TKCSOWNER.WTKEMPLOYEE A6 ON (A6.EMPLOYEEID = A7.EMPLOYEEID)
> -- 1:1 FK join
> > INNER JOIN TKCSOWNER.MYPAYPERIOD A5 ON (A5.PAYRULEID = A6.PAYRULEID ) --
> 1:1 FK join
> > INNER JOIN TKCSOWNER.LABORACCT A4 ON (A4.LABORACCTID = A7.LABORACCTID)
> -- 1:1 FK join
> > INNER JOIN TKCSOWNER.PERSON A1 ON (A1.PERSONID = A6.PERSONID) -- 1:1 FK
> join
> >
> > WHERE A2.TYPE ='P' AND A2.NAME ='STIIP-INDICATOR' -- a unique row is
> retrieved
> > AND A7.NOTPAIDSW = 0 AND A7.APPLYDTM >= A3.EFFECTIVEDTM AND A7.APPLYDTM
> < A3.EXPIRATIONDTM -- very important filter
> > AND A5.PPSTARTDATEDTM <= A7.APPLYDTM AND A5.PPENDDATEDTM >= A7.APPLYDTM
> >
> > Based on this analysis, the right join order is A2, A3, A7, A6, A5, A4,
> and A1.
> >
> > The question is how to influence Oracle into choosing this plan. Ram
> could use hint injection to inject a LEADING hint because this is a query
> from a canned application called Kronos that he cannot modify.
> >
> > I have to question whether Oracle is getting the right information about
> primary keys and foreign keys. For example:
> >
> > Is PAYCODEID the primary key of PAYCODE?
> > Is PAYCODE1MMFLAT(GRANDCODEID) a foreign key to PAYCODE and supported by
> an index?
> > Is WFCTOTAL(PAYCODEID) a foreign key to PAYCODE1MMFLAT and supported by
> an index?
>
>
--
Thanks,
Ram.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
