|
Problem IT29067
|
Status: Closed |
A QUERY WITH MANY JOINS CAN POTENTIALLY RUN FOR MANY SECONDS WITHOUT YIELDING
|
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem description: |
This problem was noted on 12.10.FC12 and the following pstack
info shows common stacks
while the thread was running without yield:
#0 0x000000306ce83a3e in memmove () from /lib64/libc.so.6
#1 0x000000000082d2c2 in project_tups ()
#2 0x0000000000850264 in hjoin_next ()
#3 0x000000000084f7c1 in hjoin_next ()
#4 0x0000000000b7e0bd in join_next ()
#5 0x000000000084f7c1 in hjoin_next ()
#6 0x0000000000b7e0bd in join_next ()
#7 0x0000000000b7e0bd in join_next ()
#8 0x0000000000b7e0bd in join_next ()
#9 0x000000000084f7c1 in hjoin_next ()
#10 0x0000000000b7e0bd in join_next ()
#11 0x0000000000b7e0bd in join_next ()
#12 0x0000000000b7e0bd in join_next ()
#13 0x0000000000b7e0bd in join_next ()
#14 0x0000000000b7e0bd in join_next ()
#15 0x000000000084f7c1 in hjoin_next ()
#16 0x000000000084f7c1 in hjoin_next ()
#17 0x000000000084f7c1 in hjoin_next ()
#18 0x000000000084f7c1 in hjoin_next ()
#19 0x000000000084f7c1 in hjoin_next ()
#20 0x000000000084f7c1 in hjoin_next ()
#21 0x000000000084f7c1 in hjoin_next ()
#22 0x000000000084f7c1 in hjoin_next ()
#23 0x000000000084f7c1 in hjoin_next ()
#24 0x000000000084f7c1 in hjoin_next ()
#25 0x000000000084f7c1 in hjoin_next ()
#26 0x0000000000845dbd in sort_open ()
#27 0x0000000000822b79 in prepselect ()
#28 0x0000000000a4063e in open_cursor ()
#29 0x0000000000a484b9 in sql_open ()
#30 0x0000000000a486a6 in sq_open ()
#31 0x0000000000b0b011 in sqmain ()
#32 0x000000000154ca1b in spawn_thread ()
#33 0x0000000001411450 in th_init_initgls ()
#34 0x0000000001456d88 in startup ()
#0 0x000000000084fd4e in hjoin_next ()
#1 0x000000000084f7c1 in hjoin_next ()
#2 0x0000000000b7e0bd in join_next ()
#3 0x000000000084f7c1 in hjoin_next ()
#4 0x0000000000b7e0bd in join_next ()
#5 0x0000000000b7e0bd in join_next ()
#6 0x0000000000b7e0bd in join_next ()
#7 0x000000000084f7c1 in hjoin_next ()
#8 0x0000000000b7e0bd in join_next ()
#9 0x0000000000b7e0bd in join_next ()
#10 0x0000000000b7e0bd in join_next ()
#11 0x0000000000b7e0bd in join_next ()
#12 0x0000000000b7e0bd in join_next ()
#13 0x000000000084f7c1 in hjoin_next ()
#14 0x000000000084f7c1 in hjoin_next ()
#15 0x000000000084f7c1 in hjoin_next ()
#16 0x000000000084f7c1 in hjoin_next ()
#17 0x000000000084f7c1 in hjoin_next ()
#18 0x000000000084f7c1 in hjoin_next ()
#19 0x000000000084f7c1 in hjoin_next ()
#20 0x000000000084f7c1 in hjoin_next ()
#21 0x000000000084f7c1 in hjoin_next ()
#22 0x000000000084f7c1 in hjoin_next ()
#23 0x000000000084f7c1 in hjoin_next ()
#24 0x0000000000845dbd in sort_open ()
#25 0x0000000000822b79 in prepselect ()
#26 0x0000000000a4063e in open_cursor ()
#27 0x0000000000a484b9 in sql_open ()
#28 0x0000000000a486a6 in sq_open ()
#29 0x0000000000b0b011 in sqmain ()
#30 0x000000000154ca1b in spawn_thread ()
#31 0x0000000001411450 in th_init_initgls ()
#32 0x0000000001456d88 in startup ()
The from/where clause shows almost 30 joins for this particular
query
from
diaemp
inner join diafun on diaemp.empcod = diafun.empcod and exists
(
select * from foavlf where foavlf.empcod
= diafun.empcod and
foavlf.funcod
= diafun.funcod and
foavlf.cta_cta=210 )
inner join diamaq on diafun.empcod = diamaq.empcod
inner join foacfu on diafun.empcod = foacfu.empcod and
diafun.funcod = foacfu.funcod
inner join diasec on diafun.empcod = diasec.empcod and
diafun.seccod = diasec.seccod
inner join diahor on diafun.empcod = diahor.empcod and
diafun.etbcod = diahor.etbcod and
diafun.horcod = diahor.horcod
inner join foasin on foacfu.empcod = foasin.empcod and
foacfu.sin_codsind = foasin.sin_codsind
inner join foacbo on foacfu.empcod = foacbo.empcod and
foacfu.cbo_codcbo = foacbo.cbo_codcbo and
foacfu.cbo_cboint = foacbo.cbo_cboint
left join diatlo on foacfu.tlocod = diatlo.tlocod
left join foanex on foacfu.empcod = foanex.empcod and
foacfu.funcod = foanex.funcod
left join diafon on diafun.empcod = diafon.empcod and
diafun.etbcod = diafon.etbcod and
diafun.funcod = diafon.funcod
left join foadem on foacfu.empcod = foadem.empcod and
foacfu.etbcod = foadem.etbcod and
foacfu.cfu_dtafast = foadem.cfu_dtafast
and
foacfu.funcod = foadem.funcod
left join foacie on foacfu.empcod = foacie.empcod and
foacfu.etbcod = foacie.etbcod and
foacfu.funcod = foacie.funcod
left join foagri on diafun.empcod = foagri.empcod and
foacfu.gri_grauinstrint =
foagri.gri_grauinstrint
left join foaief on foacfu.empcod = foaief.empcod and
foacfu.etbcod = foaief.etbcod and
foacfu.funcod = foaief.funcod
left join diaban on diaban.bancod = foaief.ief_banempres
left join dhagef on foacfu.empcod = dhagef.empcod and
foacfu.funcod = dhagef.funcod
left join diafun funnomeges on diafun.empcod =
funnomeges.empcod and
dhagef.funcodges =
funnomeges.funcod
left join diafon funfoneges on diafun.empcod =
funfoneges.empcod and
dhagef.funcodges =
funfoneges.funcod
left join diadiv div_nacion on foacfu.cfu_codnacion =
div_nacion.divcod and
div_nacion.sissist = '' and
div_nacion.dividioma = 'P' and
div_nacion.diccod =
'cfu_codnacion'
left join diadiv div_raca on foacfu.cfu_raca = div_raca.divcod
and
div_raca.sissist = '' and
div_raca.dividioma = 'P' and
div_raca.diccod = 'cfu_raca'
left join diadiv div_defic on foacfu.cfu_defic =
div_defic.divcod and
div_defic.sissist = '' and
div_defic.dividioma = 'P' and
div_defic.diccod = 'cfu_defic'
left join diadiv div_sitadm on foacfu.cfu_sitadm =
div_sitadm.divcod and
div_sitadm.sissist = '' and
div_sitadm.dividioma = 'P' and
div_sitadm.diccod = 'cfu_sitadm'
left join diadiv div_tipadm on foacfu.cfu_tipadmrais =
div_tipadm.divcod and
div_tipadm.sissist = '' and
div_tipadm.dividioma = 'P' and
div_tipadm.diccod = 'cfu_tipadmrais'
left join diadiv div_morad on foacfu.cfu_morad =
div_morad.divcod and
div_morad.sissist = '' and
div_morad.dividioma = 'P' and
div_morad.diccod = 'cfu_morad'
left join diadiv div_veic on foacfu.cfu_veic = div_veic.divcod
and
div_veic.sissist = '' and
div_veic.dividioma = 'P' and
div_veic.diccod = 'cfu_veic'
left join diadiv div_vinc on foacfu.cfu_vinc = div_vinc.divcod
and
div_vinc.sissist = '' and
div_vinc.dividioma = 'P' and
div_vinc.diccod = 'cfu_vinc'
left join diadiv div_estadcivil on foacfu.cfu_estadcivil =
div_estadcivil.divcod and
div_estadcivil.sissist = '' and
div_estadcivil.dividioma = 'P' and
div_estadcivil.diccod = 'cfu_estadcivil'
left join diadiv div_sitdemis on foacfu.cfu_sitdemis =
div_sitdemis.divcod and
div_sitdemis.sissist = '' and
div_sitdemis.dividioma = 'P' and
div_sitdemis.diccod = 'cfu_sitdemis'
left join diadiv div_grauinstrrais on foacfu.cfu_grauinstrrais
= div_grauinstrrais.divcod and
div_grauinstrrais.sissist = '' and
div_grauinstrrais.dividioma = 'P' and
div_grauinstrrais.diccod =
'cfu_grauinstrrais'
where
diafun.empcod not in ( 43, 94 ) and
diaemp.empcod='2' and
foacfu.etbcod=0 and
foacfu.cfu_sitafast 2
order by
diafun.empcod, foacfu.etbcod, diafun.funcod desc; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC14 and 14.10.xC2. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to Informix Server 12.10.xC14 or 14.10.xC2. *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.05.2019
26.02.2020
26.02.2020 |
| Problem solved at the following versions (IBM BugInfos) |
12.10.xC14,
14.10.xC2
|
| Problem solved according to the fixlist(s) of the following version(s) |
