Informix

Expand all | Collapse all

Unused Indexes

  • 1.  Unused Indexes

    Posted Mon January 25, 2021 11:33 AM
    Edited by TOM GIRSCH Mon January 25, 2021 12:18 PM

    A few revisions of IDS 12.10 ago, HCL finally added a field to the oncheck -pt output that tells you the last time an index was used by a query. I'd like to search on that and refine it a bit. Rather than reinventing the entire wheel, has someone here sussed out if that field is in sysmaster, and if so, where?

    UPDATED to note: I'm on 12.10.FC14XF

    TIA,

    - TJG

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 2.  RE: Unused Indexes

    Posted Mon January 25, 2021 11:50 AM
    sysmaster:sysactptnhdr.pn_idx_acc_tm - active partitions
    sysmaster:sysptnhdr.idx_acc_tm - all partitions
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     





  • 3.  RE: Unused Indexes

    Posted Mon January 25, 2021 12:09 PM
    Edited by TOM GIRSCH Mon January 25, 2021 12:14 PM

    I don't see a column in sysptnhdr that corresponds to the datetime at which an index was most recently accessed. I also don't see that column in sysactptnhdr; wondering if maybe it didn't get added until 14.10? I'm looking for the emphasized portion here:

    $ oncheck -pt sysadmin:ph_task



    TBLspace Report for sysadmin:informix.ph_task

    Physical Address 1:181905
    Creation date 01/31/2017 14:20:23
    TBLspace Flags 902 Row Locking
    TBLspace contains VARCHARS
    TBLspace use 4 bit bit-maps
    Maximum row size 8709
    Number of special columns 6
    Number of keys 0
    Number of extents 3
    Current serial value 69
    Current SERIAL8 value 1
    Current BIGSERIAL value 1
    Current REFID value 1
    Pagesize (k) 2
    First extent size 8
    Next extent size 32
    Number of pages allocated 32
    Number of pages used 23
    Number of data pages 21
    Number of rows 61
    Partition partnum 1048840
    Partition lockid 1048840

    Extents
    Logical Page Physical Page Size Physical Pages
    0 1:188302 8 8
    8 1:188696 8 8
    16 1:189096 16 16

    Index 102_4 fragment partition chaos_root in DBspace chaos_root

    Physical Address 1:181906
    Creation date 01/31/2017 14:20:23
    TBLspace Flags 802 Row Locking
    TBLspace use 4 bit bit-maps
    Maximum row size 8709
    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 4
    Next extent size 4
    Number of pages allocated 4
    Number of pages used 2
    Number of data pages 0
    Number of rows 0
    Partition partnum 1048841
    Partition lockid 1048840
    >> Last Lookup/Scan No Record  <-- THIS!

    Extents
    Logical Page Physical Page Size Physical Pages
    0 1:188310 4 4


    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 4.  RE: Unused Indexes

    Posted Mon January 25, 2021 02:49 PM
    they are integers, think you have to convert to date
     
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     






  • 5.  RE: Unused Indexes

    Posted Mon January 25, 2021 02:57 PM
    @Scott Pickett The relevant columns seem to be absent from 12.10.FC14XF.


    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 6.  RE: Unused Indexes

    Posted Mon January 25, 2021 03:25 PM
    This was picked up from the sysmaster.sql file in $INFORMIXDIR/etc/sysmaster.sql for version 14.10.xC5.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     





  • 7.  RE: Unused Indexes

    Posted Mon January 25, 2021 04:33 PM

    So it looks like there's no way to get it from sysmaster in 12.10. Drat.

     






  • 8.  RE: Unused Indexes

    Posted Mon January 25, 2021 12:16 PM
    Edited by TOM GIRSCH Mon January 25, 2021 12:17 PM

    sysactptnhdr.altstmp, maybe?

    SELECT n.tabname, ph.*
      FROM sysactptnhdr AS ph
      JOIN systabnames AS n ON n.partnum = ph.partnum
     WHERE n.tabname MATCHES "ix*";
    
    tabname           ix_cmd_hist_01
    address           42611433512
    partnum           1048834
    pn_flags          2050
    rowsize           0
    ncols             0
    nkeys             1
    nextns            6
    pagesize          2048
    created           1485872423
    cur_serial4       1
    cur_bigserial     1
    cur_serial8       1
    fextsiz           4
    nextsiz           128
    nptotal           236
    npused            236
    npdata            0
    lockid            1048833
    nrows             0
    ninserts          554401
    nupdates          0
    ndeletes          544710
    flags             0
    ucount            0
    chunk             1
    offset            181899
    lastidxpn         191
    nextns1           6
    nextns2           0
    badkeys           0
    altstmp           174775196
    ocount            0
    skstamp           -871249784
    pta_oldvers       0
    pta_newvers       0
    pta_bmpagenum     0
    pta_totpgs        0
    pta_opems_allocd  0
    pta_opems_filled  0
    glscollname       en_US.819
    pf_rqlock         144398
    pf_wtlock         0
    pf_deadlk         0
    pf_dltouts        0
    pf_isread         29058
    pf_iswrite        29050
    pf_isrwrite       0
    pf_isdelete       28424
    pf_bfcread        203219
    pf_bfcwrite       59725
    pf_seqscans       0
    pf_dskreads       2570
    pf_dskwrites      4103
    pn_flags2         0  


    ------------------------------
    TOM GIRSCH
    ------------------------------