Large extents

Posted in 1999

Shevkar, Mukund — Mon, 19 Jul 1999 11:55:21 -0700 — source: Informix-list mailing list archive (1991-1998)

I have reorganized some of the tables to reduce no of extents. Following is an excerpt of oncheck -pT on one of the table. See the free space summary and please advise if I should change it. If yes what are the commands I can use for the same. TBLspace Report for ola:db.lppas_tran Physical Address 21d302 Creation date 07/15/99 13:29:29 TBLspace Flags 1 Page Locking Maximum row size 371 Number of special columns 0 Number of keys 6 Number of extents 10 Current serial value 1 First extent size 8 Next extent size 300 Number of pages allocated 38108 Number of pages used 38009 Number of data pages 30685 Number of rows 153422 Partition partnum 2097450 Partition lockid 2097450 Extents Logical Page Physical Page Size 0 200045 8 8 21630c 600 608 217738 600 1208 2179e0 900 2108 217e4c 1200 3308 230185 600 3908 230699 300 4208 2311e9 900 5108 2318f1 22800 27908 2400cf 10200 TBLspace Usage Report for ola:db.lppas_tran Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 99 Bit-Map 5 Index 7319 Data (Home) 30685 ---------- Total Pages 38108 Unused Space Summary Unused data slots 3 Unused bytes per data page 145 Total unused bytes in data pages 4449325 Index Usage Report for index detached on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 12 1836 2 12 97 750 3 1173 130 1092 ----- -------- -------- ---------- Total 1186 130 1089 Index Usage Report for index lppas_tran4 on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 5 1968 2 5 120 984 3 600 255 731 ----- -------- -------- ---------- Total 606 254 735 Index Usage Report for index lppas_rpt on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 23 1532 2 23 60 813 3 1400 109 859 ----- -------- -------- ---------- Total 1424 108 859 Index Usage Report for index lppas_app on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 18 1700 2 18 77 801 3 1391 110 1015 ----- -------- -------- ---------- Total 1410 109 1013 Index Usage Report for index lppas_tran3 on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 34 1253 2 34 61 663 3 2101 73 713 ----- -------- -------- ---------- Total 2136 72 712 Index Usage Report for index 950_217169 on ola:db.lppas_tran Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 6 1966 2 6 91 1282 3 550 278 619 ----- -------- -------- ---------- Total 557 276 628

↪ replying to Shevkar, Mukund

Art S. Kagel — Mon, 19 Jul 1999 17:45:29 -0400 — source: Informix-list mailing list archive (1991-1998)

The only thing you can do is reduce or increase the row length so you waste less than 145 byte per page (time 30,600 pages that adds up). Art S. Kagel "Shevkar, Mukund" wrote: > > I have reorganized some of the tables to reduce no of extents. Following is > an excerpt of oncheck -pT on > one of the table. See the free space summary and please advise if I should > change it. If yes what are the > commands I can use for the same. > TBLspace Report for ola:db.lppas_tran > > Physical Address 21d302 > Creation date 07/15/99 13:29:29 > TBLspace Flags 1 Page Locking > Maximum row size 371 > Number of special columns 0 > Number of keys 6 > Number of extents 10 > Current serial value 1 > First extent size 8 > Next extent size 300 > Number of pages allocated 38108 > Number of pages used 38009 > Number of data pages 30685 > Number of rows 153422 > Partition partnum 2097450 > Partition lockid 2097450 > > Extents > Logical Page Physical Page Size > 0 200045 8 > 8 21630c 600 > 608 217738 600 > 1208 2179e0 900 > 2108 217e4c 1200 > 3308 230185 600 > 3908 230699 300 > 4208 2311e9 900 > 5108 2318f1 22800 > 27908 2400cf 10200 > > TBLspace Usage Report for ola:db.lppas_tran > > Type Pages Empty Semi-Full Full Very-Full > ---------------- ---------- ---------- ---------- ---------- ---------- > Free 99 > Bit-Map 5 > Index 7319 > Data (Home) 30685 > ---------- > Total Pages 38108 > > Unused Space Summary > > Unused data slots 3 > Unused bytes per data page 145 > Total unused bytes in data pages 4449325 > > Index Usage Report for index detached on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 12 1836 > 2 12 97 750 > 3 1173 130 1092 > ----- -------- -------- ---------- > Total 1186 130 1089 > > Index Usage Report for index lppas_tran4 on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 5 1968 > 2 5 120 984 > 3 600 255 731 > ----- -------- -------- ---------- > Total 606 254 735 > > Index Usage Report for index lppas_rpt on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 23 1532 > 2 23 60 813 > 3 1400 109 859 > ----- -------- -------- ---------- > Total 1424 108 859 > > Index Usage Report for index lppas_app on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 18 1700 > 2 18 77 801 > 3 1391 110 1015 > ----- -------- -------- ---------- > Total 1410 109 1013 > > Index Usage Report for index lppas_tran3 on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 34 1253 > 2 34 61 663 > 3 2101 73 713 > ----- -------- -------- ---------- > Total 2136 72 712 > > Index Usage Report for index 950_217169 on ola:db.lppas_tran > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 6 1966 > 2 6 91 1282 > 3 550 278 619 > ----- -------- -------- ---------- > Total 557 276 628