Informix

Expand all | Collapse all

Fragmentation issues

  • 1.  Fragmentation issues

    Posted Thu June 17, 2021 07:08 AM
    Hi,

    I frequently see fragmentation in tables and indexes, even though I have activated the auto_crsd task there is still fragmentation.
    Are there any other tasks needed?
    How can I get the size of tables and indexes, including fragments?

    Thanks in advance for any help,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------


  • 2.  RE: Fragmentation issues

    Posted Thu June 17, 2021 10:56 AM
    Hi Sergio,

    I guess you're talking about intra-partition fragmentation, so multiple / too many extents in partitions and the (auto-)defrag functionality?

    For the auto_crsd task, please be aware that its various capabilities can be turned on/off by specific switches in the ph_threshold table, and most are off by default (since the switches aren't even there in ph_threshold), if I'm right. 
    Please refer to Scheduling data optimization for more details and/or ook at the related auto_crsd() SPL UDR definition in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql for how exactly this works.

    Regarding size of partitions/tables/indices, I'm sure Art has this in one of his many scripts and utilities ;-)  ... and it probably even will be in one of the discussions here.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: Fragmentation issues

    Posted Thu June 17, 2021 12:31 PM
    Thanks for Reply Andreas,

    I am afraid that I am not invoking the process in the correct way, however as I have tables that quickly get more than 30 extents I was convinced that these situations would be detected by auto_crsd...

    Thanks for the info, I'll check as I'm using the default values.

    Best regards,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------



  • 4.  RE: Fragmentation issues

    Posted Thu June 17, 2021 03:14 PM
    So for "tables that quickly get more than 30 extents" you might want to (significantly) increase the "next size", by means of ALTER TABLE?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 5.  RE: Fragmentation issues

    Posted Thu June 17, 2021 02:19 PM
    Sergio:

    The best way to see how much storage your tables and indexes are taking up is to use oncheck -pT. That report will give you everything you need to know.

    Art

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