index exclusivity

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

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 ------------------------------

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 ------------------------------

One of the problems over some tables is they have old schemas, and their indexes may need to be modified!! ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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

Wonderful, thanks Paul! Regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

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

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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 ------------------------------

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: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

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 ------------------------------