Re: Unused Index Search

Just to clarify a little more. In version 11 for detached indexes you have some additional information. First you have the partitions stats in onstat -g ppf of sysmaster:sysptprof which have the isread, iswrite and isdelete which will be populated for each index item selected, inserted and deleted. The other place I would look is onstat -C part. This will show you how many times we have used an index to position on a specific index item, along with index compress (combining pages) and splits pages. John F. Miller III STSM, Embedability Architect ids-bounces@iiug.org wrote on 12/13/2012 01:59:34 PM: > From: Jacques Renaut/Lenexa/IBM@IBMUS > To: ids@iiug.org > Date: 12/13/2012 02:02 PM > Subject: Re: Unused Index Search [29077] > Sent by: ids-bounces@iiug.org > > Original post: > > I'm looking at index usage and have a question about onstat -g ppf output. > > It has both a "Reads" and "Buffer Reads" column. I have some indexeswith zero > "Reads" but, in a few cases, many thousands (and even millions) of "Buffer > Reads". > > This doesn't make much sense to me if I assume "Buffer Reads" are a subset of > "Reads", which I expect to be the sum of disk+buffer)....so I have to assume > this logic isn't right. > > So, the question is, why are Buffer Reads > 0 when Reads are = 0, and are > these really in use. > > This is 11.70.FC2. > > Response: > > Ok, in a quick test that I did (on 11.50.FC9 was the first server I > found that > I had up already). I found the isrd column appeared to only be bumped if I > used a query that actually used the index (it would bump both isrd and > bfrd's). However, if I just inserted a row into the table, it has toinsert a > item into the index, and in that case, bfrd + bfwrt + iswrt would get bumped. > So my quick testing would seem to show that if you have an index butthe index > isn't getting used for any queries, isrd wouldn't be getting bumped, but > bfrd's would if you are inserting/updating/deleting rows (well updates that > would update the indexed column). > > All I did to test this was create a table (2 columns) with 1 detached index > (on 1 of the 2 columns). Get the hex partnums, inserted a couple > rows into the > table. Zero'd out my stats with onstat -z. Then used set explain and ran a > query that I believed would use the index I had. After running, > checked onstat > -g ppf. Zero'd stats, then ran a sequential scan (verifying in my set explain > output that the index was used or not in both the above cases). Checked the > stats. Zero'd stats, and then inserted another row...checked stats. > > Jacques Renaut > IBM Informix Advanced Support > APD Team > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >