Indexes usage

Hi People !! Please, a need some help how to find in my database the indexes with high percent of usage. Anybody knows if exist a function or if I have to develop a query ? Thanks, Best Regards, Eduardo

Mr. Carlos: What´s your Informix version and platform, please??? You want to know what are most acessed (used) indexes, or what are the largest indexes (extents sizes)??? Regards. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; CARLOS BOCCIO escreveu: > Hi People !! > > Please, a need some help how to find in my database the indexes with high > percent of usage. > Anybody knows if exist a function or if I have to develop a query ? > > Thanks, > > Best Regards, > > Eduardo > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >

Not sure which version you are on, so this information might be in your version or might not. 1) onstat -C part shows how many times an index has been used for read= ing 2) onstat -g ppf and search for the index partition shows you how many index items have been read, deleted, inserted to a specific index. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = = From: "CARLOS BOCCIO" <cboccio@yahoo.com.br> = = = = To: ids@iiug.org = = = = Date: 12/29/2009 05:27 AM = = = = Subject: Indexes usage [18496] = = = = Sent by: ids-bounces@iiug.org = = = = Hi People !! Please, a need some help how to find in my database the indexes with hi= gh percent of usage. Anybody knows if exist a function or if I have to develop a query ? Thanks, Best Regards, Eduardo ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

IFF you have TBLSPACE_STATS set to 1 in your ONCONFIG file, you can find the stats on your index partnums in the onstat -g ppf and depending on your server version (you REALLY must post your IDS version and platform information when you post!) you will find the same information in the sysmaster:sysptprof table. If TBLSPACE_STATS is not present or is set to anything but 1 there will be no data on partition IO available. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 29, 2009 at 8:26 AM, CARLOS BOCCIO <cboccio@yahoo.com.br> wrote: > Hi People !! > > Please, a need some help how to find in my database the indexes with high > percent of usage. > Anybody knows if exist a function or if I have to develop a query ? > > Thanks, > > Best Regards, > > Eduardo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517478384541867047be11174

Hi, Mr. Alexandre, The Informix version is 10.00.FC8W4X5 and the plataform is AIX version 5.3. I want to know the most acessed (used) indexes. Regards. Carlos E. Boccio

The question is still a little ambiguous. What does most accessed mean? Does it mean the most number of rows processed by an index, the number of times an index has been used (no matter if 1 row or 100 rows are processed by the index). In short without going to version 11 you can only do the latter. You can tell how many times an index has been used for READING, base on the "onstat -C part" and look at the position column. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 12/29/2009 09:37:44 AM: > [image removed] > > Re: Indexes usage [18500] > > CARLOS BOCCIO > > to: > > ids > > 12/29/2009 09:38 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, Mr. Alexandre, > > The Informix version is 10.00.FC8W4X5 and the plataform is AIX version 5.3. > > I want to know the most acessed (used) indexes. > > Regards. > > Carlos E. Boccio > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I had a short PERL script( Running on IDS11.10, AIX5.3) to query the sysmaster database. It will show both index and table usages. You can chose to sort by read, writes, locks... The following are sample output and the script. Thanks, Frank informix@erika $ tbio Usage: tbio 2(sort by lock),3(r),4(w),5(pr) 6(pw) informix@erika $ tbio 5 tabname lockreqs isreads, iswrites pagreads pagwrites file_receipt_info 106199886 89230181 82344 4706489 37264 lin_file_info 38060930 2922778 575699 4142726 257744 hist_file_receipt_info 374024 62878 79132 1080610 18672 file_change_notice 19228713 266393 153628 652298 46498 hist_dataset_info 360009 66525 77168 636295 11391 file_met -897984453 35756 17139 580155 3645 cman_cat_deliv 46638700 44097574 202206 501897 29115 dataset_info_idx1 11561803 543512 0 466919 4624 dataset_info 49152952 890684 75004 456750 24158 order_spec 11705929 70416 64216 428507 78627 cman_cat_deliv_idx1 1185007 222968 0 419769 99117 #!/usr/local/bin/perl -w # #------------------------------------------------------------- use DBI; $connect = 'dbi:Informix:sysmaster'; $dbh = DBI->connect($connect) or die "could not connect to database\\ "; $dbh->{ChopBlanks} = 1; $dbh->{RaiseError} = 1; $dbh->do("Set lock mode to wait 60"); $output = *STDOUT; $rows = 80; $sort = $ARGV[0]; # check Usage $argNum=@ARGV; if ($argNum!=1) { print "Usage: tbio 2(sort by lock),3(r),4(w),5(pr) 6(pw)\\ "; exit; } $where_clause=" tabname matches '[a-z]*' and tabname not like 'sys%'"; # $where_clause="dbsname='noaa' and tabname matches '[a-z]*' and tabname not like 'sys%'"; # and tabname not like '%idx%' and tabname not like 'cdr%' and tabname not matches '*_ipk'"; $stmt= "select first $rows tabname, lockreqs, isreads, iswrites, pagreads, pagwrites from sysptprof where $where_clause order by $sort desc"; $reptab = $dbh->prepare($stmt); $reptab->bind_columns(undef,\\\\$tabname,\\\\$lockreqs,\\\\$isreads, \\\\$iswrites,\\\\$pagreads, \\\\$pagwrites); $reptab->execute; printf $output "\\ %25s %10s %12s, %12s %10s %10s \\ ", "tabname", "lockreqs", "isreads","iswrites", "pagreads", "pagwrites"; while ($reptab->fetch) { printf $output "\\ %25s %10s %12s %12s %10s %10s \\ ", $tabname, $lockreqs, $isreads, $iswrites, $pagreads, $pagwrites; } On Tue, Dec 29, 2009 at 12:37 PM, CARLOS BOCCIO <cboccio@yahoo.com.br>wrote: > Hi, Mr. Alexandre, > > The Informix version is 10.00.FC8W4X5 and the plataform is AIX version 5.3. > > I want to know the most acessed (used) indexes. > > Regards. > > Carlos E. Boccio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502c14d0916b6047be24fb7

Hi John, I need to know the number of times an index has been used (no matter if 1 row or 100 rows are processed by the index). I need this information to do a tuning job in my database tables. Let me give you a exemple that I want: Example: Table TAB1 has 6 indexes (ix1, ix2...ix6) but the applications (programs) are using the indexes ix1, ix2 and ix3. So the indexes ix4, ix5 and ix6 can be dropped, because nobody use it. So I need to know how can I identify these indexes in my database tables (the indexes that I have more access ou usage). Thanks Regards, Carlos E. Boccio

While this perl script works on version 11 for indexes, it will not work in version 10 for indexes. This information was added in version 11 for indexes to provide DBA more information. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 12/29/2009 10:37:13 AM: > [image removed] > > Re: Indexes usage [18502] > > FRANK > > to: > > ids > > 12/29/2009 10:38 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I had a short PERL script( Running on IDS11.10, AIX5.3) to query the > sysmaster database. It will show both index and table usages. > > You can chose to sort by read, writes, locks... > > The following are sample output and the script. > > Thanks, > Frank > > informix@erika $ tbio > Usage: tbio 2(sort by lock),3(r),4(w),5(pr) 6(pw) > informix@erika $ tbio 5 > > tabname lockreqs isreads, iswrites > pagreads pagwrites > > file_receipt_info 106199886 89230181 82344 > 4706489 37264 > > lin_file_info 38060930 2922778 575699 > 4142726 257744 > > hist_file_receipt_info 374024 62878 79132 > 1080610 18672 > > file_change_notice 19228713 266393 153628 > 652298 46498 > > hist_dataset_info 360009 66525 77168 > 636295 11391 > > file_met -897984453 35756 17139 > 580155 3645 > > cman_cat_deliv 46638700 44097574 202206 > 501897 29115 > > dataset_info_idx1 11561803 543512 0 > 466919 4624 > > dataset_info 49152952 890684 75004 > 456750 24158 > > order_spec 11705929 70416 64216 > 428507 78627 > > cman_cat_deliv_idx1 1185007 222968 0 > 419769 99117 > > #!/usr/local/bin/perl -w > # > #------------------------------------------------------------- > > use DBI; > > $connect = 'dbi:Informix:sysmaster'; > > $dbh = DBI->connect($connect) or die "could not connect to database\\ "; > > $dbh->{ChopBlanks} = 1; > > $dbh->{RaiseError} = 1; > > $dbh->do("Set lock mode to wait 60"); > > $output = *STDOUT; > > $rows = 80; > > $sort = $ARGV[0]; > > # check Usage > > $argNum=@ARGV; > > if ($argNum!=1) { print "Usage: tbio 2(sort by > lock),3(r),4(w),5(pr) 6(pw)\\ "; exit; } > > $where_clause=" tabname matches '[a-z]*' and tabname not like 'sys%'"; > # $where_clause="dbsname='noaa' and tabname matches '[a-z]*' and tabname > not like 'sys%'"; > # and tabname not like '%idx%' and tabname not like 'cdr%' and tabname > not matches '*_ipk'"; > > $stmt= "select first $rows tabname, lockreqs, isreads, iswrites, > pagreads, pagwrites from sysptprof where $where_clause order by $sort desc"; > > $reptab = $dbh->prepare($stmt); > > $reptab->bind_columns(undef,\\\\$tabname,\\\\$lockreqs,\\\\$isreads, > \\\\$iswrites,\\\\$pagreads, \\\\$pagwrites); > > $reptab->execute; > > printf $output "\\ %25s %10s %12s, %12s %10s %10s \\ ", "tabname", > "lockreqs", "isreads","iswrites", "pagreads", "pagwrites"; > > while ($reptab->fetch) { > > printf $output "\\ %25s %10s %12s %12s %10s %10s \\ ", $tabname, > $lockreqs, $isreads, $iswrites, $pagreads, $pagwrites; > > } > > On Tue, Dec 29, 2009 at 12:37 PM, CARLOS BOCCIO <cboccio@yahoo.com.br>wrote: > > > Hi, Mr. Alexandre, > > > > The Informix version is 10.00.FC8W4X5 and the plataform is AIX version 5.3. > > > > I want to know the most acessed (used) indexes. > > > > Regards. > > > > Carlos E. Boccio > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00504502c14d0916b6047be24fb7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, be careful about special workloads which don't run regularly: End of month, end of year, archiving/deleting of old data, rare information reports They might need indexes otherwise unused. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > CARLOS BOCCIO > Gesendet: Dienstag, 29. Dezember 2009 20:17 > An: ids@iiug.org > Betreff: Re: Indexes usage [18503] > > Hi John, > > I need to know the number of times an index has been used (no matter if 1 > row > or 100 rows are processed by the index). > I need this information to do a tuning job in my database tables. > Let me give you a exemple that I want: > > Example: > Table TAB1 has 6 indexes (ix1, ix2...ix6) but the applications (programs) > are > using the indexes ix1, ix2 and ix3. So the indexes ix4, ix5 and ix6 can be > dropped, because nobody use it. > So I need to know how can I identify these indexes in my database tables > (the > indexes that I have more access ou usage). > > Thanks > > Regards, > > Carlos E. Boccio > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, I known that and this kind of job will be done in tables from my online systems. The old datas are extract to others tables and are used by others systems. Thanks for your comments. Regards, Carlos