Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help interpreting TKPROF output
Dan,
Having been a victim to some of SIEBELs queries in the past I can understand your frustration and realize throwing it out the window is an unrealistic solution given this is a 3rd party application.
I tried to reformat the query so I could read it a bit easiser but it seems like maybe a ) is missing or I mis-formatted the query in any case I think my comments are still relevant.
WHERE
T3.CURR_STG_ID = T8.ROW_ID (+) AND T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND T1.ASSET_ID = T23.ROW_ID (+) AND T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND T1.OPTY_ID = T3.ROW_ID (+) AND T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND T1.PROJ_ID = T9.ROW_ID (+) AND T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND T1.TEMPLATE_ID = T10.ROW_ID (+) AND T1.OPTY_ID = T11.PAR_ROW_ID (+) AND T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND T1.TARGET_PER_ID = T19.ROW_ID (+) AND T19.PREF_LANG_ID = T25.ROW_ID (+) AND T23.PROD_ID = T17.ROW_ID (+) AND T1.ROW_ID = T22.PAR_ROW_ID (+) AND T1.ROW_ID = T21.PAR_ROW_ID (+) AND T1.ROW_ID = T18.PAR_ROW_ID (+) AND T1.OWNER_PER_ID = T5.EMP_ID (+) AND T1.ROW_ID = T5.ACTIVITY_ID (+) AND T5.EMP_ID = T12.ROW_ID (+) AND T5.EMP_ID = T24.PAR_ROW_ID (+) AND T1.PR_PRDINT_ID = T16.ROW_ID (+) AND T16.PRDINT_ID = T15.ROW_ID (+) AND T13.BU_ID = T6.SUB_PARTY_ID (+) AND(
( T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL ) AND (T1.OPTY_ID IS NULL OR T3.SECURE_FLG = 'N' OR T1.OPTY_ID IN ( SELECT SQ1_T1.OPTY_ID FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY SQ1_T2, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN SQ1_T4 WHERE ( SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) AND SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID AND SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID) AND (SQ1_T3.ROW_ID = '1-29WRP') ) )
Joins
t1 -> t13
-> t6 ( problem here t6.party_id = '1-2J-5235' but it could be null from outer join )
-> t20 (this is done twice)
-> t23
-> t17
-> t14
-> t3 ( problem here T3.SECURE_FLG = 'N' but it could be null therefore outer join is a waste ) -> t8 -> t2 -> t9 -> t4 -> t10 -> t11
-> t25
-> t22 -> t21 -> t18 -> t5 ( owner_per_id, row_id -> emp_id, activity_id) -> t12 -> t24 -> t16 -> t15
According to your predicates I think the join order should be
t1 -> t6
-> t13 -> t3
and you should remove the outer joins from on the tables t6 and t13 and t3. If you really require these tables to be outer joined then you need to rewrite your query such that the predicates include
where ....
OR (T3.SECURE_FLG = 'N' or t3.SECURE_FLG is null)
...
((T6.PARTY_ID = '1-2J-5235') or (T6.PARTY_ID is null ))
otherwise your query is not constructed properly.
you would try to add tables to the mix in such a way that you are adding the least number of rows with the addition of each table.
SubQuery
There is no reason to outer join sq1_t4 to sq1_t3 since you are asking for SQ1_T3.ROW_ID = '1-29WRP' When you outer join you are asking oracle to make up a row for you if it is not found. However, you are then turning around and saying for the row you just made up for me check if the value is 1-29WRP which it won't be so Oracle will throw out the row and you will be left with a regular join but did the work of an outer join.
Also by removing the outer join the subquery will be able to drive off of the SQ1_t3 table possible making this subquery access fewer rows. Personally, I would try using an exists clause as I think that might work better in this case.
OR T1.OPTY_ID IN ( SELECT SQ1_T1.OPTY_ID FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY SQ1_T2, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN SQ1_T4 WHERE ( SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) AND SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID AND SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID) AND (SQ1_T3.ROW_ID = '1-29WRP') ) )
Hope that helps
Scott Watson.
"Jan van Mourik" <jmourik_at_yahoo.com> wrote in message news:ffe966de.0311081000.6c466847_at_posting.google.com...
> This is one BAD, BAD query! Doesn't make sense to me. Why all the > outer joins? I tried to format it a bit: > > <snip> > WHERE > T3.CURR_STG_ID = T8.ROW_ID (+) AND > T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND > T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND > T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND > T1.ASSET_ID = T23.ROW_ID (+) AND > T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND > T1.OPTY_ID = T3.ROW_ID (+) AND > T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND > T1.PROJ_ID = T9.ROW_ID (+) AND > T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND > T1.TEMPLATE_ID = T10.ROW_ID (+) AND > T1.OPTY_ID = T11.PAR_ROW_ID (+) AND > T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND > T1.TARGET_PER_ID = T19.ROW_ID (+) AND > T19.PREF_LANG_ID = T25.ROW_ID (+) AND > T23.PROD_ID = T17.ROW_ID (+) AND > T1.ROW_ID = T22.PAR_ROW_ID (+) AND > T1.ROW_ID = T21.PAR_ROW_ID (+) AND > T1.ROW_ID = T18.PAR_ROW_ID (+) AND > T1.OWNER_PER_ID = T5.EMP_ID (+) AND > T1.ROW_ID = T5.ACTIVITY_ID (+) AND > T5.EMP_ID = T12.ROW_ID (+) AND > T5.EMP_ID = T24.PAR_ROW_ID (+) AND > T1.PR_PRDINT_ID = T16.ROW_ID (+) AND > T16.PRDINT_ID = T15.ROW_ID (+) AND > T13.BU_ID = T6.SUB_PARTY_ID (+) AND > ( ( (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR > T1.TEMPLATE_FLG IS NULL) > AND > (T1.OPTY_ID IS NULL > OR T3.SECURE_FLG = 'N' > OR T1.OPTY_ID > IN (SELECT SQ1_T1.OPTY_ID > FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY > SQ1_T2 > , SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN > SQ1_T4 > WHERE (SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) > AND SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID > AND SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID > ) > AND (SQ1_T3.ROW_ID = '1-29WRP') > ) > ) > ) > AND (T6.PARTY_ID = '1-2J-5235') > AND (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID = > '1-2L-5407') > AND (T1.APPT_REPT_REPL_CD IS NULL) > ) > > T1 outer joins to T13 > T13 outer joins T6 > but then (T6.PARTY_ID = '1-2J-5235') > Why the outer joins if it will be tossed out anyway if null? > > What kind of indexes are there on SIEBEL.S_EVT_ACT T1? > You sure some indexes haven't been dropped? > > I almost can't believe this query EVER was fast... > > janReceived on Sun Nov 09 2003 - 13:00:04 CST