Re: unkillable sid's
BTW, onstat cmds are slow to respond. Which could be a symptom of high
CPU. 8 way box with all at or near 100%. Which is pretty norm when we go
into close periods.
$ onstat -g stk 309
IBM Informix Dynamic Server Version 9.40.FC2XA -- On-Line -- Up 3 days
12:17:1
8 -- 11580348 Kbytes
Stack for thread: 309 sqlexec
base: 0xc000000249ffd000
len: 69632
pc: 0x0000000000000000
tos: 0xc000000249ffeae0state: running
vp: 3
0x40000000008610b0 oninit :: resume + 0x0 sp=0xc000000249ffeae0
0x0 ?unknown? :: ?unknown? + 0x0 sp=0xc000000249ffe9b0 delta_sp=304
$ osu|grep 194
c0000002488914a0 ---P--- 194 sysadm FSAPP2P 0 0 3223737 42713
"david@smooth1.co
.uk"
<david@smooth1.co To
.uk> informix-list@iiug.org
Sent by: cc
informix-list-bou
nces@iiug.org Subject
Re: unkillable sid's
02/29/2008 04:16
PM
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?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Darren_Jacobs@carmax.com wrote:
> BTW, onstat cmds are slow to respond. Which could be a symptom of high
> CPU. 8 way box with all at or near 100%. Which is pretty norm when we go
> into close periods.
Check out onstat -g glo. Check to see if one of the CPUVPs looks way
out of line. If it is, then that's a clue of a run-away thread running
on that VP. If you don't see that, and you have an 8 way box and
onstat's running slow, then that's an indication that there might be
some other issue at play --- maybe a screaming interrupt or something
like that. Unless you have run-away threads running on all of the VPs,
I wouldn't expect onstat to be sluggish, since it runs outside of the
server.
>
>
>
> $ onstat -g stk 309>
> IBM Informix Dynamic Server Version 9.40.FC2XA -- On-Line -- Up 3 days
> 12:17:1
> 8 -- 11580348 Kbytes>
> Stack for thread: 309 sqlexec
> base: 0xc000000249ffd000
> len: 69632
> pc: 0x0000000000000000
> tos: 0xc000000249ffeae0> state: running
> vp: 3
>
> 0x40000000008610b0 oninit :: resume + 0x0 sp=0xc000000249ffeae0
> 0x0 ?unknown? :: ?unknown? + 0x0 sp=0xc000000249ffe9b0 delta_sp=304
>
> $ osu|grep 194
> c0000002488914a0 ---P--- 194 sysadm FSAPP2P 0 0 3> 223737 42713
>
>
>
>
> "david@smooth1.co
> .uk"
> <david@smooth1.co To
> .uk> informix-list@iiug.org
> Sent by: cc
> informix-list-bou
> nces@iiug.org Subject
> Re: unkillable sid's
>
> 02/29/2008 04:16
> PM
>
>
>
>
>
>
>
> 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,distribu'tion_sw,
>
> disposal_dist_sw,business_unit_gl,cal_depr_pd,rt_type,ledger_group,ledger,b'ud_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?
> _______________________________________________
> I