Printer FriendlyEmail Article Link

Informix, STCLive PM: How can I tell if a table in Informix is sized properly?

Answer

Use 'onstat -pT <database>:<table>' command.
The output includes:

  • Number of extents:
    For best performance the 'number of extents' should be 1.  If the number grows, it will affect performance.
     
  • Number of pages allocated and Number of pages used:
    Shows the number of pages that are allocated for the table and the number of pages being used.  As rows are added to the table, the 'number of pages' will increase to the size of the 'Number of pages allocated'.  If that point is reached and additional rows are added, the number of pages specified in the Next Extent Size will be added to the table. If rows are deleted these value will not decrease.
     
  • Number of data pages:
    Shows the number of pages that have data. 
     
  • The USAGE Report section of the output shows the break down of the how the pages are being used and includes the number of free pages.

Example: If this table will not have additional rows added, the table could have been sized with smaller first extent size.

=> oncheck -pT scout:qscope_1h_229


TBLspace Report for scout:smartsys.qscope_1h_229

    Physical Address               62:14
    Creation date                  03/09/2013 19:46:25
    TBLspace Flags                 902        Row Locking
                                              TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
    Maximum row size               290
    Number of special columns      2
    Number of keys                 0
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              160000
    Next extent size               32000
    Number of pages allocated      160000
    Number of pages used           135438
    Number of data pages           13801
    Number of rows                 82804
    Partition partnum              61865995
    Partition lockid               61865995

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0        62:1534230      160000     160000


TBLspace Usage Report for scout:smartsys.qscope_1h_229

    Type                  Pages      Empty  Semi-Full       Full  Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free                 146165
    Bit-Map                  34
    Index                     0
    Data (Home)           13801
    Data (Remainder)          0          0          0          0          0
                     ----------
    Total Pages          160000

    Unused Space Summary

        Unused data bytes in Home pages             3699244
        Unused data bytes in Remainder pages              0

    Home Data Page Version Summary

                 Version                                 Count

                       0 (current)                      13801

                  Index oheunq229 fragment partition scout1h6 in DBspace scout1h6

    Physical Address               37:39
    Creation date                  03/09/2013 20:48:22
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               290
    Number of special columns      0
    Number of keys                 1
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              43586
    Next extent size               8717
    Number of pages allocated      43586
    Number of pages used           43586
    Number of data pages           0
    Number of rows                 0
    Partition partnum              35651620
    Partition lockid               61865995

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0        37:2425646       43586      43586


TBLspace Usage Report for scout:smartsys.qscope_1h_229

    Type                  Pages      Empty  Semi-Full       Full  Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free                  37811
    Bit-Map                  11
    Index                  5764
    Data (Home)               0
                     ----------
    Total Pages           43586

    Unused Space Summary

        Unused data slots                                 0
        Unused bytes per data page                      256
        Total unused bytes in data pages                  0

    Home Data Page Version Summary

                 Version                                 Count

                       0 (current)                          0

Index Usage Report for index oheunq229 on scout:smartsys.qscope_1h_229

                    Average    Average
    Level    Total No. Keys Free Bytes
    ----- -------- -------- ----------
        1        1        9       1388
        2        9        9       1256
        3       89        4       1639
        4      435       12       1082
        5     5230       16        691
    ----- -------- -------- ----------
    Total     5764       16        736

                  Index ohevref229 fragment partition scout1h11 in DBspace scout1h11

    Physical Address               62:38
    Creation date                  03/09/2013 20:48:23
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               290
    Number of special columns      0
    Number of keys                 1
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              5737
    Next extent size               1147
    Number of pages allocated      5737
    Number of pages used           503
    Number of data pages           0
    Number of rows                 0
    Partition partnum              61866019
    Partition lockid               61865995

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0        62:2345150        5737       5737


TBLspace Usage Report for scout:smartsys.qscope_1h_229

    Type                  Pages      Empty  Semi-Full       Full  Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free                   5469
    Bit-Map                   1
    Index                   267
    Data (Home)               0
                     ----------
    Total Pages            5737

    Unused Space Summary

        Unused data slots                                 0
        Unused bytes per data page                      256
        Total unused bytes in data pages                  0

    Home Data Page Version Summary

                 Version                                 Count

                       0 (current)                          0

Index Usage Report for index ohevref229 on scout:smartsys.qscope_1h_229

                    Average    Average
    Level    Total No. Keys Free Bytes
    ----- -------- -------- ----------
        1        1        3       1988
        2        3       87        969
        3      263      319        410
    ----- -------- -------- ----------
    Total      267      315        422

Product : VW Controller PM Classic,VW Controller T&D Classic,Informix