unkillable sid's
A DBA on IDS 9.40.FC2XA (HP-UX 11.11) reported three PeopleSoft sessions that could not be killed; all were running the same SELECT against a complex multi-table view (PS_SP_BOOKB3_CLSVW), showed as 'running' sqlexec threads, and an xtree/session trace returned nothing. He suspected these queries of causing server hangs and performance problems and asked how to get rid of them. The only reply asked for diagnostics - output of 'onstat -g stk' for the threads and the session flags from 'onstat -u'. No answer or resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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, BOOKFOR
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, BOOKFOR
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 ) ) ;
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?