RE: Query tuning help
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: <lambu999@xxxxxxxxx>
- Date: Fri, 8 Feb 2013 04:32:22 -0800
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
