Re: unkillable sid's
A DBA on IDS 9.40FC2 / HP-UX 11.11 had three PeopleSoft sessions running the same complex multi-table view query that could not be killed, with xtree showing nothing and the server eventually refusing new connections (onmode hung too). Advice: onstat -g stk is unreliable for 'running' threads, so use onstat -g act to find the VP and onmode -X stack <vp>; also, onmode -z only sets a kill flag, so non-yielding threads can't be killed. The stack showed the thread spinning in the optimizer (sqoptim/op_join/mergepaths), likely evaluating too many join permutations and starving the CPU VPs. Suggested workarounds were removing any optimizer directives or using SET OPTIMIZATION LOW around the statement. The poster only restarted the engine to recover; no confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
On Feb 29, 3:10 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> On 29 Feb, 18:42, Darren_Jac...@carmax.com wrote:
>
>
>
> > Greetings,
>
> > 9.40FC2xa
> > HPUX 11.11
>
> > I have 3 sessions that I can't kill. All three sessions are running the
> > same sql and the onstat -g ses are all the same. I tried an xtree to see
> > what, if anything these sessions are doing, and xtree comes back blank.
>
> > Here is the ses...yes, it's PeopleSoft.
>
> > Any ideas on how to take them out? We are having perf issues and I've seen
> > in the past where these queries have caused informix to hang.
>
> > Thanks in advance for any insight.
>
> > $ ses 194>
> > IBM Informix Dynamic Server Version 9.40.FC2XA -- On-Line -- Up 3 days
> > 08:09:36 -- 11580348 Kbytes>
> > session #RSAM total used
> > dynamic
> > id user tty pid hostname threads memory memory
> > explain
> > 194 sysadm FSAPP2P 3900 fsapp2p. 1 733184 691328
> > off
>
> > tid name rstcb flags curstk status
> > 309 sqlexec c0000002488914a0 ---P--- 62752 running
>
> > Memory pools count 6
> > name class addr totalsize freesize #allocfrag
> > #freefrag
> > 194 V c00000024a00e040 479232 31512 2546 28
> > 194*O0 V c00000025c469040 36864 2888 31 2
> > 194*O1 V c00000025bbf0040 40960 2888 35 2
> > 194*O2 V c00000025bc4d040 49152 1864 44 2
> > 194*O3 V c00000025bdbc040 69632 1864 64 2
> > 194*O4 V c00000025beb3040 57344 840 53 1
>
> > name free used name free used
> > overhead 0 19536 mtmisc 0 280
> > scb 0 312 opentable 0 49432
> > filetable 0 8160 ru 0 304
> > misc 0 128 blobio 0 10192
> > log 0 2184 temprec 0 10104
> > blob 0 2448 keys 0 40368
> > ralloc 0 341296 gentcb 0 1808
> > ostcb 0 3416 sort 0 104
> > sqscb 0 180592 sql 0 72
> > rdahead 0 1120 hashfiletab 0 552
> > osenv 0 1080 sqtcb 0 9600
> > fragman 0 624 udr 0 7616
>
> > sqscb info
> > scb sqscb optofc pdqpriority sqlstats optcompind
> > directives
> > c000000247f518b0 c00000024a00f028 0 0 0 2
> > 1
>
> > Sess SQL Current Iso Lock SQL ISAM F.E.
> > Id Stmt type Database Lvl Mode ERR ERR Vers
> > Explain
> > 194 SELECT rol8prd CR Wait 0 0 9.03 Off
>
> > Current SQL statement :
> > SELECT BUSINESS_UNIT, BOOK FROM PS_SP_BOOKB3_CLSVW A WHERE
> > OPRCLASS='AMANLST' AND BUSINESS_UNIT=' ' ORDER BY BUSINESS_UNIT, BOOK> > FOR
> > READ ONLY
>
> > Last parsed SQL statement :
> > SELECT BUSINESS_UNIT, BOOK FROM PS_SP_BOOKB3_CLSVW A WHERE
> > OPRCLASS='AMANLST' AND BUSINESS_UNIT=' ' ORDER BY BUSINESS_UNIT, BOOK> > FOR
> > READ ONLY
>
> > Here's the view:
>
> > create view "sysadm".ps_sp_bookb3_clsvw
> > (oprclass,business_unit,book,acct_ent_tmpl_id,
> > required_sw,book_type,currency_cd,capitalization_min,lease_cap_min,distribution_sw,
> > disposal_dist_sw,business_unit_gl,cal_depr_pd,rt_type,ledger_group,ledger,bud_ledger_group)
> > as
> > select x1.oprclass ,x0.business_unit ,x0.book ,x0.acct_ent_tmpl_id
> > ,x0.required_sw ,x0.book_type ,x0.currency_cd ,x0.capitalization_min
> > ,x0.lease_cap_min ,x0.distribution_sw ,x0.disposal_dist_sw
> > ,x0.business_unit_gl ,x0.cal_depr_pd ,x0.rt_type ,x0.ledger_group
> > ,x0.ledger ,x0.bud_ledger_group from "sysadm".ps_bu_book_tbl
> > x0 ,"sysadm".ps_sp_book_clsvw x1 ,"sysadm".ps_set_cntrl_rec
> > x2 ,"sysadm".ps_led_grp_tbl x3 ,"sysadm".ps_led_grp_led_tbl
> > x4 where (((((((((((x0.distribution_sw = 'Y' ) AND (x2.recname
> > = 'LED_GRP_TBL' ) ) AND (x2.setcntrlvalue = x0.business_unit_gl
> > ) ) AND (x3.setid = x2.setid ) ) AND (x3.ledger_group = x0.ledger_group
> > ) ) AND (x4.setid = x3.setid ) ) AND (x4.ledger_group = x3.ledger_group
> > ) ) AND ((((x3.ledgers_sync = 'Y' ) AND (x4.primary_ledger
> > = 'Y' ) ) AND ((x4.ledger = x0.ledger ) OR (x0.ledger = ''
> > ) ) ) OR ((x3.ledgers_sync != 'Y' ) AND (x4.ledger = x0.ledger
> > ) ) ) ) AND (x1.setid = (select x5.setid from "sysadm".ps_set_cntrl_rec
> > x5 where ((x5.recname = 'BOOK_DEFN_TBL' ) AND (x5.setcntrlvalue
> > = x0.business_unit ) ) ) ) ) AND (x1.book = x0.book ) ) AND
> > (x1.business_unit = x0.business_unit ) ) ;>
> What does onstat -g stk for the threads give? What flags are against
> the session in onstat -u?
onstat -g stk usually isn't very accurate when run against threads inthe "running" state (it can sometimes print a stack out for the
thread, but the result is usually just the stack of what the thread
had been doing the last time it yielded, not what it is currently
doing, other times -g stk on a running thread just failes to unwind
the stack at all and just gives things like *unknown* since the
program counter, and stack pointer (tos) that is checked by the -g stk
code isn't updated on the fly as the thread executes). To get an
accurate stack for running threads you would be better off running the
onmode -X stack <vp id> which will generate a stack into a file. Tofind the vp the thread is executing on you can use onstat -g act and
look at the tid column and the vp-class column..if it's on 1cpu then
use 1 for vp id, if its on 4cpu then use 4 for the vp-id.
As a side note, the onmode command to kill sessions doesn't actually
kill the session, it just sets a kill flag that needs to be checked
for by the session so it will then kill itself. If the session is in
some sort of non yielding loop and it's stuck, then the onmode command
to kill that session usually isn't effective, and unless the threat
comes out of it's loop and checks the kill flag, you are stuck with
forcing the server offline, or possibly forcing it to single user mode
to try and kill the thread that way.
Jacques
On 29 Feb, 21:41, jpren...@yahoo.com wrote:
> On Feb 29, 3:10 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
>
>
>
>
>
> > On 29 Feb, 18:42, Darren_Jac...@carmax.com wrote:
>
> > > Greetings,
>
> > > 9.40FC2xa
> > > HPUX 11.11
>
> > > I have 3 sessions that I can't kill. All three sessions are running the
> > > same sql and the onstat -g ses are all the same. I tried an xtree to see
> > > what, if anything these sessions are doing, and xtree comes back blank.
>
> > > Here is the ses...yes, it's PeopleSoft.
>
> > > Any ideas on how to take them out? We are having perf issues and I've seen
> > > in the past where these queries have caused informix to hang.
>
> > > Thanks in advance for any insight.
>
> > > $ ses 194>
> > > IBM Informix Dynamic Server Version 9.40.FC2XA -- On-Line -- Up 3 days
> > > 08:09:36 -- 11580348 Kbytes>
> > > session #RSAM total used
> > > dynamic
> > > id user tty pid hostname threads memory memory
> > > explain
> > > 194 sysadm FSAPP2P 3900 fsapp2p. 1 733184 691328
> > > off
>
> > > tid name rstcb flags curstk status
> > > 309 sqlexec c0000002488914a0 ---P--- 62752 running
>
> > > Memory pools count 6
> > > name class addr totalsize freesize #allocfrag
> > > #freefrag
> > > 194 V c00000024a00e040 479232 31512 2546 28
> > > 194*O0 V c00000025c469040 36864 2888 31 2
> > > 194*O1 V c00000025bbf0040 40960 2888 35 2
> > > 194*O2 V c00000025bc4d040 49152 1864 44 2
> > > 194*O3 V c00000025bdbc040 69632 1864 64 2
> > > 194*O4 V c00000025beb3040 57344 840 53 1
>
> > > name free used name free used
> > > overhead 0 19536 mtmisc 0 280
> > > scb 0 312 opentable 0 49432
> > > filetable 0 8160 ru 0 304
> > > misc 0 128 blobio 0 10192
> > > log 0 2184 temprec 0 10104
> > > blob 0 2448 keys 0 40368
> > > ralloc 0 341296 gentcb 0 1808
> > > ostcb 0 3416 sort 0 104
> > > sqscb 0 180592 sql 0 72
> > > rdahead 0 1120 hashfiletab 0 552
> > > osenv 0 1080 sqtcb 0 9600
> > > fragman 0 624 udr 0 7616
>
> > > sqscb info
> > > scb sqscb optofc pdqpriority sqlstats optcompind
> > > directives
> > > c000000247f518b0 c00000024a00f028 0 0 0 2
> > > 1
>
> > > Sess SQL Current Iso Lock SQL ISAM F.E.
> > > Id Stmt type Database Lvl Mode ERR ERR Vers
> > > Explain
> > > 194 SELECT rol8prd CR Wait 0 0 9.03 Off
>
> > > Current SQL statement :
> > > SELECT BUSINESS_UNIT, BOOK FROM PS_SP_BOOKB3_CLSVW A WHERE> > > OPRCLASS='AMANLST' AND BUSINESS_UNIT=' ' ORDER BY BUSINESS_UNIT, BOOK
> > > FOR
> > > READ ONLY
>
> > > Last parsed SQL statement :
> > > SELECT BUSINESS_UNIT, BOOK FROM PS_SP_BOOKB3_CLSVW A WHERE> > > OPRCLASS='AMANLST' AND BUSINESS_UNIT=' ' ORDER BY BUSINESS_UNIT, BOOK
> > > FOR
> > > READ ONLY
>
> > > Here's the view:
>
> > > create view "sysadm".ps_sp_bookb3_clsvw
> > > (oprclass,business_unit,book,acct_ent_tmpl_id,
> > > required_sw,book_type,currency_cd,capitalization_min,lease_cap_min,distribution_sw,
> > > disposal_dist_sw,business_unit_gl,cal_depr_pd,rt_type,ledger_group,ledger,bud_ledger_group)
> > > as
> > > select x1.oprclass ,x0.business_unit ,x0.book ,x0.acct_ent_tmpl_id
> > > ,x0.required_sw ,x0.book_type ,x0.currency_cd ,x0.capitalization_min
> > > ,x0.lease_cap_min ,x0.distribution_sw ,x0.disposal_dist_sw
> > > ,x0.business_unit_gl ,x0.cal_depr_pd ,x0.rt_type ,x0.ledger_group
> > > ,x0.ledger ,x0.bud_ledger_group from "sysadm".ps_bu_book_tbl
> > > x0 ,"sysadm".ps_sp_book_clsvw x1 ,"sysadm".ps_set_cntrl_rec
> > > x2 ,"sysadm".ps_led_grp_tbl x3 ,"sysadm".ps_led_grp_led_tbl> > > x4 where (((((((((((x0.distribution_sw = 'Y' ) AND (x2.recname
> > > = 'LED_GRP_TBL' ) ) AND (x2.setcntrlvalue = x0.business_unit_gl
> > > ) ) AND (x3.setid = x2.setid ) ) AND (x3.ledger_group = x0.ledger_group
> > > ) ) AND (x4.setid = x3.setid ) ) AND (x4.ledger_group = x3.ledger_group
> > > ) ) AND ((((x3.ledgers_sync = 'Y' ) AND (x4.primary_ledger
> > > = 'Y' ) ) AND ((x4.ledger = x0.ledger ) OR (x0.ledger = ''
> > > ) ) ) OR ((x3.ledgers_sync != 'Y' ) AND (x4.ledger = x0.ledger
> > > ) ) ) ) AND (x1.setid = (select x5.setid from "sysadm".ps_set_cntrl_rec
> > > x5 where ((x5.recname = 'BOOK_DEFN_TBL' ) AND (x5.setcntrlvalue
> > > = x0.business_unit ) ) ) ) ) AND (x1.book = x0.book ) ) AND
> > > (x1.business_unit = x0.business_unit ) ) ;
>
> > What does onstat -g stk for the threads give? What flags are against
> > the session in onstat -u?
>
> onstat -g stk usually isn't very accurate when run against threads in> the "running" state (it can sometimes print a stack out for the
> thread, but the result is usually just the stack of what the thread
> had been doing the last time it yielded, not what it is currently
> doing, other times -g stk on a running thread just failes to unwind
> the stack at all and just gives things like *unknown* since the
> program counter, and stack pointer (tos) that is checked by the -g stk
> code isn't updated on the fly as the thread executes). To get an
> accurate stack for running threads you would be better off running the
> onmode -X stack <vp id> which will generate a stack into a file. To> find the vp the thread is executing on you can use onstat -g act and
> look at the tid column and the vp-class column..if it's on 1cpu then
> use 1 for vp id, if its on 4cpu then use 4 for the vp-id.
>
> As a side note, the onmode command to kill sessions doesn't actually
> kill the session, it just sets a kill flag that needs to be checked
> for by the session so it will then kill itself. If the session is in
> some sort of non yielding loop and it's stuck, then the onmode command
> to kill that session usually isn't effective, and unless the threat
> comes out of it's loop and checks the kill flag, you are stuck with
> forcing the server offline, or possibly forcing it to single user mode
> to try and kill the thread that way.
>
> Jacques- Hide quoted text -
>
> - Show quoted text -
If the thread is continuously running it could be moving between vps.
At least it is a start.
On Solaris you
>
> If the thread is continuously running it could be moving between vps.
> At least it is a start.
>
It's possible, it just would depend on what sort of loop it was in, if
it was in a loop that did a mt_yield call then yeah you might have
some sort of reasonable stack. However, if it's not in any sort of
loop that's doing a mt_yield, then it also wouldn't be migrating
across vps, so given the output of a couple onstat -g act -r 1
iterations, it would be easy enough to determine if the thread was
indead migrating vps or not.
> On Solaris you can also run pstack to get the stack for all threads in
> the vp.
>
> Not sure what the HP-UX equivalent is.
>
> Last I checked (10.x something) HP-UX didn't even have truss or
> strace.
>
> Yes I had tusc but that was not part of the normal OS build it was
> from some HP lab and not covered by a support contract.
> More like try this and if is goes not work tough!
>
> It would be interesting to hear what the HP-UX equivalent of truss and
> pstack are....
>
> Also what are the equivalent commands on the latest version of AIX?
I'm not familiar enough with HP to know if they have a pstack
equivalent. However, I believe AIX has a utility called procstack.
Jacques
Here is the stack from the onmode -X:
17:51:37 Stack for thread: 309 sqlexec
base: 0xc000000249ffd000
len: 69632
pc: 0x0000000000000000
tos: 0xc000000249ffecd0
state: running
vp: 3
( 0) 0x40000000008895e0 legacy_hp_afstack + 0x258
[/usr/informix/bin/oninit]
( 1) 0x4000000000888c1c afstack + 0x5c [/usr/informix/bin/oninit]
( 2) 0x4000000000888bb0 afstack_dump + 0x50 [/usr/informix/bin/oninit]
( 3) 0x400000000085bb10 notifyvp_signal_handler + 0x110
[/usr/informix/bin/o
ninit]
( 4) 0xc0000000004f8aa0 _sigreturn [/usr/lib/pa20_64/libc.2]
( 5) 0x40000000003e5ef8 mergepaths + 0x3a8 [/usr/informix/bin/oninit]
( 6) 0x40000000003e6020 opprune + 0xf0 [/usr/informix/bin/oninit]
( 7) 0x40000000003e7420 op_join + 0x358 [/usr/informix/bin/oninit]
( 8) 0x40000000003e8c98 op_phasejoin + 0x360 [/usr/informix/bin/oninit]
( 9) 0x400000000034a9cc sqoptim + 0x14dc [/usr/informix/bin/oninit]
(10) 0x400000000047f130 bldstructs + 0x148 [/usr/informix/bin/oninit]
(11) 0x400000000047eee8 sqcmd + 0x250 [/usr/informix/bin/oninit]
(12) 0x400000000047e9ec sq_cmnd + 0x94 [/usr/informix/bin/oninit]
(13) 0x400000000047eb10 sq_prepare + 0x40 [/usr/informix/bin/oninit]
"af.51d8c78" 36 lines, 1517 characters
jprenaut@yahoo.co
m
Sent by: To
informix-list-bou informix-list@iiug.org
nces@iiug.org cc
Subject
02/29/2008 05:40 Re: unkillable sid's
PM
>
> If the thread is continuously running it could be moving between vps.
> At least it is a start.
>
It's possible, it just would depend on what sort of loop it was in, if
it was in a loop that did a mt_yield call then yeah you might have
some sort of reasonable stack. However, if it's not in any sort of
loop that's doing a mt_yield, then it also wouldn't be migrating
across vps, so given the output of a couple onstat -g act -r 1
iterations, it would be easy enough to determine if the thread was
indead migrating vps or not.
> On Solaris you can also run pstack to get the stack for all threads in
> the vp.
>
> Not sure what the HP-UX equivalent is.
>
> Last I checked (10.x something) HP-UX didn't even have truss or
> strace.
>
> Yes I had tusc but that was not part of the normal OS build it was
> from some HP lab and not covered by a support contract.
> More like try this and if is goes not work tough!
>
> It would be interesting to hear what the HP-UX equivalent of truss and
> pstack are....
>
> Also what are the equivalent commands on the latest version of AIX?
I'm not familiar enough with HP to know if they have a pstack
equivalent. However, I believe AIX has a utility called procstack.
Jacques
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Feb 29, 4:58 pm, Darren_Jac...@carmax.com wrote:
> Here is the stack from the onmode -X:
>
> 17:51:37 Stack for thread: 309 sqlexec>
> base: 0xc000000249ffd000
> len: 69632
> pc: 0x0000000000000000
> tos: 0xc000000249ffecd0
> state: running
> vp: 3
>
> ( 0) 0x40000000008895e0 legacy_hp_afstack + 0x258
> [/usr/informix/bin/oninit]
> ( 1) 0x4000000000888c1c afstack + 0x5c [/usr/informix/bin/oninit]
> ( 2) 0x4000000000888bb0 afstack_dump + 0x50 [/usr/informix/bin/oninit]
> ( 3) 0x400000000085bb10 notifyvp_signal_handler + 0x110
> [/usr/informix/bin/o
> ninit]
> ( 4) 0xc0000000004f8aa0 _sigreturn [/usr/lib/pa20_64/libc.2]
> ( 5) 0x40000000003e5ef8 mergepaths + 0x3a8 [/usr/informix/bin/oninit]
> ( 6) 0x40000000003e6020 opprune + 0xf0 [/usr/informix/bin/oninit]
> ( 7) 0x40000000003e7420 op_join + 0x358 [/usr/informix/bin/oninit]
> ( 8) 0x40000000003e8c98 op_phasejoin + 0x360 [/usr/informix/bin/oninit]
> ( 9) 0x400000000034a9cc sqoptim + 0x14dc [/usr/informix/bin/oninit]
> (10) 0x400000000047f130 bldstructs + 0x148 [/usr/informix/bin/oninit]
> (11) 0x400000000047eee8 sqcmd + 0x250 [/usr/informix/bin/oninit]
> (12) 0x400000000047e9ec sq_cmnd + 0x94 [/usr/informix/bin/oninit]
> (13) 0x400000000047eb10 sq_prepare + 0x40 [/usr/informix/bin/oninit]
> "af.51d8c78" 36 lines, 1517 characters
>
Well xtree isn't showing anything because the query isn't doing
anything yet, it looks to be stuck in the query optimization phase. I
guess to verify that you'd want to look at a couple onmode -X stack
commands and if it is stuck in the optimizer the stuff below sqoptim
would be the same for sure, but there could be some variation in the
stack once you hit the op_phasejoin.
Are you trying to use any sort of optimizer directive? I don't see
one in the SQL statement but I can't recall if that gets stripped out
or not. I recall a problem with large complex queries (queries with
greater then 5 tables or so) that if you use optimizer directives it
can cause the optimizer to consider way too many join combinations
(when not using directives we detect this and start throwing out join
possiblities or something to that effect). I'm not sure you can do
this or not, but if possible, you could try executing the statement
"set optimization low;" immediately before this statement, and then
right after execute "set optimization high;" to switch back to the
normal level and that might get this query unstuck from the
optimizer. If you are using a directive, you could try removing the
directive 1st and see if that is ok, but if you need the directive, if
posisble you could try the set optimization statement to see if that
helps.
If you aren't using optimizer directives, you could still possibly try
the "set optimization low" idea, but I'm not sure it would help.
I guess it depends on if the optimizer is stuck in some competely
infinate loop during optimization, or if it's just considering lots
and lots of join paths and that's taking an extreme amount of time
where it doesn't yield, but eventually will determine a path and then
continue on.
Jacques
I'm not sure on the directive. It's a psoft process. I've tried to get my
financial systems guys to track the query to a process. They haven't
yet.... or they truly haven't tried.
I strongly suspect that this query, multiples running concurrently, has
actual caused the box to hang. Well, hang so to speak. Any process that
has a connection appears to run without issue. Any process attempting to
connect hangs without connecting. We can attempt dbaccess at the cli and
it never connects to the db. onstat cmds appear to work but onmode just
hangs. We eventually kill the oninit pids and restart.
I've just restarted the db and all is up and running fine.
Many thanks for the response. I found the info very helpful.
jprenaut@yahoo.co
m
Sent by: To
informix-list-bou informix-list@iiug.org
nces@iiug.org cc
Subject
02/29/2008 06:35 Re: unkillable sid's
PM
On Feb 29, 4:58 pm, Darren_Jac...@carmax.com wrote:
> Here is the stack from the onmode -X:
>
> 17:51:37 Stack for thread: 309 sqlexec>
> base: 0xc000000249ffd000
> len: 69632
> pc: 0x0000000000000000
> tos: 0xc000000249ffecd0
> state: running
> vp: 3
>
> ( 0) 0x40000000008895e0 legacy_hp_afstack + 0x258
> [/usr/informix/bin/oninit]
> ( 1) 0x4000000000888c1c afstack + 0x5c [/usr/informix/bin/oninit]
> ( 2) 0x4000000000888bb0 afstack_dump + 0x50
[/usr/informix/bin/oninit]
> ( 3) 0x400000000085bb10 notifyvp_signal_handler + 0x110
> [/usr/informix/bin/o
> ninit]
> ( 4) 0xc0000000004f8aa0 _sigreturn [/usr/lib/pa20_64/libc.2]
> ( 5) 0x40000000003e5ef8 mergepaths + 0x3a8 [/usr/informix/bin/oninit]
> ( 6) 0x40000000003e6020 opprune + 0xf0 [/usr/informix/bin/oninit]
> ( 7) 0x40000000003e7420 op_join + 0x358 [/usr/informix/bin/oninit]
> ( 8) 0x40000000003e8c98 op_phasejoin + 0x360
[/usr/informix/bin/oninit]
> ( 9) 0x400000000034a9cc sqoptim + 0x14dc [/usr/informix/bin/oninit]
> (10) 0x400000000047f130 bldstructs + 0x148 [/usr/informix/bin/oninit]
> (11) 0x400000000047eee8 sqcmd + 0x250 [/usr/informix/bin/oninit]
> (12) 0x400000000047e9ec sq_cmnd + 0x94 [/usr/informix/bin/oninit]
> (13) 0x400000000047eb10 sq_prepare + 0x40 [/usr/informix/bin/oninit]
> "af.51d8c78" 36 lines, 1517 characters
>
Well xtree isn't showing anything because the query isn't doing
anything yet, it looks to be stuck in the query optimization phase. I
guess to verify that you'd want to look at a couple onmode -X stack
commands and if it is stuck in the optimizer the stuff below sqoptim
would be the same for sure, but there could be some variation in the
stack once you hit the op_phasejoin.
Are you trying to use any sort of optimizer directive? I don't see
one in the SQL statement but I can't recall if that gets stripped out
or not. I recall a problem with large complex queries (queries with
greater then 5 tables or so) that if you use optimizer directives it
can cause the optimizer to consider way too many join combinations
(when not using directives we detect this and start throwing out join
possiblities or something to that effect). I'm not sure you can do
this or not, but if possible, you could try executing the statement
"set optimization low;" immediately before this statement, and then
right after execute "set optimization high;" to switch back to the
normal level and that might get this query unstuck from the
optimizer. If you are using a directive, you could try removing the
directive 1st and see if that is ok, but if you need the directive, if
posisble you could try the set optimization statement to see if that
helps.
If you aren't using optimizer directives, you could still possibly try
the "set optimization low" idea, but I'm not sure it would help.
I guess it depends on if the optimizer is stuck in some competely
infinate loop during optimization, or if it's just considering lots
and lots of join paths and that's taking an extreme amount of time
where it doesn't yield, but eventually will determine a path and then
continue on.
Jacques
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Feb 29, 5:57 pm, Darren_Jac...@carmax.com wrote:
> I'm not sure on the directive. It's a psoft process. I've tried to get my
> financial systems guys to track the query to a process. They haven't
> yet.... or they truly haven't tried.
>
> I strongly suspect that this query, multiples running concurrently, has
> actual caused the box to hang. Well, hang so to speak. Any process that
> has a connection appears to run without issue. Any process attempting to
> connect hangs without connecting. We can attempt dbaccess at the cli and
> it never connects to the db. onstat cmds appear to work but onmode just
> hangs. We eventually kill the oninit pids and restart.
It's certainly possible that if one of these sqlexec's that seem to be
getting stuck optimizing a query, got stuck on certain cpu vps, they
could definately impact the ability for new connections to be made,
and even for onmode commands to be processed by the engine. If you
had enough copies of the query spinning on all your cpu vps, your
server would pretty much be dead in the water.
>
> I've just restarted the db and all is up and running fine.
>
> Many thanks for the response. I found the info very helpful.
>
Darren_Jacobs@carmax.com wrote:
> I'm not sure on the directive. It's a psoft process. I've tried to get my
> financial systems guys to track the query to a process. They haven't
> yet.... or they truly haven't tried.
>
> I strongly suspect that this query, multiples running concurrently, has
> actual caused the box to hang. Well, hang so to speak. Any process that
> has a connection appears to run without issue. Any process attempting to
> connect hangs without connecting. We can attempt dbaccess at the cli and
> it never connects to the db. onstat cmds appear to work but onmode just
> hangs. We eventually kill the oninit pids and restart.
>
> I've just restarted the db and all is up and running fine.
Never mind about the onstat -g glo... ;-)
>
> Many thanks for the response. I found the info very helpful.
>
>
>
>
> jprenaut@yahoo.co
> m
> Sent by: To
> informix-list-bou informix-list@iiug.org
> nces@iiug.org cc
>
> Subject
> 02/29/2008 06:35 Re: unkillable sid's
> PM
>
>
>
>
>
>
>
>
>
> On Feb 29, 4:58 pm, Darren_Jac...@carmax.com wrote:
>> Here is the stack from the onmode -X:
>>
>> 17:51:37 Stack for thread: 309 sqlexec>>
>> base: 0xc000000249ffd000
>> len: 69632
>> pc: 0x0000000000000000
>> tos: 0xc000000249ffecd0
>> state: running
>> vp: 3
>>
>> ( 0) 0x40000000008895e0 legacy_hp_afstack + 0x258
>> [/usr/informix/bin/oninit]
>> ( 1) 0x4000000000888c1c afstack + 0x5c [/usr/informix/bin/oninit]
>> ( 2) 0x4000000000888bb0 afstack_dump + 0x50
> [/usr/informix/bin/oninit]
>> ( 3) 0x400000000085bb10 notifyvp_signal_handler + 0x110
>> [/usr/informix/bin/o
>> ninit]
>> ( 4) 0xc0000000004f8aa0 _sigreturn [/usr/lib/pa20_64/libc.2]
>> ( 5) 0x40000000003e5ef8 mergepaths + 0x3a8 [/usr/informix/bin/oninit]
>> ( 6) 0x40000000003e6020 opprune + 0xf0 [/usr/informix/bin/oninit]
>> ( 7) 0x40000000003e7420 op_join + 0x358 [/usr/informix/bin/oninit]
>> ( 8) 0x40000000003e8c98 op_phasejoin + 0x360
> [/usr/informix/bin/oninit]
>> ( 9) 0x400000000034a9cc sqoptim + 0x14dc [/usr/informix/bin/oninit]
>> (10) 0x400000000047f130 bldstructs + 0x148 [/usr/informix/bin/oninit]
>> (11) 0x400000000047eee8 sqcmd + 0x250 [/usr/informix/bin/oninit]
>> (12) 0x400000000047e9ec sq_cmnd + 0x94 [/usr/informix/bin/oninit]
>> (13) 0x400000000047eb10 sq_prepare + 0x40 [/usr/informix/bin/oninit]
>> "af.51d8c78" 36 lines, 1517 characters
>>
>
> Well xtree isn't showing anything because the query isn't doing
> anything yet, it looks to be stuck in the query optimization phase. I
> guess to verify that you'd want to look at a couple onmode -X stack
> commands and if it is stuck in the optimizer the stuff below sqoptim
> would be the same for sure, but there could be some variation in the
> stack once you hit the op_phasejoin.
>
> Are you trying to use any sort of optimizer directive? I don't see
> one in the SQL statement but I can't recall if that gets stripped out
> or not. I recall a problem with large complex queries (queries with
> greater then 5 tables or so) that if you use optimizer directives it
> can cause the optimizer to consider way too many join combinations
> (when not using directives we detect this and start throwing out join
> possiblities or something to that effect). I'm not sure you can do
> this or not, but if possible, you could try executing the statement
> "set optimization low;" immediately before this statement, and then
> right after execute "set optimization high;" to switch back to the
> normal level and that might get this query unstuck from the
> optimizer. If you are using a directive, you could try removing the
> directive 1st and see if that is ok, but if you need the directive, if
> posisble you could try the set optimization statement to see if that
> helps.
>
> If you aren't using optimizer directives, you could still possibly try
> the "set optimization low" idea, but I'm not sure it would help.
>
> I guess it depends on if the optimizer is stuck in some competely
> infinate loop during optimization, or if it's just considering lots
> and lots of join paths and that's taking an extreme amount of time
> where it doesn't yield, but eventually will determine a path and then
> continue on.
>
> Jacques
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>