Home » RDBMS Server » Performance Tuning » Interesting performance bottle neck issue (Oracle 11g)
Interesting performance bottle neck issue [message #641630] Wed, 19 August 2015 08:28 Go to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
The query executes fast if I remove the last OR condition. If I include that it kills time and takes more than 2 mins. each of the tables used has millions of records.
Why am I comparing 'CMPL_DATA'!= 'CMPL_DATA' ? Those 2 come as parameters from front end like Source != Target. If source and target are different values there is no problem. If they are same it is killing time. Shouldnt that be straight forward?

SELECT DISTINCT a.goc
FROM au_distinct_goc a
INNER JOIN au_distinct_goc b
ON a.goc = b.goc
AND a.src_eff_status = b.src_eff_status
AND b.src_eff_status = DECODE( 'A', 'A', 'A', b.src_eff_status)
INNER JOIN dsmt_assmt_au_mv c
ON a.seq_id = c.seq_id
AND c.au_source = 'CMPL_DATA'
WHERE 1=1
AND b.seq_id IN (17520,17521,17522,17523,17524,17525,17526,17527,17528,17529,17530,17531,17532,17533,17534,17535,17536,17537,17538,17539,17540,17541, 17542,17543,17544,17545,17546,17547,17548,17549,17550,17551,17552,17553,17554,17555,17556,17557,17558,17559,17560,17561,17562,17563,1 7564,17565,17566,17567,17568,17569,17570,17571,17572,17573,17574,17575,17576,17577,17578,17579,17580,17581,17582,17583,17584,17585,17 586,17587,17588,17589,17590,17591,17592,17593,17594,17595,17596,17597,17598,17599,17600,17601,17602,17603,17604,17605,17606,17607,176 08,17609,17610,17611,17612,17613,17614,17615,17616,17617,17618,17619,17620,17621,17622,17623,17624,17625,17626,17627,17628,17629,1763 0,17631,17632,17633,17634,17635,17636,17637,17638,17639,17640,17641,17642,17643,17644,17645,17646,17647,17648,17649,17650,17651,17652 ,17653,17654,17655,17656,17657,17658,17659,17660,17661,17662,17663,17664,17665,17666,17667,17668,17669,17670,17671,17672,17673,17674, 17675,17676,17677,17678,17679, 17680,17681,17682,17683,17684,17685,17686,17687,17688,17689,17690,17691,17692,17693,17694,17695,17696,17697,17698,17699,17700,17701,1 7702,17703,17704,17705,17706,17707,17708,17709,17710,17711,17712,17713,17714,17715,17716,17717,17718,17719,17720,17721,17722,17723,17 724,17725,17726,17727,17728,17729,17730,17731,17732,17733,17734,17735,17736,17737,17738,17739,17740,17741,17742,17743,17744,17745,177 46,17747,17748,17749,17750,17751,17752,17753,17754,17755,17756,17757,17758,17759,17760,17761,17762,17763,17764,17765,17766,17767,1776 8,17769,17770,17771,17772,17773,17774,17775,17776,17777,17778,17779,17780,17781,17782,17783,17784,17785,17786,17787,17788,17789,17790 ,17791,17792,17793,17794,17795,17796,17797,17798,17799,17800,17801,17802,17803,17804,17805,17806,17807,17808,17809,17810,17811,17812, 17813,17814,17815,17816,17817,17818,17819,17820,17821,17822,17823,17824,17825,17826,17827,17828,17829,17830,17831,17832,17833,17834,1 7835,17836,17837,17838,17839,17840,17841,17842,17843,17844,17845,17846,17847,17848,17849,17850,17851,17852,17853,17854,17855,17856,17 857,17858,17859,17860,17861,17862,17863,17864,17865,17866,17867,17868,17869,17870,17871,17872,17873,17874,17875,17876,17877,17878,178 79,17880,17881,17882,17883,17884,17885,17886,17887,17888,17889,17890,17891,17892,17893,17894,17895,17896,17897,17898,17899,17900,1790 1,17902,17903,17904,17905,17906,17907,17908,17909,17910,17911,17912,17913,17914,17915,17916,17917,17918,17919,17920,17921,17922,17923 ,17924,17925,17926,17927,17928,17929,17930,17931,17932,17933,17934,17935,17936,17937,17938,17939,17940,17941,17942,17943,17944,17945, 17946,17947,17948,17949,17950,17951,17952,17953,17954,17955,17956,17957,17958,17959,17960,17961,17962,17963,17964,17965,17966,17967,1 7968,17969,17970,17971,17972,17973,17974,17975,17976,17977,17978,17979,17980,17981,17982,17983,17984,17985,17986,17987,17988,17989,17 990,17991,17992,17993,17994,17995,17996,17997,17998,17999,18000,18001,18002,18003,18004,18005, 18006,18007,18008,18009,18010)
AND (
( 'CMPL_DATA' ='CMPL_DATA'
AND a.seq_id IN (select seq_id from au_distinct_goc minus
SELECT to_number(trim(regexp_substr(str, '[^,]+', 1, LEVEL))) str
FROM ( SELECT '17520,17521,17522,17523,17524,17525,17526,17527,17528,17529,17530,17531,17532,17533,17534,17535,17536,17537,17538,17539,17540,17541, 17542,17543,17544,17545,17546,17547,17548,17549,17550,17551,17552,17553,17554,17555,17556,17557,17558,17559,17560,17561,17562,17563,1 7564,17565,17566,17567,17568,17569,17570,17571,17572,17573,17574,17575,17576,17577,17578,17579,17580,17581,17582,17583,17584,17585,17 586,17587,17588,17589,17590,17591,17592,17593,17594,17595,17596,17597,17598,17599,17600,17601,17602,17603,17604,17605,17606,17607,176 08,17609,17610,17611,17612,17613,17614,17615,17616,17617,17618,17619,17620,17621,17622,17623,17624,17625,17626,17627,17628,17629,1763 0,17631,17632,17633,17634,17635,17636,17637,17638,17639,17640,17641,17642,17643,17644,17645,17646,17647,17648,17649,17650,17651,17652 ,17653,17654,17655,17656,17657,17658,17659,17660,17661,17662,17663,17664,17665,17666,17667,17668,17669,17670,17671,17672,17673,17674, 17675,17676,17677,17678,17679,17680,17681,17682,17683,17684,17685,17686,17687,17688,17689,17690,17691,17692,17693,17694,17695,17696,1 7697,17698,17699,17700,17701,17702,17703,17704,17705,17706,17707,17708,17709,17710,17711,17712,17713,17714,17715,17716,17717,17718,17 719,17720,17721,17722,17723,17724,17725,17726,17727,17728,17729,17730,17731,17732,17733,17734,17735,17736,17737,17738,17739,17740,177 41,17742,17743,17744,17745,17746,17747,17748,17749,17750,17751,17752,17753,17754,17755,17756,17757,17758,17759,17760,17761,17762,1776 3,17764,17765,17766,17767,17768,17769,17770,17771,17772,17773,17774,17775,17776,17777,17778,17779,17780,17781,17782,17783,17784,17785 ,17786,17787,17788,17789,17790,17791,17792,17793,17794,17795,17796,17797,17798,17799,17800,17801,17802,17803,17804,17805,17806,17807, 17808,17809,17810,17811,17812,17813,17814,17815,17816,17817,17818,17819,17820,17821,17822,17823,17824,17825,17826,17827,17828,17829,1 7830,17831,17832,17833,17834,17835,17836,17837,17838,17839,17840,17841,17842,17843,17844,17845,17846,17847,17848,17849,17850,17851,17 852,17853,17854,17855,17856,17857,17858,17859,17860,17861,17862,17863,17864,17865,17866,17867,17868,17869,17870,17871,17872,17873,178 74,17875,17876,17877,17878,17879,17880,17881,17882,17883,17884,17885,17886,17887,17888,17889,17890,17891,17892,17893,17894,17895,1789 6,17897,17898,17899,17900,17901,17902,17903,17904,17905,17906,17907,17908,17909,17910,17911,17912,17913,17914,17915,17916,17917,17918 ,17919,17920,17921,17922,17923,17924,17925,17926,17927,17928,17929,17930,17931,17932,17933,17934,17935,17936,17937,17938,17939,17940, 17941,17942,17943,17944,17945,17946,17947,17948,17949,17950,17951,17952,17953,17954,17955,17956,17957,17958,17959,17960,17961,17962,1 7963,17964,17965,17966,17967,17968,17969,17970,17971,17972,17973,17974,17975,17976,17977,17978,17979,17980,17981,17982,17983,17984,17 985,17986,17987,17988,17989,17990,17991,17992,17993,17994,17995,17996,17997,17998,17999,18000,18001,18002,18003,18004,18005, 18006,18007,18008,18009,18010' str from dual)
CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0)
)
OR 'CMPL_DATA'!= 'CMPL_DATA'
)
;
Re: Interesting performance bottle neck issue [message #641634 is a reply to message #641630] Wed, 19 August 2015 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post EXPLAIN PLAN for both cases
Re: Interesting performance bottle neck issue [message #641636 is a reply to message #641630] Wed, 19 August 2015 08:45 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
These four lines have no purpose:
INNER JOIN au_distinct_goc b
ON a.goc = b.goc
AND a.src_eff_status = b.src_eff_status
AND b.src_eff_status = DECODE( 'A', 'A', 'A', b.src_eff_status)
The fact that your programmer included them shows that he des not understand what he is doing. Better re-visit the whole query, starting by defining what it is that you want to achieve.
Re: Interesting performance bottle neck issue [message #641639 is a reply to message #641636] Wed, 19 August 2015 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're over-looking the where clause John. To pseudo code it for ease of understanding:
WHERE b.seq_id IN (<list>)
AND ((param1 = param2
      AND a.seq_id IN (SELECT <different_list>
                       MINUS <list>
                      )
     OR param1 != param2


Looks like au_distinct_goc is self referential
Re: Interesting performance bottle neck issue [message #641640 is a reply to message #641639] Wed, 19 August 2015 09:30 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
@ram50958 - as Blackswan said we'll need explain plans along with table structures and indexes.
Also you should indicate which values are actually variable. I rather hope that decode doesn't always have three 'A' parameters.
Re: Interesting performance bottle neck issue [message #641641 is a reply to message #641639] Wed, 19 August 2015 09:34 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
Are you sure? I would have thought that this
SELECT DISTINCT a.goc
FROM au_distinct_goc a
INNER JOIN au_distinct_goc b
ON a.goc = b.goc
AND a.src_eff_status = b.src_eff_status
AND b.src_eff_status = DECODE( 'A', 'A', 'A', b.src_eff_status)

is equivalent to this
SELECT DISTINCT a.goc
FROM au_distinct_goc a
WHERE
AND a.src_eff_status is not null
and replace any b. lower down in the query with a.

I am of course open to correction. I hope you are too, Ram Smile


--edit: tags, and a duplicate filter

[Updated on: Wed, 19 August 2015 09:37]

Report message to a moderator

Re: Interesting performance bottle neck issue [message #641642 is a reply to message #641641] Wed, 19 August 2015 09:42 Go to previous messageGo to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
Thank you everyone. The select/join clause is not causing any issue and the decode three values comes as parameters. The problem is only when I include the OR clause especially when param1 and param2 are passed same values (which means the OR condition becomes invalid)

WHERE b.seq_id IN (<list>)
AND ((param1 = param2
AND a.seq_id IN (SELECT <different_list>
MINUS <list>
)
OR param1 != param2
Re: Interesting performance bottle neck issue [message #641643 is a reply to message #641642] Wed, 19 August 2015 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/

Either you provide more details or you live with what you have.
Nobody can tune SQL simply by looking at the statement alone.
Re: Interesting performance bottle neck issue [message #641644 is a reply to message #641641] Wed, 19 August 2015 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're assuming:
a) those 'A''s in the decode are hard-coded (I doubt it, though the OP really should make that clear)
b) goc or (goc and src_eff_status) are unique.

If they're not then the combiniation of the where claus and the join to dsmt_assmt_au_mv mean it's roughly this:
SELECT DISTINCT au_distinct_goc.goc
WHERE au_distinct_goc matches data in c
AND au_distinct_goc matches other data in the same table that in turn match a list of seq_ids
AND a.seq_id in another list if two parameters match
Re: Interesting performance bottle neck issue [message #641647 is a reply to message #641644] Wed, 19 August 2015 10:05 Go to previous messageGo to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
I dont see any attach option to attach explain plan
Re: Interesting performance bottle neck issue [message #641648 is a reply to message #641647] Wed, 19 August 2015 10:09 Go to previous message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
ram50958 wrote on Wed, 19 August 2015 08:05
I dont see any attach option to attach explain plan


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

COPY & PASTE work & work well here. Please use them.

Previous Topic: Please help for Resolving Issue for Explain plan as query taking long time for execution
Next Topic: Response time
Goto Forum:
  


Current Time: Sun Feb 25 14:36:59 CST 2018

Total time taken to generate the page: 0.02078 seconds