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: Jan van Mourik <jmourik_at_yahoo.com>
Date: 8 Nov 2003 10:00:03 -0800
Message-ID: <ffe966de.0311081000.6c466847@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 Sat Nov 08 2003 - 12:00:03 CST

Original text of this message

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