onstat -g ppf - lkrqrs X isrd X bfrd

Hi, I'm trying to identify useless indexes on the database. (v10 and v11) For this I'm using the 'onstat -g ppf' where I have filtered indexes partnum what the isrd equal to 0 (isam read) . With this, I deduce, any SQL use this index for access the table since the statistics was zeroed (onstat -z). But I can see somes bfrd and lkrqrs with values on some indexes... I supose this two columns have values because occur updates/deletes into the table where affect the ROWID or the field of the index and for this the engine need to lock, read and update this indexes (but this not occur via "isam read" because is some internal process of the engine) This is correct? Cesar

Consider that a write requires a read. Looking for 0 reads could be misleading. Look for indexes where the number of reads is equivalent to the number of writes. j. Feb 23, 2011 02:58:57 PM, ids@iiug.org wrote: =========================================== Hi, I'm trying to identify useless indexes on the database. (v10 and v11) For this I'm using the 'onstat -g ppf' where I have filtered indexes partnum what the isrd equal to 0 (isam read) . With this, I deduce, any SQL use this index for access the table since the statistics was zeroed (onstat -z). But I can see somes bfrd and lkrqrs with values on some indexes... I supose this two columns have values because occur updates/deletes into the table where affect the ROWID or the field of the index and for this the engine need to lock, read and update this indexes (but this not occur via "isam read" because is some internal process of the engine) This is correct? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The reason of this isrd x bfrd is because the statistics was zeroed a few days ago (onstat -z), so probally the pages of this indexes already buffered since then... Well, some of this indexes have the same value of bfwrt and bfrd... Thanks Jack for your suggestion. anyone else? --- Em qua, 23/2/11, jack.parker4@verizon.net <jack.parker4@verizon.net> escreveu: De: jack.parker4@verizon.net <jack.parker4@verizon.net> Assunto: Re: onstat -g ppf - lkrqrs X isrd X bfrd [22868] Para: ids@iiug.org Data: Quarta-feira, 23 de Fevereiro de 2011, 12:55 Consider that a write requires a read. Looking for 0 reads could be misleading. Look for indexes where the number of reads is equivalent to the number of writes. j. Feb 23, 2011 02:58:57 PM, ids@iiug.org wrote: =========================================== Hi, I'm trying to identify useless indexes on the database. (v10 and v11) For this I'm using the 'onstat -g ppf' where I have filtered indexes partnum what the isrd equal to 0 (isam read) . With this, I deduce, any SQL use this index for access the table since the statistics was zeroed (onstat -z). But I can see somes bfrd and lkrqrs with values on some indexes... I supose this two columns have values because occur updates/deletes into the table where affect the ROWID or the field of the index and for this the engine need to lock, read and update this indexes (but this not occur via "isam read" because is some internal process of the engine) This is correct? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You might also check into the sysmaster DB, there you can find the page reads/ page writes for each dataspace (including indices). Might be easier to script. Something like: #Write more than read select c.tabname, a.indexname, c.pagreads, c.pagwrites from sysfragments a, sysmaster:sysptprof c where a.indexname is not null and a.partn=c.partnum and pagwrites > pagreads j. Feb 23, 2011 04:16:20 PM, ids@iiug.org wrote: =========================================== The reason of this isrd x bfrd is because the statistics was zeroed a few days ago (onstat -z), so probally the pages of this indexes already buffered since then... Well, some of this indexes have the same value of bfwrt and bfrd... Thanks Jack for your suggestion. anyone else? --- Em qua, 23/2/11, jack.parker4@verizon.net escreveu: De: jack.parker4@verizon.net Assunto: Re: onstat -g ppf - lkrqrs X isrd X bfrd [22868] Para: ids@iiug.org Data: Quarta-feira, 23 de Fevereiro de 2011, 12:55 Consider that a write requires a read. Looking for 0 reads could be misleading. Look for indexes where the number of reads is equivalent to the number of writes. j. Feb 23, 2011 02:58:57 PM, ids@iiug.org wrote: =========================================== Hi, I'm trying to identify useless indexes on the database. (v10 and v11) For this I'm using the 'onstat -g ppf' where I have filtered indexes partnum what the isrd equal to 0 (isam read) . With this, I deduce, any SQL use this index for access the table since the statistics was zeroed (onstat -z). But I can see somes bfrd and lkrqrs with values on some indexes... I supose this two columns have values because occur updates/deletes into the table where affect the ROWID or the field of the index and for this the engine need to lock, read and update this indexes (but this not occur via "isam read" because is some internal process of the engine) This is correct? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.