RE: Query tuning help

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?

                                               --
http://www.freelists.org/webpage/oracle-l Received on Fri Feb 08 2013 - 13:32:22 CET