I cannot calculate accurate metrics for you, as you did not post the time since
stats were zero'd and I remember you saying you did zero them at some point.
The guestimated numbers look OK, though. Otherwise everything in the tuning
dept looks OK from these output.
I do see very extensive tempdbspace usage which would indicate that the poorly
performing apps are using temp tables. That COULD be a difference in the way
the 7.3x and 9.xx optimizers are handling certain complex queries. You should
turn dynamic SET EXPLAIN on for the app (onmode -Y <sid> 1) and see what's doing
with the queries. Perhaps the change in OPTCOMPIND from 1 to 0 will help by
eliminating HASH table creation overhead, but other things may be going on also.
Also one partition seems to be dominating the buffer cache: partnum 15728642
with 690224 pages. This could be a red herring caused by the time of day you
ran the onstat -P (analysis of onstat -P works best during typical load or peak
load or be comparing the two). Reports taken at off hours can show dominance
simply because some partition was the last one that was extensively accessed
before the server became quiet.
Art S. Kagel
----- Original Message -----
From: Doug Fossmeyer <ids@iiug.org>
At: 6/12 20:06:08
This is embarrassing, our email server (groupwise is configured to limit email
to 30 k. I am having to post my issue to the list serve with that limitation,
meaning that to post the onstat and dbschema requires 3, yes 3 emails.
I hope someone can evaluate the content and provide some guidance. Aside from
upgrading to FC7 we are at a loss as to why the large batch processes are
taking so long. Recap: I zero'd out the statistics just before running the
process. Nothing else is running at the same time. Our issue is that after the
upgrade most OLTP and small batch processes are running fine, the processes
with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS
94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport,
install seq Tools, Engine, Network steps, dbimport, manually update stats of
system tables, update stats using dostats.
Thanks in advance, Doug
Part 1
onstat -p
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:51:54
-- 2325280 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
1692924 5680780 2148702238 99.92 98673 478066 9326835 98.94
isamtot open start read write rewrite delete commit rollbk
1403470631 5711631 27131297 1288458477 714047 182811 21956 192676 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 25339.64 948.51 217 1608
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
38861 0 1361203201 0 0 77 374795 1127856
ixda-RA idx-RA da-RA RA-pgsused lchwaits
483892 201 25013 509073 4265
onstat -d
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:11
-- 2325280 Kbytes
Dbspaces
address number flags fchunk nchunks flags owner name
c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs
c00000008f581ce8 2 0x1 2 1 N informix syscat94
c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941
c00000008f584028 4 0x20001 13 11 N informix ifashrpy
c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94
c00000008f584328 6 0x2001 24 1 N T informix tempdbs6
c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7
c00000008f584628 8 0x2001 26 1 N T informix tempdbs8
c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9
c00000008f584928 10 0x20001 28 1 N informix tempdbs10
c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941
c00000008f584c28 12 0x20001 40 1 N informix plogdbs94
c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp
c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt
c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2
15 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
c00000008e3fc028 1 1 0 15000 2826 PO-- /dbms/links/rootdbs9
c00000008f57d710 2 2 0 1023744 966451 PO-- /dbms/links/syscatdbs94
c00000008f57d8a8 3 3 0 1023744 3513 PO-- /dbms/links/ifasnrdbs941
c00000008f57da40 4 3 256 1023744 3 PO-- /dbms/links/ifasnrdbs942
c00000008f57dbd8 5 3 256 1023744 2029 PO-- /dbms/links/ifasnrdbs943
c00000008f57dd70 6 3 256 1023744 27788 PO-- /dbms/links/ifasnrdbs944
c00000008f57e028 7 3 256 1023744 53819 PO-- /dbms/links/ifasnrdbs945
c00000008f57e1c0 8 3 256 1023744 17378 PO-- /dbms/links/ifasnrdbs946
c00000008f57e358 9 3 256 1023744 235004 PO-- /dbms/links/ifasnrdbs947
c00000008f57e4f0 10 3 256 1023744 1023441 PO-- /dbms/links/ifasnrdbs948
c00000008f57e688 11 3 256 1023744 1023241 PO-- /dbms/links/ifasnrdbs949
c00000008f57e820 12 3 256 1023744 1023741 PO-- /dbms/links/ifasnrdbs9410
c00000008f57e9b8 13 4 0 1023744 8 PO-- /dbms/links/ifashrpy1
c00000008f57eb50 14 4 256 1023744 0 PO-- /dbms/links/ifashrpy2
c00000008f57ece8 15 4 256 1023744 89 PO-- /dbms/links/ifashrpy3
c00000008f57ee80 16 4 256 1023744 470 PO-- /dbms/links/ifashrpy4
c00000008f57f028 17 4 256 1023744 2 PO-- /dbms/links/ifashrpy5
c00000008f57f1c0 18 4 256 1023744 9 PO-- /dbms/links/ifashrpy6
c00000008f57f358 19 4 256 1023744 6 PO-- /dbms/links/ifashrpy7
c00000008f57f4f0 20 4 256 1023744 3 PO-- /dbms/links/ifashrpy9
c00000008f57f688 21 4 256 1023744 552910 PO-- /dbms/links/ifashrpy10
c00000008f57f820 22 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy11
c00000008f57f9b8 23 5 0 511872 51819 PO-- /dbms/links/llogdbs94
c00000008f57fb50 24 6 256 1023744 1021639 PO-- /dbms/links/tempdbs6
c00000008f57fce8 25 7 256 1023744 1023583 PO-- /dbms/links/tempdbs7
c00000008f57fe80 26 8 256 1023744 1023591 PO-- /dbms/links/tempdbs8
c00000008f580028 27 9 256 1023744 1023591 PO-- /dbms/links/tempdbs9
c00000008f5801c0 28 10 256 1023744 1023675 PO-- /dbms/links/tempdbs10
c00000008f580358 29 11 0 1023744 3 PO-- /dbms/links/ifasdevdbs1
c00000008f5804f0 30 11 256 1023744 3817 PO-- /dbms/links/ifasdevdbs2
c00000008f580688 31 11 256 1023744 1276 PO-- /dbms/links/ifasdevdbs3
c00000008f580820 32 11 256 1023744 25494 PO-- /dbms/links/ifasdevdbs4
c00000008f5809b8 33 11 256 1023744 22984 PO-- /dbms/links/ifasdevdbs5
c00000008f580b50 34 11 256 1023744 143 PO-- /dbms/links/ifasdevdbs6
c00000008f580ce8 35 11 256 1023744 0 PO-- /dbms/links/ifasdevdbs7
c00000008f580e80 36 11 256 1023744 14017 PO-- /dbms/links/ifasdevdbs8
c00000008f581028 37 11 256 1023744 133739 PO-- /dbms/links/ifasdevdbs9
c00000008f5811c0 38 11 256 1023744 948330 PO-- /dbms/links/ifasdevdbs10
c00000008f581358 39 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy12
c00000008f5814f0 40 12 0 63750 197 PO-- /dbms/links/plogdbs94
c00000008f581688 41 13 256 1023744 1023691 PO-- /dbms/links/ifasdbs94temp
c00000008f581820 42 14 256 1023744 1004580 PO-- /dbms/links/ifasdev2dbspyt
c00000008f5819b8 43 15 256 1023744 634273 PO-- /dbms/links/ifasdev2dbspyt2
c00000008f581b50 44 15 256 1023744 0 PO-- /dbms/links/ifasdev2dbspyt3
44 active, 2047 maximum
Expanded chunk capacity mode: disabled
onstat -D
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:43
-- 2325280 Kbytes
Dbspaces
address number flags fchunk nchunks flags owner name
c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs
c00000008f581ce8 2 0x1 2 1 N informix syscat94
c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941
c00000008f584028 4 0x20001 13 11 N informix ifashrpy
c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94
c00000008f584328 6 0x2001 24 1 N T informix tempdbs6
c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7
c00000008f584628 8 0x2001 26 1 N T informix tempdbs8
c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9
c00000008f584928 10 0x20001 28 1 N informix tempdbs10
c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941
c00000008f584c28 12 0x20001 40 1 N informix plogdbs94
c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp
c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt
c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2
15 active, 2047 maximum
Chunks
address chunk/dbs offset page Rd page Wr pathname
c00000008e3fc028 1 1 0 481 2063 /dbms/links/rootdbs9
c00000008f57d710 2 2 0 900 112 /dbms/links/syscatdbs94
c00000008f57d8a8 3 3 0 194832 241 /dbms/links/ifasnrdbs941
c00000008f57da40 4 3 256 566 20 /dbms/links/ifasnrdbs942
c00000008f57dbd8 5 3 256 15582 667 /dbms/links/ifasnrdbs943
c00000008f57dd70 6 3 256 18903 12243 /dbms/links/ifasnrdbs944
c00000008f57e028 7 3 256 26 5 /dbms/links/ifasnrdbs945
c00000008f57e1c0 8 3 256 8324 6 /dbms/links/ifasnrdbs946
c00000008f57e358 9 3 256 43320 77 /dbms/links/ifasnrdbs947
c00000008f57e4f0 10 3 256 0 0 /dbms/links/ifasnrdbs948
c00000008f57e688 11 3 256 1 4 /dbms/links/ifasnrdbs949
c00000008f57e820 12 3 256 0 0 /dbms/links/ifasnrdbs9410
c00000008f57e9b8 13 4 0 5 0 /dbms/links/ifashrpy1
c00000008f57eb50 14 4 256 0 0 /dbms/links/ifashrpy2
c00000008f57ece8 15 4 256 0 0 /dbms/links/ifashrpy3
c00000008f57ee80 16 4 256 0 0 /dbms/links/ifashrpy4
c00000008f57f028 17 4 256 0 0 /dbms/links/ifashrpy5
c00000008f57f1c0 18 4 256 0 0 /dbms/links/ifashrpy6
c00000008f57f358 19 4 256 8 0 /dbms/links/ifashrpy7
c00000008f57f4f0 20 4 256 0 0 /dbms/links/ifashrpy9
c00000008f57f688 21 4 256 0 0 /dbms/links/ifashrpy10
c00000008f57f820 22 4 256 0 0 /dbms/links/ifashrpy11
c00000008f57f9b8 23 5 0 0 78681 /dbms/links/llogdbs94
c00000008f57fb50 24 6 256 97045 117122 /dbms/links/tempdbs6
c00000008f57fce8 25 7 256 82884 83875 /dbms/links/tempdbs7
c00000008f57fe80 26 8 256 68811 69825 /dbms/links/tempdbs8
c00000008f580028 27 9 256 68298 69322 /dbms/links/tempdbs9
c00000008f5801c0 28 10 256 5 58 /dbms/links/tempdbs10
c00000008f580358 29 11 0 0 0 /dbms/links/ifasdevdbs1
c00000008f5804f0 30 11 256 0 0 /dbms/links/ifasdevdbs2
c00000008f580688 31 11 256 0 0 /dbms/links/ifasdevdbs3
c00000008f580820 32 11 256 0 0 /dbms/links/ifasdevdbs4
c00000008f5809b8 33 11 256 0 0 /dbms/links/ifasdevdbs5
c00000008f580b50 34 11 256 0 0 /dbms/links/ifasdevdbs6
c00000008f580ce8 35 11 256 0 0 /dbms/links/ifasdevdbs7
c00000008f580e80 36 11 256 0 0 /dbms/links/ifasdevdbs8
c00000008f581028 37 11 256 0 0 /dbms/links/ifasdevdbs9
c00000008f5811c0 38 11 256 0 0 /dbms/links/ifasdevdbs10
c00000008f581358 39 4 256 0 0 /dbms/links/ifashrpy12
c00000008f5814f0 40 12 0 0 15399 /dbms/links/plogdbs94
c00000008f581688 41 13 256 4 8 /dbms/links/ifasdbs94temp
c00000008f581820 42 14 256 3844 24807 /dbms/links/ifasdev2dbspyt
c00000008f5819b8 43 15 256 966987 3563 /dbms/links/ifasdev2dbspyt2
c00000008f581b50 44 15 256 4110741 0 /dbms/links/ifasdev2dbspyt3
44 active, 2047 maximum
Expanded chunk capacity mode: disabled
onstat -P
IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:53:16
-- 2325280 Kbytes
partnum total btree data other dirty
0 138 0 0 138 4
1048578 3 1 1 1 0
1048579 9 5 4 0 0
1048580 4 2 1 1 0
1048581 3 1 1 1 0
1048582 4 2 1 1 0
1048583 3 1 1 1 0
1048585 4 1 1 2 0
1048598 2 1 0 1 0
1048599 2 1 0 1 0
1048602 5 2 2 1 0
1048603 2 1 0 1 0
1048608 3 1 0 2 0
1048613 3 1 1 1 0
1048615 3 1 1 1 0
1048623 1 0 0 1 0
1048624 4 3 0 1 0
2097201 10 4 6 0 0
2097207 2 1 1 0 0
2097224 94 16 78 0 0
2097250 178 0 178 0 0
2097251 8 8 0 0 0
2097252 44 44 0 0 0
2097254 3 3 0 0 0
2097260 1 0 1 0 0
2097261 1 1 0 0 0
2097265 81 0 81 0 0
2097266 6 6 0 0 0
2097267 32 32 0 0 0
2097277 6 0 6 0 0
2097281 11 11 0 0 0
2097284 145 0 145 0 0
2097287 5 5 0 0 0
2097293 29 0 29 0 0
2097294 13 13 0 0 0
2097295 3 3 0 0 0
2097358 20 0 20 0 0
2097359 3 3 0 0 0
2097360 6 5 0 1 0
3145730 107 46 59 2 2
3145731 66 22 42 2 2
3145732 41 12 27 2 1
3145733 16 8 6 2 1
3145734 7 3 2 2 1
3145735 8 3 3 2 2
3145736 3 1 1 1 0
3145737 5 2 1 2 1
3145739 1 1 0 0 0
3145740 7 3 4 0 0
3145743 5 3 2 0 0
3145744 7 5 2 0 0
3145745 10 6 4 0 0
3145746 4 2 2 0 0
3145747 3 3 0 0 0
3145748 4 3 1 0 0
3145749 1 1 0 0 0
3145750 1 1 0 0 0
3145751 4 3 1 0 0
3145752 5 3 2 0 0
3145753 291 76 213 2 1
3145754 10 4 4 2 1
3145755 1 1 0 0 0
3145756 4 2 1 1 0
3145759 1 1 0 0 0
3145760 1 1 0 0 0
3145762 3 2 1 0 0
3145764 2 1 1 0 0
3145765 1 1 0 0 0
3145771 1 1 0 0 0
3145774 25 20 3 2 0
3145775 3 3 0 0 0
3145777 81 0 81 0 0
3145778 12 12 0 0 0
3145779 1 0 1 0 0
3146110 1 0 1 0 0
3146149 2031 0 2031 0 0
3146188 5 0 5 0 0
3146191 5 5 0 0 0
3146228 540 0 537 3 0
3146703 4 0 4 0 0
3146706 13 13 0 0 0
3146794 1 0 1 0 0
3146796 2 2 0 0 0
3147073 1 0 1 0 0
3147076 9 0 8 1 0
3147077 1 1 0 0 0
3147084 131 0 130 1 0
3147086 3 3 0 0 0
3147087 4 4 0 0 0
3147089 2 0 2 0 0
3147091 4 4 0 0 0
3147099 1 0 1 0 0
3147100 27 26 0 1 0
3147101 2 2 0 0 0
3147103 125 0 124 1 0
3147105 2 2 0 0 0
3147111 1 1 0 0 0
3147113 23 0 23 0 0
3147114 23 23 0 0 0
3147116 2 0 2 0 0
3147117 3 3 0 0 0
3147118 3 3 0 0 0
3147120 14 0 14 0 0
3147121 9 9 0 0 0
3147123 31 0 31 0 0
3147124 21 21 0 0 0
3147125 14 14 0 0 0
3147131 7 0 7 0 0
3147132 10 10 0 0 0
3147135 4 0 4 0 0
3147136 5 5 0 0 0
3147144 1 0 1 0 0
3147145 2 2 0 0 0
3147150 6 0 5 1 0
3147151 1 1 0 0 0
3147169 1 0 1 0 0
3147183 5426 0 5426 0 0
3147203 1786 1786 0 0 0
3147205 1 1 0 0 0
3147208 19100 0 19100 0 0
3147211 1632 0 1632 0 0
3147214 69 69 0 0 0
3147230 62 0 61 1 0
3147233 10 0 10 0 0
3147332 94137 0 94137 0 0
3147344 3 0 2 1 0
3147348 1 0 0 1 0
3147355 5 0 4 1 0
3147356 8 0 7 1 0
3147480 1 0 1 0 0
3147481 3 3 0 0 0
3147488 3 3 0 0 0
3147651 2 0 2 0 0
3147654 3 3 0 0 0
3147834 1 0 1 0 0
3148051 1 0 1 0 0
3148123 8 0 6 2 0
3148124 1 1 0 0 0
3148205 673 0 672 1 0
3148209 3 3 0 0 0
3148369 624 0 624 0 0
3148471 85 85 0 0 0
3148489 3 3 0 0 0
3148502 598 598 0 0 0
3148506 11 11 0 0 0
3148521 96 96 0 0 0
3148526 4897 4897 0 0 0
3148527 2600 2600 0 0 0
3148559 6948 6945 0 3 0
3148560 2099 2070 0 29 0
3148561 9683 9636 0 47 0
4194496 2 0 1 1 0
4194498 8 0 7 1 0
4195008 12 7 5 0 0
4195015 2 1 1 0 0
4195032 4 3 1 0 0
6291457 2 0 0 2 1
6291458 30238 0 30230 8 0
6291459 1 0 0 1 0
6291460 1 0 0 1 0
6291461 1 0 0 1 0
....snip...
9437327 1 0 0 1 0
9437328 1 0 0 1 0
9437329 1 0 0 1 0
9437330 1 0 0 1 0
9437331 1 0 0 1 0
9437332 1 0 0 1 0
9437333 1 0 0 1 0
10485761 1 0 0 1 0
10485762 6 0 4 2 0
10485763 6 4 0 2 0
13631489 1 0 0 1 0
13631490 3 0 1 2 0
14680066 9475 0 9470 5 288
14680067 4086 4061 0 25 23
14680068 478 476 0 2 11
14680069 811 809 0 2 3
15728641 2 0 0 2 0
15728642 690224 0 690224 0 0
15728644 3216 3216 0 0 0
15728649 3549 3545 0 4 0
Totals: 900000 41525 857540 935 395
Percentages:
Data 95.28
Btree 4.61
Other 0.10
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
