Informix

 View Only
Expand all | Collapse all

index exclusivity

  • 1.  index exclusivity

    Posted Fri January 17, 2020 06:54 AM
    Hi,

    I have several tables with different levels of use between clients. how can I identify their utilization rate and effectiveness?

    Thanks for any help,

    SP

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

    #Informix


  • 2.  RE: index exclusivity

    IBM Champion
    Posted Fri January 17, 2020 07:13 AM
    Hi,

    some ideas only - the question isn't very precise, and even if it would, presice answers might still not be easy:

    • for whether used at all, there's a new (12.10.xC10) 'last used time' field in index partition header pages (oncheck -pt, or sysptnhdr.idx_acc_tm field SMI)  ->  might be spoiled by e.g. AUS?
    • for how much space occupying in your bufferpools (as an indication for ...?):  onstat -P
    • for how much read/write activity: onstat -g ppf, or sysptprof SMI
    • for some historic perspective:  some sysadmin:mon_* monitor tables
    HTH,
     Andreas

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

    Original Message


  • 3.  RE: index exclusivity

    Posted Fri January 17, 2020 07:30 AM
    Thanks for reply,

    I want to know if my indexes are effective or if they aren't usefull!!
    We are using last versions >12.10.xC10. 

    Regards,

    SP

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

    Original Message


  • 4.  RE: index exclusivity

    Posted Fri January 17, 2020 07:32 AM
    One of the problems over some tables is they have old schemas, and their indexes may need to be modified!!

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

    Original Message


  • 5.  RE: index exclusivity

    IBM Champion
    Posted Fri January 17, 2020 08:06 AM
    An index, even if used rarely, can still be hugely important - for that particular query running once a month and depending on this index, without which the whole query might be lost and be a lot more expensive.  I'm theoretisizing...

    But seriously, from what information could you conclude an index' general usefulness or effectiveness?

    Maybe tackling the problem (is there any?) from the opposite side would be more practical:  are there any queries that would benefit from an index that currently doesn't exist in the format it would be needed in? Any queries misbehaving, being more expensive than expected, etc., also any tables seeing avoidable expensive sequential scans ... and what would be the SQL behind these scans?
    To know whether you're having all the required indices, you'd have to start from queries and query plans.

    BR,
     Andreas

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

    Original Message


  • 6.  RE: index exclusivity

    Posted Fri January 17, 2020 08:23 AM
    Hi Sergio,
    To see if it's a frequently used index, I think it's a good idea to periodically check if the bufread value in the sysptprof table is increasing.
    As mentioned by Andreas, starting with version 12.10.xc10, you will also see 'Last Lookup / Scan' in the output of oncheck -pt.

    Do you mean 'effectiveness' as you compare the number of rows with the number of unique values?
    I think comparing nrows in systables and nunique in sysindexes will help you determine if it is an efficient index.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 7.  RE: index exclusivity
    Best Answer

    Posted Fri January 17, 2020 09:20 AM
    Thanks for replies,

    This is an old question I have about what is being used, there is the onstat opn option for open tables but I can't find any information about the interpretation of these values and results.
    As I have databases that have tables with different uses in clients, what I want to know is if their indexes are being effective because we are facing some performance issues...

    SP

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

    Original Message


  • 8.  RE: index exclusivity

    IBM Champion
    Posted Fri January 17, 2020 09:32 AM
    http://www.oninit.com/onstat/pda.php?id=onstat%20-g%20opn

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC



    Original Message


  • 9.  RE: index exclusivity

    Posted Fri January 17, 2020 09:40 AM
    Wonderful, thanks Paul!

    Regards,

    SP

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

    Original Message


  • 10.  RE: index exclusivity

    IBM Champion
    Posted Fri January 17, 2020 09:48 AM

    All the onstats should be visible via www.oninit.com/onstast <onstat option>  - all (most) browsers will cope with the spaces. Some of the 'official' IBM onstat pages are a lifts from Oninit

     

    Cheers

    Paul

     




    Original Message


  • 11.  RE: index exclusivity

    IBM Champion
    Posted Fri January 17, 2020 09:53 AM
    Sergio:

    How do you define "effective" though? Hard to respond when we don't know what you are looking for.

    Personally I would approach the problem from the other side. Find queries that see slow, run under SET EXPLAIN with explain statistics enabled (EXPLAIN_STAT 1) and a) see what indexes are being used, 2) check that the estimated and actual rows match at each step and total, 3) see if there is an unexpected sequential scan  involved.

    Then see how more up-to-date or more detailed data distributions helps. Add indexes that seem to be missing. Rebuild any indexes being ignored by the query that are inefficient internally due to deletes (see the "onstat -C hot" report - match the partnums there to the partnum for the index in sysmaster:systabnames or in <database>:sysfragments).

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message


  • 12.  RE: index exclusivity

    Posted Fri January 17, 2020 01:44 PM

    Sérgio,

    Latest versions have information in "onstat -pT":

    Maximum row size 134
    [...]
    Partition partnum 8389067
    Partition lockid 8389066
    Last Lookup/Scan Tue Jan 8 11:22:53 2019

    This information was supposed to be in sysmaster, but I couldn't find it.

    This tells you the last time the index was used for a query.

    But be careful.... Andreas already pointed that sometimes an index may be used very sparsely and still be essential. Besides, some must exist to enforce integrity rules.

    I would add:
    1- I never identified an issue on a system because of too many indexes
    2- From what I saw in your systems, this was clearly not a top priority, even though I could conceptually believe you have too many indexes

    Regards.



    ------------------------------
    FERNANDO NUNES
    ------------------------------

    Original Message


  • 13.  RE: index exclusivity

    Posted Fri January 17, 2020 02:22 PM
    When update statistics didn't have enough buckets for the distributions in its basic design, back 11.5 time frame and earlier, customers tried to compensate for the resulting inconsistent query speed by creating lots of composite indexes on specific tables which worked sometimes but for the most part did not. Some tables had 26/27 indexes in my experience when only 3-4 at max should have been present. The sheer weight of creating too many indexes on one table slowed all transactions down utilizing that table. One of the purposes of the more recent feature of looking for unused or under-used indexes has its genesis, in part, to identify such redundant indexes so that they could be potentially eliminated, thereby speeding up the transactions.
     
    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 Informix Roadshow page is here:

    https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

    Shortcut to the Informix Roadshow Page:

    http://bit.ly/ifmx_roadshow

    All presentations and the agenda used by the Roadshow can be found there.
     
    The current ZACS Informix Page can be found here:
     
     
     



    Original Message


  • 14.  RE: index exclusivity

    Posted Sat January 18, 2020 11:17 AM
    Thank you all for replies,

    I am going to try to find some issues with your references and help.
    Our development is subject to government directives, so sometimes we have daily changes, there is not enough time to do the necessary tests and configurations.
    Despite the good experience we have had with informix over more than 20 years, I confess that sometimes it is difficult to find help with evidence. I also often feel a lack of information about the new features and respective application modes, in order to take advantage of these new features.

    Best regards,

    SP

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

    Original Message