RE: Query tuning help

  • From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
  • To: <ric.van.dyke@xxxxxxxxxx>, <lambu999@xxxxxxxxx>
  • Date: Wed, 6 Feb 2013 21:54:48 -0800

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?


-- Iggy                                           --
//www.freelists.org/webpage/oracle-l


Other related posts: