First, thanks in advance for any help you may provide.
We have a vendor supplied 4gl process that has doubled in time since an upgrade to IDS 9.4fc8, tools7.32, sdk2.81 respectively. I have narrowed the issue down to several sql's in the 4gl. Below is the sqexplain on the first sql problem. While we don't normally amend our vendor's sql in this case we are willing to make the attempt. Optcompind set to 0, pdq off, buffers at 900000, shm at 360488. I added update stats high for cdhtmp after the inserts are done and have been running the dostats utility. I have included the sqexplain, temp table info, oncheck -pt on the large table.
Tables in join:
cdhtmp = 234 rows
py_per_mstr = 236 rows
pyt_hrs_dtl = 1.4 mil rows (but it is a denormalized table of 322 columns.)
4gl temp table stmt:
let sSql = "create temp table ", cdhtmp_name,
"(",
" CdhNo smallint,",
" CdhCd char(8),",
" StatusCd char(2),",
" DeferFlag char(1),",
" Type char(1),",
" System char(1),",
" Plan smallint,",
" dtbeg date,",
" dtend date,",
" RetirePB char(1),",
" RetireHB char(1)",
") with no log"
sqexplain.out:
QUERY:
------
select pyt_per_cc, py_batch_name, pyt_date01, pyt_hrs_no01,
pyt_hrs01, pyt_rt01, pyt_amt01, type, statuscd, system,
plan, deferflag, py_per_check_dt, py_per_end,
RetirePB, RetireHB, pyt_num_cd from pyt_hrs_dtl,
cdhtmp19494, py_per_mstr where hr_pe_id = 111800
and (py_batch_name like 'SYSTM%' or py_batch_name like 'DRS%')
and (pyt_status = 'DS' or pyt_status = 'DM' or pyt_status = 'DT')
and (pyt_date01 <= 08/31/2006)
"sqexplain.out" 54 lines, 2351 characters
QUERY:
------
select count(*), current from hr_empmstr
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) bsidba.hr_empmstr: INDEX PATH
(1) Index Keys: (count)
QUERY:
------
select pyt_per_cc, py_batch_name, pyt_date01, pyt_hrs_no01,
pyt_hrs01, pyt_rt01, pyt_amt01, type, statuscd, system,
plan, deferflag, py_per_check_dt, py_per_end,
RetirePB, RetireHB, pyt_num_cd from pyt_hrs_dtl,
cdhtmp19494, py_per_mstr where hr_pe_id = 111800
and (py_batch_name like 'SYSTM%' or py_batch_name like 'DRS%')
and (pyt_status = 'DS' or pyt_status = 'DM' or pyt_status = 'DT')
and (pyt_date01 <= 08/31/2006)
and (pyt_date01 >= 02/01/2000)
and pyt_hrs_no01= cdhtmp19494.CdhNo
and ((cdhtmp19494.RetirePB > ' '
and cdhtmp19494.RetirePB is not NULL) or (cdhtmp19494.RetireHB > ' '
and cdhtmp19494.RetireHB is not NULL)) and (cdhtmp19494.Type <> 'P')
and ((py_per_check_dt >= cdhtmp19494.dtBeg or
cdhtmp19494.dtBeg is NULL or cdhtmp19494.dtBeg = ' ')
and (py_per_check_dt <= cdhtmp19494.dtEnd or
cdhtmp19494.dtEnd is NULL or cdhtmp19494.dtEnd = ' '))
and py_per_cc = pyt_per_cc
Estimated Cost: 25162
Estimated # of Rows Returned: 1
1) informix.cdhtmp19494: SEQUENTIAL SCAN
Filters: (informix.cdhtmp19494.type != 'P' AND ((informix.cdhtmp19494.re
tirepb > ' ' AND informix.cdhtmp19494.retirepb IS NOT NULL ) OR (informix.cdhtmp
19494.retirehb > ' ' AND informix.cdhtmp19494.retirehb IS NOT NULL ) ) )
2) bsi.py_per_mstr: SEQUENTIAL SCAN
Filters: (((bsi.py_per_mstr.py_per_check_dt >= informix.cdhtmp19494.dtbe
g OR informix.cdhtmp19494.dtbeg IS NULL ) OR informix.cdhtmp19494.dtbeg = ) AND
((bsi.py_per_mstr.py_per_check_dt <= informix.cdhtmp19494.dtend OR informix.cdh
tmp19494.dtend IS NULL ) OR informix.cdhtmp19494.dtend = ) )
NESTED LOOP JOIN
3) bsi.pyt_hrs_dtl: INDEX PATH
Filters: ((((bsi.pyt_hrs_dtl.hr_pe_id = 111800 AND bsi.pyt_hrs_dtl.pyt_h
rs_no01 = informix.cdhtmp19494.cdhno ) AND (bsi.pyt_hrs_dtl.py_batch_name LIKE '
SYSTM%' OR bsi.pyt_hrs_dtl.py_batch_name LIKE 'DRS%' ) ) AND bsi.pyt_hrs_dtl.pyt
_date01 = 12/31/1899 ) AND ((bsi.pyt_hrs_dtl.pyt_status = 'DS' OR bsi.pyt_hrs_dt
l.pyt_status = 'DM' ) OR bsi.pyt_hrs_dtl.pyt_status = 'DT' ) )
(1) Index Keys: pyt_per_cc (Serial, fragments: ALL)
Lower Index Filter: bsi.py_per_mstr.py_per_cc = bsi.pyt_hrs_dtl.pyt_per_cc
NESTED LOOP JOIN
oncheck -pT ifasdev2:pyt_hrs_dtl
TBLspace Report for ifasdev2:bsi.pyt_hrs_dtl
Physical Address 66:721161
Creation date 08/21/2006 09:42:25
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 0
Number of extents 6
Current serial value 1
First extent size 1136
Next extent size 4544
Number of pages allocated 1423408
Number of pages used 1419244
Number of data pages 1418889
Number of rows 1418889
Partition partnum 15729025
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 67:159340 859952
859952 68:3 545280
1405232 74:581162 4544
1409776 74:586294 4544
1414320 74:593280 4544
1418864 74:601338 4544
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 4167
Bit-Map 352
Index 0
Data (Home) 1418889
----------
Total Pages 1423408
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 1299702324
Home Data Page Version Summary
Version Count
0 (current) 1418889
Index pyt_hrs_dtl_a fragment in DBspace ifasdev2dbs
Physical Address 66:721162
Creation date 08/21/2006 10:33:30
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 4
Current serial value 1
First extent size 14
Next extent size 28
Number of pages allocated 4802
Number of pages used 4786
Number of data pages 0
Number of rows 0
Partition partnum 15729026
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 67:1019292 4438
4438 68:545283 308
4746 74:590980 28
4774 74:597940 28
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 16
Bit-Map 2
Index 4784
Data (Home) 0
----------
Total Pages 4802
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_a on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 41 1356
2 41 115 838
3 4742 299 490
----- -------- -------- ----------
Total 4784 297 493
Index pyt_hrs_dtl_b fragment in DBspace ifasdev2dbs
Physical Address 66:721163
Creation date 08/21/2006 10:33:39
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 12
Current serial value 1
First extent size 14
Next extent size 14
Number of pages allocated 5852
Number of pages used 5846
Number of data pages 0
Number of rows 0
Partition partnum 15729027
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 67:1023730 14
14 68:545591 5698
5712 74:580606 14
5726 74:581020 14
5740 74:586138 14
5754 74:586280 14
5768 74:590966 14
5782 74:591252 14
5796 74:592734 14
5810 74:598064 14
5824 74:599630 14
5838 74:601324 14
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 6
Bit-Map 2
Index 5844
Data (Home) 0
----------
Total Pages 5852
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_b on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 61 848
2 61 94 654
3 5782 245 763
----- -------- -------- ----------
Total 5844 243 762
Index pyt_hrs_dtl_c fragment in DBspace ifasdev2dbs
Physical Address 66:721164
Creation date 08/21/2006 10:34:03
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 8
Current serial value 1
First extent size 17
Next extent size 128
Number of pages allocated 12433
Number of pages used 12349
Number of data pages 0
Number of rows 0
Partition partnum 15729028
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 68:551289 11409
11409 74:580764 256
11665 74:581034 128
11793 74:585706 128
11921 74:585866 128
12049 74:586152 128
12177 74:590838 128
12305 74:599406 128
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 84
Bit-Map 4
Index 12345
Data (Home) 0
----------
Total Pages 12433
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_c on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 3 1964
2 3 66 526
3 198 61 787
4 12143 116 1042
----- -------- -------- ----------
Total 12345 115 1038
Index pyt_hrs_dtl_d fragment in DBspace ifasdev2dbs
Physical Address 66:721165
Creation date 08/21/2006 10:34:18
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 5
Current serial value 1
First extent size 19
Next extent size 144
Number of pages allocated 23059
Number of pages used 22960
Number of data pages 0
Number of rows 0
Partition partnum 15729029
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 68:562698 22483
22483 74:580620 144
22627 74:585994 144
22771 74:591266 144
22915 74:599968 144
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 99
Bit-Map 6
Index 22954
Data (Home) 0
----------
Total Pages 23059
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_d on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 9 1804
2 9 41 938
3 375 60 459
4 22569 62 353
----- -------- -------- ----------
Total 22954 62 355
Index pyt_hrs_dtl_e fragment in DBspace ifasdev2dbs
Physical Address 66:721166
Creation date 08/21/2006 10:35:06
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
First extent size 42
Next extent size 1344
Number of pages allocated 63210
Number of pages used 62204
Number of data pages 0
Number of rows 0
Partition partnum 15729030
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 68:585181 63210
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 1006
Bit-Map 16
Index 62188
Data (Home) 0
----------
Total Pages 63210
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_e on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 2 1968
2 2 22 1048
3 45 35 474
4 1581 38 334
5 60559 23 965
----- -------- -------- ----------
Total 62188 23 949
Index pyt_hrs_dtl_f fragment in DBspace ifasdev2dbs
Physical Address 66:721167
Creation date 08/21/2006 10:37:24
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 4
Current serial value 1
First extent size 5
Next extent size 32
Number of pages allocated 4101
Number of pages used 4085
Number of data pages 0
Number of rows 0
Partition partnum 15729031
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 66:1023738 5
5 68:648391 4032
4037 74:585834 32
4069 74:592960 32
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 16
Bit-Map 2
Index 4083
Data (Home) 0
----------
Total Pages 4101
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_f on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 21 1850
2 21 193 467
3 4061 349 267
----- -------- -------- ----------
Total 4083 348 268
Index pyt_hrs_dtl_g fragment in DBspace ifasdev2dbs
Physical Address 66:721168
Creation date 08/21/2006 10:37:27
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 2
Current serial value 1
First extent size 7
Next extent size 32
Number of pages allocated 4103
Number of pages used 4100
Number of data pages 0
Number of rows 0
Partition partnum 15729032
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 68:652423 4071
4071 74:591008 32
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 3
Bit-Map 2
Index 4098
Data (Home) 0
----------
Total Pages 4103
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_g on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 31 1708
2 31 131 958
3 4066 348 267
----- -------- -------- ----------
Total 4098 347 272
4071 74:591008 32
