Home » RDBMS Server » Performance Tuning » Left outer join taking more time
Left outer join taking more time [message #377469] Tue, 23 December 2008 03:21 Go to next message
srihari.gurram
Messages: 13
Registered: May 2008
Junior Member
Hi All,

My query is taking more time,Can any one suggest a better way to handle this:

SELECT eq.MASTER_NUM,
ta.DV_ASSET_FLG,
ta.PREF_ASSET_ID_TYP,
ta.ASSET3,
ta.ASSET_NM,
eq.EXCH_ID,
eq.Q_DATE,
eq.USER_ID,
eq.ASSET_ID,
eq.PRC_DT,
ta.ASSET_TYP_CD,
ta.ASSET_STAT_CD,
trt.REASON_TYP_DESC,
ta.HELD_INDCTR,
eq.REASON,
T_ASSET_ASSET_REL_A.REL_ASSET_ID,
T_ASSET_ASSET_REL_B.ASSET_ID,
ta.CNTRY_QUOTE,
eq.VERIFY_USER_ID,
ta.OUT_OF_HOLD_DT,
ta.FIRST_DT_HELD,
eq.PRICING_PT_ID,
tae.ASSET_EXT_ID_SYS_NM SECNDR_ASSET_ID_TYP_CD,
CASE
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CU' THEN ta.CUSIP
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IS' THEN ta.ISIN
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'QU' THEN ta.QUICK
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'RE' THEN ta.RIC
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SE' THEN ta.SEDOL
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'VA' THEN ta.VALOR
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CI' THEN ta.CINS
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'LU' THEN ta.LUXEM
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AU' THEN ta.AUSTRI
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SV' THEN ta.SVM
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SI' THEN ta.SICOVAM
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'WP' THEN ta.WPK
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'DU' THEN ta.DUTCH
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CE' THEN ta.CEDEL
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'EU' THEN ta.EUROCLEAR
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CO' THEN ta.COMMON
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'ID' THEN ta.IDC_TCKR
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AM' THEN ta.AMS_CUSIP
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IF' THEN ta.IFC_TCKR
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MS' THEN ta.MSTC_CUSIP
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SB' THEN ta.SB_CUSIP
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MO' THEN ta.MORT_CUSIP
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'TS' THEN ta.TSE_TCKR
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'FM' THEN ta.FMSECRN
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T1' THEN ta.ASSET1
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T2' THEN ta.ASSET2
WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T3' THEN ta.ASSET3
END SECNDR_ASSET_ID,
eg.DESCRIP AS EXCHANGE_NAME,
ta.CNTRY_QUOTE ,
ta.ASSET_TYP_CD ,
ta.CNTRY_ID ,
(select tc1.CNTRY_NM from T_CNTRY tc1
where tc1.CNTRY_ID = ta.CNTRY_QUOTE
AND eq.ASSET_ID = ta.ASSET_ID and rownum = 1) as CNTRY_QUOTE_NM ,
(select tc2.CNTRY_NM from T_CNTRY tc2
where tc2.CNTRY_ID = ta.CNTRY_ID
AND eq.ASSET_ID = ta.ASSET_ID and rownum = 1) as CNTRY_ID_NM ,
tat.ASSET_TYP_NM
FROM T_PRC_ERR_Q eq,
T_ASSET ta
LEFT OUTER JOIN T_ASSET_TYP_CNTRY atc ON ( ta.ASSET_TYP_CD = atc.ASSET_TYP_CD
AND ta.CNTRY_QUOTE = atc.CNTRY_ID
AND atc.ASSET_ID = -1 )
LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_A ON (ta.asset_id = t_asset_asset_rel_a.asset_id)
LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_B ON (ta.asset_id = t_asset_asset_rel_b.rel_asset_id),
T_REASON_TYP trt,
T_BUS_GRP_CNTRY bgc,
T_USER tu,
T_PRICING_PT_GRP_ASSIGN ptg,
T_ASSET_EXT_ID_SYS tae,
T_EXCH_MKT mkt,
T_EXCH_GRP eg,
T_ASSET_TYP tat
WHERE
( eq.ASSET_ID = ta.ASSET_ID ) and
( eq.REASON = trt.REASON_TYP_CD ) and
( bgc.BUS_GRP = tu.BUS_GRP ) and
( ta.CNTRY_QUOTE = bgc.CNTRY_ID ) and
( tu.PRICING_PT_GRP_ID = ptg.PRICING_PT_GRP_ID ) and
( ptg.PRICING_PT_ID = eq.PRICING_PT_ID ) and
( trt.reason_typ_type = 'E' ) AND
( tu.user_id = 2021 ) AND
(tae.ASSET_EXT_ID_SYS_CD = NVL(atc.SECNDR_ASSET_ID_TYP_CD,' ') or
tae.ASSET_EXT_ID_SYS_CD = NVL(atc.SECNDR_ASSET_ID_TYP_CD,' ')) AND
(tae.USAGE_FLG = 'Y') AND
(mkt.EXCH_ID = eq.EXCH_ID) AND
(eg.EXCH_GRP_ID = mkt.EXCH_GRP_ID) AND
( ta.ASSET_TYP_CD = bgc.ASSET_TYP_CD ) AND
( bgc.EXCH_ID = eq.EXCH_ID )
AND (tat.ASSET_TYP_CD = ta.ASSET_TYP_CD)
AND NOT EXISTS (SELECT 1 FROM T_EMD_ERR_Q_RULES
WHERE ta.ASSET_TYP_CD = T_EMD_ERR_Q_RULES.ASSET_TYP_CD
AND ta.CNTRY_QUOTE = T_EMD_ERR_Q_RULES.CNTRY_QUOTE
and ta.CNTRY_ID = T_EMD_ERR_Q_RULES.CNTRY_ID)


Attaching the explain plan for the above query.
Re: Left outer join taking more time [message #377470 is a reply to message #377469] Tue, 23 December 2008 03:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You haven't attached the explain plan.
Re: Left outer join taking more time [message #377612 is a reply to message #377469] Tue, 23 December 2008 21:38 Go to previous messageGo to next message
srihari.gurram
Messages: 13
Registered: May 2008
Junior Member
Oops here's the explain plan.
Re: Left outer join taking more time [message #377838 is a reply to message #377469] Thu, 25 December 2008 21:05 Go to previous messageGo to next message
srihari.gurram
Messages: 13
Registered: May 2008
Junior Member
Hi there,

Can anyone help on this please...
Re: Left outer join taking more time [message #377851 is a reply to message #377838] Thu, 25 December 2008 22:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Both your query and plan are unreadable because they are not formatted. Look though some of the posts in this forum that include formatted queries and plans.

See this chapter of the Oracle Performance Tuning Guide on displaying Explain Plans.

Enclose your query and plan in [CODE]...[/CODE] tags so that your formating is preserved.

[Updated on: Thu, 25 December 2008 22:59]

Report message to a moderator

Re: Left outer join taking more time [message #377911 is a reply to message #377851] Fri, 26 December 2008 04:08 Go to previous messageGo to next message
srihari.gurram
Messages: 13
Registered: May 2008
Junior Member
SELECT eq.MASTER_NUM,   
         ta.DV_ASSET_FLG,   
         ta.PREF_ASSET_ID_TYP,   
         ta.ASSET3,   
         ta.ASSET_NM,   
         eq.EXCH_ID,   
         eq.Q_DATE,   
         eq.USER_ID,   
         eq.ASSET_ID,   
         eq.PRC_DT,   
         ta.ASSET_TYP_CD,   
         ta.ASSET_STAT_CD,   
         trt.REASON_TYP_DESC,   
         ta.HELD_INDCTR,   
         eq.REASON,   
         T_ASSET_ASSET_REL_A.REL_ASSET_ID,   
         T_ASSET_ASSET_REL_B.ASSET_ID,   
         ta.CNTRY_QUOTE,   
         eq.VERIFY_USER_ID,   
         ta.OUT_OF_HOLD_DT,   
         ta.FIRST_DT_HELD,   
         eq.PRICING_PT_ID,
         tae.ASSET_EXT_ID_SYS_NM SECNDR_ASSET_ID_TYP_CD,
   CASE
    WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CU' THEN ta.CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IS' THEN ta.ISIN
    WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'QU' THEN ta.QUICK
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'RE' THEN ta.RIC
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SE' THEN ta.SEDOL
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'VA' THEN ta.VALOR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CI' THEN ta.CINS
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'LU' THEN ta.LUXEM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AU' THEN ta.AUSTRI
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SV' THEN ta.SVM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SI' THEN ta.SICOVAM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'WP' THEN ta.WPK
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'DU' THEN ta.DUTCH
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CE' THEN ta.CEDEL
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'EU' THEN ta.EUROCLEAR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CO' THEN ta.COMMON
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'ID' THEN ta.IDC_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AM' THEN ta.AMS_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IF' THEN ta.IFC_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MS' THEN ta.MSTC_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SB' THEN ta.SB_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MO' THEN ta.MORT_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'TS' THEN ta.TSE_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'FM' THEN ta.FMSECRN
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T1' THEN ta.ASSET1
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T2' THEN ta.ASSET2
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T3' THEN ta.ASSET3
   END SECNDR_ASSET_ID,
   eg.DESCRIP AS EXCHANGE_NAME,
    ta.CNTRY_QUOTE ,
    ta.ASSET_TYP_CD ,
    ta.CNTRY_ID ,
 (select distinct tc1.CNTRY_NM from T_CNTRY tc1
   where tc1.CNTRY_ID = ta.CNTRY_QUOTE
    AND eq.ASSET_ID = ta.ASSET_ID) as CNTRY_QUOTE_NM ,
 (select distinct tc2.CNTRY_NM from T_CNTRY tc2
   where tc2.CNTRY_ID = ta.CNTRY_ID
    AND eq.ASSET_ID = ta.ASSET_ID) as CNTRY_ID_NM ,
    tat.ASSET_TYP_NM

   FROM T_PRC_ERR_Q eq,   
         T_ASSET ta    
    LEFT OUTER JOIN T_ASSET_TYP_CNTRY atc  ON ( ta.ASSET_TYP_CD = atc.ASSET_TYP_CD 
                                                AND ta.CNTRY_QUOTE = atc.CNTRY_ID 
                                                AND atc.ASSET_ID = -1 )         
    LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_A ON (ta.asset_id = t_asset_asset_rel_a.asset_id)
    LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_B ON (ta.asset_id = t_asset_asset_rel_b.rel_asset_id),  
         T_REASON_TYP trt,   
         T_BUS_GRP_CNTRY bgc,   
         T_USER tu,   
         T_PRICING_PT_GRP_ASSIGN ptg,
         T_ASSET_EXT_ID_SYS tae,
         T_EXCH_MKT mkt,
         T_EXCH_GRP eg,
         T_ASSET_TYP tat
         --t_asset_typ_cntry atc
   WHERE 
      
         ( eq.ASSET_ID = ta.ASSET_ID ) and  
         ( eq.REASON = trt.REASON_TYP_CD ) and  
         ( bgc.BUS_GRP = tu.BUS_GRP ) and  
         ( ta.CNTRY_QUOTE = bgc.CNTRY_ID ) and  
         ( tu.PRICING_PT_GRP_ID = ptg.PRICING_PT_GRP_ID ) and  
         ( ptg.PRICING_PT_ID = eq.PRICING_PT_ID ) and  
         ( trt.reason_typ_type = 'E' ) AND  
         ( tu.user_id = 2021  )    AND
         (tae.ASSET_EXT_ID_SYS_CD =  NVL(atc.SECNDR_ASSET_ID_TYP_CD,' ') or         
         tae.ASSET_EXT_ID_SYS_CD  =  NVL(atc.SECNDR_ASSET_ID_TYP_CD,'  ')) AND         
         (tae.USAGE_FLG = 'Y')  AND
         (mkt.EXCH_ID = eq.EXCH_ID) AND
         (eg.EXCH_GRP_ID = mkt.EXCH_GRP_ID)  AND
         ( ta.ASSET_TYP_CD = bgc.ASSET_TYP_CD ) AND
         ( bgc.EXCH_ID = eq.EXCH_ID ) 
    AND (tat.ASSET_TYP_CD =ta.ASSET_TYP_CD)
      AND NOT EXISTS (SELECT 1 FROM  T_EMD_ERR_Q_RULES
       WHERE ta.ASSET_TYP_CD = T_EMD_ERR_Q_RULES.ASSET_TYP_CD
       AND ta.CNTRY_QUOTE = T_EMD_ERR_Q_RULES.CNTRY_QUOTE
                            and ta.CNTRY_ID = T_EMD_ERR_Q_RULES.CNTRY_ID);
Re: Left outer join taking more time [message #377936 is a reply to message #377469] Fri, 26 December 2008 09:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
When tuning something big like this the best approach for me is divide and conquer. You need to take out all the pieces of the query and put them back in one at a time to see how the performance changes. Usually one join or one section of the query is causing your performance to bloat. I generally don't even bother with query plans at this point because you are really only interested in wall time.

So...

Start with one table and its part of the where clause and time it. Then add another table with its associated where clause items and time that. Then...

Twenty tables later you know the cost of adding each additional table and its join to your query. From that, you may find there is an obvious jump in time with one or more pieces/joins. That is where you can start tuning. You can often re-code the offending pieces a different way, or take a look at adding additional indexes.

Yes, this will take a day for you to do.
Yes, you can do it without anyone's help.

Yes, this is tedious but this is the job you signed up for when you decided you wanted to be a programmer. Its not all just writing code. Sometimes you got to do some dirty work. If you were hoping there was a genious who would take 30 seconds to look at your code and say "aha here is the problem", well maybe there is but so far that genious hasn't logged on and responsed.

Good luck, Kevin

[Updated on: Fri, 26 December 2008 09:25]

Report message to a moderator

Re: Left outer join taking more time [message #377968 is a reply to message #377936] Sat, 27 December 2008 02:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Can you PLEASE read the chapter I referenced and format your Explain Plan as it states.

Ross Leishman
Re: Left outer join taking more time [message #377969 is a reply to message #377968] Sat, 27 December 2008 02:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I also see you have some correlated scalar sub-queries in your SELECT clause. Read this article to find out why that might not be such a hot idea.

Ross Leishman
Re: Left outer join taking more time [message #377989 is a reply to message #377469] Sat, 27 December 2008 08:57 Go to previous messageGo to next message
srihari.gurram
Messages: 13
Registered: May 2008
Junior Member
Hi Kevin,

Thanks for putting in your suggestions. Yes we have tried out with divide and conquer methods and since there are 10 tables used here,all the join conditions has to be put in at a time but yes as u said we will try out with query spilt. But we were thinking on whether we have any better replacement for left outer join and not exists clause.

Re: Left outer join taking more time [message #377993 is a reply to message #377469] Sat, 27 December 2008 11:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK so... If you did the work breaking the query up into pieces, what were the timing results? Which pieces of the query take the most time? Show us the SQLPLUS timings.

Let me summarize that you have suggested issues in the following sections of code, or someone else has suggested you consider:

       (SELECT DISTINCT tc1.cnTry_nm
        FROM   t_cnTry tc1
        WHERE  tc1.cnTry_Id = ta.cnTry_Quote
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Quote_nm,
       (SELECT DISTINCT tc2.cnTry_nm
        FROM   t_cnTry tc2
        WHERE  tc2.cnTry_Id = ta.cnTry_Id
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Id_nm,


       LEFT OUTER JOIN t_Asset_typ_cnTry Atc ON (ta.Asset_typ_cd = Atc.Asset_typ_cd AND ta.cnTry_Quote = Atc.cnTry_Id AND Atc.Asset_Id = - 1)
       LEFT OUTER JOIN t_Asset_Asset_rel t_Asset_Asset_rel_a ON (ta.Asset_Id = t_Asset_Asset_rel_a.Asset_Id)
       LEFT OUTER JOIN t_Asset_Asset_rel t_Asset_Asset_rel_b ON (ta.Asset_Id = t_Asset_Asset_rel_b.rel_Asset_Id),


       AND NOT EXISTS (SELECT 1
                       FROM   t_emd_Err_q_Rules
                       WHERE  ta.Asset_typ_cd = t_emd_Err_q_Rules.Asset_typ_cd
                              AND ta.cnTry_Quote = t_emd_Err_q_Rules.cnTry_Quote
                              AND ta.cnTry_Id = t_emd_Err_q_Rules.cnTry_Id);


      indexing of join columns


Have you diagramed for youself the query plan. Not using explain plan or tkprof, but rather written down on a piece of paper the joins between tables and the columns involved? Doing this will tell you what indexes are most likely to be of service in this query. Then you can see if you have these indexes.

Do the timings and post them. If you are looking for a quick set of tests then I might suggest the following:

1) remove the scalar subselects
2) change the left outer joins to regular joins
3) remove the correlated subquery

get timing

add back in #1 get timing
add back in #2 get timing
add back in #3 get timing

Also, I do not understand your scalar subquerys. In particular the following line looks like a mistake:

AND eq.Asset_Id = ta.Asset_Id

       (SELECT DISTINCT tc1.cnTry_nm
        FROM   t_cnTry tc1
        WHERE  tc1.cnTry_Id = ta.cnTry_Quote
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Quote_nm,


It makes no sense to me as this line references tables outside your subselect and thus I see no value to it. Maybe it is just me not getting what you are trying to do with the subquery. Can you explain it please. This line is already in your main query so why is it in your subquerys?

Taking one last jabb at it, I think the following is moronic (I do not mean this as an insult to you):

       AND (tae.Asset_ext_Id_sys_cd = Nvl(Atc.secndr_Asset_Id_typ_cd,' ') OR tae.Asset_ext_Id_sys_cd = Nvl(Atc.secndr_Asset_Id_typ_cd,'  '))


I am assuming that your are checking to see if a code has values of either one or two spaces in it? Oracle in many situations will strip trailing spaces from the end of columns on tables and fields in plsql code and other Oracle tools (at least it used to do this in the past). Using space as a value is dumb. You should go back to the people who designed this database and ask them what they were thinking and why they settled on spaces as a value. When they made this decision, did they know how Oracle treats spaces? I am not suggesting that what you cannot store space in a column, nor that what you have does not work because it does. Neither am I suggesting that you should change what you are doing if it is working for you. My experience is this is a problem waiting to bite you so please be careful with it.

Good luck, Kevin

[Updated on: Sat, 27 December 2008 12:39]

Report message to a moderator

Previous Topic: Query Rewrite to Utilize Materialized Views on a Remote Database
Next Topic: performance of delete/insert deteriorate over time
Goto Forum:
  


Current Time: Wed Dec 07 07:06:35 CST 2016

Total time taken to generate the page: 0.07786 seconds