RE: Query tuning help

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 7 Feb 2013 10:02:36 +0000
Message-ID: <CABe10sYUMWPfMrK8suhJk6EKmwLw8FMyvN-z4s9gKqEu_oBHBQ_at_mail.gmail.com>

Those look like good questions to me as well. The other thing that looks interesting to me are the date time fields. We know that the stats are giving some very bad cardinality estimates. What do the high/low values for the various data columns look like (and are those figures realistic). It looks like you are on a version where you can gather pending stats and investigate if

1. they help this query and
2. they don't ruin everything else at the same time

I'd gather pending stats (with dbms_stats.auto_sample_size to get the new NDV calculations) and then test both a) and b) above (I'll admit that if the stats are just horrendously unrepresentative I might skip the pending stats bit). .

On Feb 7, 2013 5:56 AM, "Iggy Fernandez" <iggy_fernandez_at_hotmail.com> wrote:

>
> 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 --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 07 2013 - 11:02:36 CET