RE: Query tuning help

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Wed, 6 Feb 2013 21:54:48 -0800
Message-ID: <BLU165-W462299781668B25FD2BAADEB060_at_phx.gbl>


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?

Received on Thu Feb 07 2013 - 06:54:48 CET

Original text of this message