Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help interpreting TKPROF output

Re: Help interpreting TKPROF output

From: Scott Watson <nospam_at_hotmail.com>
Date: Sun, 9 Nov 2003 14:00:04 -0500
Message-ID: <o5wrb.7497$861.213889@weber.videotron.net>


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')
                                 )
                        )

    ) 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)

 )

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

   -> t7
   -> t19

      -> 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...
>
> jan
Received on Sun Nov 09 2003 - 13:00:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US