SET INDEXES ... ENABLED very slow

We are running IDS 7.30 on HPUX 10.20, IDS 9.21 on HPUX 11.00. The later one is several times slower on SET INDEXES ... ENABLE (the machine is more powerful, the load is almost none, database schema and data are identical). Does the output of onstat -g ses ..., onstat -g con have something common with this problem? I encountered the xchg threads are almost permanently in status cond wait (bufcond, freecond), contrary to 7.30. Any recommendation? onstat -g ses ... on 9.21 Informix Dynamic Server 2000 Version 9.21.FC1 -- On-Line -- Up 12 days 12:01:14 -- 379948 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 1462 aris - 599 kosatka. 4 360448 356048 tid name rstcb flags curstk status 5234 sqlexec c0000000051ea028 Y-BP--- 123856 c0000000051ea028cond wait(opened_up) 5774 xchg_1.0 c0000000051e7028 Y-B---- 132520 c0000000051e7028cond wait(opened_up) 5775 xchg_2.0 c0000000051e8828 Y-B---- 132112 c0000000051e8828cond wait(bufcond) 5776 xchg_3.0 c0000000051e6028 Y-B-R-- 132632 c0000000051e6028cond wait(free_cond) Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 1462 V c000000005d99040 315392 1904 2698 3 1462_SORT_0 V c000000005f04040 45056 2496 76 1 name free used name free used overhead 0 6512 scb 0 144 opentable 0 17464 filetable 0 5544 ru 0 280 misc 0 776 log 0 8672 temprec 0 10992 keys 0 2336 ralloc 0 34976 gentcb 0 3640 ostcb 0 3416 sort 0 39176 sqscb 0 196992 sql 0 72 srtmembuf 0 120 rdahead 0 392 xchg_desc 0 1456 xchg_port 0 1000 xchg_packet 0 384 xchg_group 0 552 xchg_priv 0 456 scan_desc 0 1296 sort_desc 0 1712 btmrg_desc 0 584 hashfiletab 0 2208 osenv 0 4168 sqtcb 0 8664 fragman 0 1064 light_scan 0 96 lt_scan_rbuff 0 176 lt_scan_bufs 0 64 shmblklist 0 664 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 1462 SET OBJMODE aris_v CR Not Wait 0 0 9.03 Current SQL statement : SET INDEXES ac_memobody_den_f2 ENABLED; Last parsed SQL statement : SET INDEXES ac_memobody_den_f2 ENABLED; 1024 byte(s) of memory is allocated from the sscpool onstat -g ses ... on 7.30 Informix Dynamic Server Version 7.30.UC9 -- On-Line -- Up 32 days 18:44:43 -- 526744 Kb session #RSAM total used id user tty pid hostname threads memory memory 2938 aris - 1496 kosatka 6 196608 169312 tid name rstcb flags curstk status 6360 sqlexec c45c31e4 Y-BP--- 33064 c45c31e4 cond wait (opened_up) 6361 xchg_1.0 c45c0c44 Y-B---- 35752 c45c0c44 cond wait (packet_cond) 6362 xchg_1.1 c45bfe28 --B---- 35752 c45bfe28 ready 6363 xchg_1.2 c45c44b4 --B---- 35752 c45c44b4 ready 6364 xchg_2.0 c45c4968 --B-R-- 35752 c45c4968 ready 6365 xchg_2.1 c45beb58 --B-R-- 35776 c45beb58 running Memory pools count 4 name class addr totalsize freesize #allocfrag #freefrag 2938 V c6766018 98304 1760 538 1 2938_SORT_0 V c69fe018 32768 7968 140 1 2938_SORT_0 V c6aac018 32768 8744 121 2 2938_SORT_0 V c6ac0018 32768 8784 120 2 name free used name free used overhead 0 512 scb 0 96 opentable 0 5600 filetable 0 2440 ru 0 224 misc 0 1968 log 0 12912 temprec 0 7128 keys 0 264 ralloc 0 23880 gentcb 0 15016 ostcb 0 2160 sort 0 50888 sqscb 0 7768 srtmembuf 0 7800 rdahead 0 256 xchg_desc 0 904 xchg_port 0 448 xchg_packet 0 384 xchg_group 0 256 xchg_priv 0 320 scan_desc 0 1280 sort_desc 0 1216 btmrg_desc 0 192 hashfiletab 0 1680 osenv 0 3360 sqtcb 0 4840 fragman 0 240 light_scan 0 144 lt_scan_rbuff 0 224 lt_scan_bufs 0 80 shmblklist 0 14872 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 2938 SET OBJMODE aris_v CR Not Wait 0 0 7.30 Current SQL statement : SET INDEXES xrd_outbody_den_u2 ENABLED Sent via Deja.com http://www.deja.com/ Before you buy.

I'd guess you do not have the PSORT_ environment vars set for fastest index rebuilds. You show multiple exchange threads, indicating PDQPRIORITY greater than zero for a parallel sort but there is only one sort memory block indicating that PSORT is not enabled. Is it possible that you DID have them set on the HPUX-10/IDS7.30 system? Art S. Kagel roman.daniel@tconsult.cz wrote: > > We are running IDS 7.30 on HPUX 10.20, IDS 9.21 on HPUX 11.00. The > later one > is several times slower on SET INDEXES ... ENABLE (the machine is more > powerful, the load is almost none, database schema and data are > identical). > Does the output of onstat -g ses ..., onstat -g con have something > common with > this problem? I encountered the xchg threads are almost permanently in > status cond wait (bufcond, freecond), contrary to 7.30. Any > recommendation? > > onstat -g ses ... on 9.21> > Informix Dynamic Server 2000 Version 9.21.FC1 -- On-Line -- Up 12 > days 12:01:14 -- 379948 Kbytes > > session #RSAM total used > id user tty pid hostname threads memory memory > 1462 aris - 599 kosatka. 4 360448 356048 > > tid name rstcb flags curstk status > 5234 sqlexec c0000000051ea028 Y-BP--- 123856 > c0000000051ea028cond wait(opened_up) > 5774 xchg_1.0 c0000000051e7028 Y-B---- 132520 > c0000000051e7028cond wait(opened_up) > 5775 xchg_2.0 c0000000051e8828 Y-B---- 132112 > c0000000051e8828cond wait(bufcond) > 5776 xchg_3.0 c0000000051e6028 Y-B-R-- 132632 > c0000000051e6028cond wait(free_cond) > > Memory pools count 2 > name class addr totalsize freesize #allocfrag > #freefrag > 1462 V c000000005d99040 315392 1904 2698 3 > 1462_SORT_0 V c000000005f04040 45056 2496 76 1 > > name free used name free used > overhead 0 6512 scb 0 144 > opentable 0 17464 filetable 0 5544 > ru 0 280 misc 0 776 > log 0 8672 temprec 0 10992 > keys 0 2336 ralloc 0 34976 > gentcb 0 3640 ostcb 0 3416 > sort 0 39176 sqscb 0 > 196992 > sql 0 72 srtmembuf 0 120 > rdahead 0 392 xchg_desc 0 1456 > xchg_port 0 1000 xchg_packet 0 384 > xchg_group 0 552 xchg_priv 0 456 > scan_desc 0 1296 sort_desc 0 1712 > btmrg_desc 0 584 hashfiletab 0 2208 > osenv 0 4168 sqtcb 0 8664 > fragman 0 1064 light_scan 0 96 > lt_scan_rbuff 0 176 lt_scan_bufs 0 64 > shmblklist 0 664 > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 1462 SET OBJMODE aris_v CR Not Wait 0 0 9.03 > > Current SQL statement : > SET INDEXES ac_memobody_den_f2 ENABLED; > > Last parsed SQL statement : > SET INDEXES ac_memobody_den_f2 ENABLED; > > 1024 byte(s) of memory is allocated from the sscpool > > onstat -g ses ... on 7.30> > Informix Dynamic Server Version 7.30.UC9 -- On-Line -- Up 32 days > 18:44:43 -- 526744 Kb > > session #RSAM total used > id user tty pid hostname threads memory memory > 2938 aris - 1496 kosatka 6 196608 169312 > > tid name rstcb flags curstk status > 6360 sqlexec c45c31e4 Y-BP--- 33064 c45c31e4 cond wait > (opened_up) > 6361 xchg_1.0 c45c0c44 Y-B---- 35752 c45c0c44 cond wait > (packet_cond) > 6362 xchg_1.1 c45bfe28 --B---- 35752 c45bfe28 ready > 6363 xchg_1.2 c45c44b4 --B---- 35752 c45c44b4 ready > 6364 xchg_2.0 c45c4968 --B-R-- 35752 c45c4968 ready > 6365 xchg_2.1 c45beb58 --B-R-- 35776 c45beb58 running > > Memory pools count 4 > name class addr totalsize freesize #allocfrag #freefrag > 2938 V c6766018 98304 1760 538 1 > 2938_SORT_0 V c69fe018 32768 7968 140 1 > 2938_SORT_0 V c6aac018 32768 8744 121 2 > 2938_SORT_0 V c6ac0018 32768 8784 120 2 > > name free used name free used > overhead 0 512 scb 0 96 > opentable 0 5600 filetable 0 2440 > ru 0 224 misc 0 1968 > log 0 12912 temprec 0 7128 > keys 0 264 ralloc 0 23880 > gentcb 0 15016 ostcb 0 2160 > sort 0 50888 sqscb 0 7768 > srtmembuf 0 7800 rdahead 0 256 > xchg_desc 0 904 xchg_port 0 448 > xchg_packet 0 384 xchg_group 0 256 > xchg_priv 0 320 scan_desc 0 1280 > sort_desc 0 1216 btmrg_desc 0 192 > hashfiletab 0 1680 osenv 0 3360 > sqtcb 0 4840 fragman 0 240 > light_scan 0 144 lt_scan_rbuff 0 224 > lt_scan_bufs 0 80 shmblklist 0 14872 > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 2938 SET OBJMODE aris_v CR Not Wait 0 0 7.30 > > Current SQL statement : > SET INDEXES xrd_outbody_den_u2 ENABLED > > Sent via Deja.com http://www.deja.com/ > Before you buy.