Informix

 View Only
  • 1.  A lot of extents in temp tables

    Posted Fri March 31, 2023 12:27 PM
    Hi everyone,

    I have many of tables with high extents what can I do to fix it?
    Follow attached print ox tables and my dbspaces (onstat -d)


    image.png


    image.png



  • 2.  RE: A lot of extents in temp tables

    IBM Champion
    Posted Fri March 31, 2023 01:53 PM

    Leandro:

    You can defragment tables and indexes most easily using the "defragment" SQL API function in the sysadmin database. 

    For tables:
    execute function task( 'defragment', 'database:owner.table' );

    For indexes (or to defragment a single partition of a partitioned table):
    execute function task( 'defragment partition', partnum );

    You can find the partnum for table partitions and for indexes (and fragments of partitioned indexes) in the sysmaster:systabnames table.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: A lot of extents in temp tables

    Posted Fri March 31, 2023 02:23 PM
    Hey Art, thanks.
    So, look at this error when perform defrag:

    Failed to defrag the partition 4194355 - ISAM error 21539





  • 4.  RE: A lot of extents in temp tables

    IBM Champion
    Posted Fri March 31, 2023 02:44 PM

    Here's the error message for that error:

    -21539  Defragment: You can't run this command on catalog/pseudo/temp tables
     
    ACTION
    Run defragment on a regular table/partition


    So, did you run this against a true temp table, or a catalog table, e.g. usually a table beginning with "sys" or a table in sysmaster?

    If these are truly temporary tables, then are they always around?  The next time the temp table is created, consider specifying a first and next extent size so that it preallocates more space when created, e.g.:

       create temp table mwtesttmp(a serial, b char(10)) extent size 10000 next size 1000;

    This will create the table initially with 10,000 KB of space, and when that fills, then it will grow by 1,000 KB each time.







    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 5.  RE: A lot of extents in temp tables

    Posted Fri March 31, 2023 02:50 PM
    Humm ok great
    But, this table is part of a partner's ERP system over which I have no control.
    Is it possible to specify this space at the instance level?





  • 6.  RE: A lot of extents in temp tables

    IBM Champion
    Posted Fri March 31, 2023 03:30 PM

    No, there isn't.  Is the large number of extents causing a problem?



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 7.  RE: A lot of extents in temp tables

    IBM Champion
    Posted Fri March 31, 2023 03:46 PM

    Leandro:

    I didn't realize that you were only concerned about the temp tables. There isn't much you can do if you do not have access to the application code. However, unless you do not have sufficient cache configured a temp table with many extents should not be a problem. In the current releases of the engine, the limit on the number of extents is over 32000 so with extent size doubling that is virtually unlimited. It should also not be a performance issue because if you have sufficient cache configured, those large temp tables should reside completely in memory and not actually flush to disk.



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 8.  RE: A lot of extents in temp tables

    Posted Fri March 31, 2023 03:54 PM
    I understand better now. It definitely won't be a problem.
    Thank you both!