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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #377851 is a reply to message #377838] |
Thu, 25 December 2008 22:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 #377989 is a reply to message #377469] |
Sat, 27 December 2008 08:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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);
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
|
|
|
Goto Forum:
Current Time: Wed Sep 11 03:16:58 CDT 2024
|