Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this?

On Dec 9 2009, 2:53 pm, Vagner <pontes.vag...@gmail.com> wrote: > Hi, > > The RA_PAGES parameter is the same in version 11.10 and 11.50? Both instances are configured the same. >What is the definition of your DBSPACE ? Did you try 8KB? > This issue is interesting, where is the bootleneck? Do you have WSTATS > parameter enabled? Run your query and sysmaster:sysseswts for your > session. > > Vagner I'll try it and post... On Dec 7 2009, 4:50 pm, Fernando Nunes <domusonl...@gmail.com> wrote: <SNIP> > > But well... The PMR is progressing and the OP will be kept informed of > any conclusions. > > Regards Indeed the PMR is progressing... this is what I got from IBM: the developer said that there is work being done regardles of this issue to, in this particular case, force the optimizer to choose a light scan instead of going for an Index Path. I asked the support engineer to ask the following to the developer: "What escapes my understanding is why not an index path? I wish the developer could explain me why not. I know that light scans are fast, but I doubt that they would be faster than an index in a larger scale (few hundred/thousand out of a few billion records). As far as I understand B-tree indexing, all the null values would be eliminated from the index search, as it wouldn’t be in the list(s) being searched, leaving the balanced cardinality data to reach. That’s why when I count the indexed column, it finds the qualified index records and counts them within seconds. The issue, the way I see it, is somewhere between the record physical address retrieval from the index and the retrieval of the record itself from disk or buffer. Now, I wouldn’t be stressing this issue so much if it wasn’t that the application development team set up a vanilla Oracle database, created the table without any bells or whistles, loaded the same data with the same indexes, and the data came back within seconds, having Oracle’s QO gone for an index path, according to my sources. Yes, I would like to see it with my own eyes too (I mean, Oracle outperforming Informix), and that’s why I’m in the process of setting up a test of my own. I’d be surprised if you guys don’t have an Oracle database laying around somewhere... I’m sure you could setup a test to see what I’m talking about. In consequence, another problem I have with this is that now there is that ominous cloud floating around... Should we migrate to Oracle? Last but not least, the biggest reason why I’m pushing this issue is because this scenario (index on a column or columns with 80% of the values being null, and the other 20% with proper cardinality) is the norm in my environment." Lets see what the developer has to say... I'll keep y'all posted. Ramon.

fandelau wrote: > On Dec 9 2009, 2:53 pm, Vagner <pontes.vag...@gmail.com> wrote: >> Hi, >> >> The RA_PAGES parameter is the same in version 11.10 and 11.50? > Both instances are configured the same. > >> What is the definition of your DBSPACE ? Did you try 8KB? > >> This issue is interesting, where is the bootleneck? Do you have WSTATS >> parameter enabled? Run your query and sysmaster:sysseswts for your >> session. >> >> Vagner > I'll try it and post... > > On Dec 7 2009, 4:50 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > <SNIP> >> But well... The PMR is progressing and the OP will be kept informed of >> any conclusions. >> >> Regards > > Indeed the PMR is progressing... this is what I got from IBM: the > developer said that there is work being done regardles of this issue > to, in this particular case, force the optimizer to choose a light > scan instead of going for an Index Path. > I asked the support engineer to ask the following to the developer: > > "What escapes my understanding is why not an index path? I wish the > developer could explain me why not. I know that light scans are fast, > but I doubt that they would be faster than an index in a larger scale > (few hundred/thousand out of a few billion records). As far as I > understand B-tree indexing, all the null values would be eliminated > from the index search, as it wouldn't be in the list(s) being > searched, leaving the balanced cardinality data to reach. That's why > when I count the indexed column, it finds the qualified index records > and counts them within seconds. The issue, the way I see it, is > somewhere between the record physical address retrieval from the index > and the retrieval of the record itself from disk or buffer. > > Now, I wouldn't be stressing this issue so much if it wasn't that the > application development team set up a vanilla Oracle database, created > the table without any bells or whistles, loaded the same data with the > same indexes, and the data came back within seconds, having Oracle's > QO gone for an index path, according to my sources. Yes, I would like > to see it with my own eyes too (I mean, Oracle outperforming > Informix), and that's why I'm in the process of setting up a test of > my own. I'd be surprised if you guys don't have an Oracle database > laying around somewhere... I'm sure you could setup a test to see what > I'm talking about. In consequence, another problem I have with this is > that now there is that ominous cloud floating around... Should we > migrate to Oracle? > > Last but not least, the biggest reason why I'm pushing this issue is > because this scenario (index on a column or columns with 80% of the > values being null, and the other 20% with proper cardinality) is the > norm in my environment." > > Lets see what the developer has to say... I'll keep y'all posted. > > Ramon. The issue here is not the number of nulls (although Oracle does not index nulls, so the index will be much smaller). The issue here is the physical layout of the data. The tests you're making with Oracle should have this in consideration. I wrote before, if you alter your Informix index to cluster, the query becomes pretty fast, without changing anything else. I have created a test case that shows this and I've sent it to the support team. I can try to push this a bit further... What your situation shows is that we can optimize the way we do index searches when we retrieve a reasonable large number of rows. The performance gains will vary significantly accordingly to the physical layout of the data, the hardware used etc. But in any case, I disagree with the proposed solution of choosing a full scan. That seems to be an easy and blind solution for a specific situation/environment that can cause problems in others. I will transmit this thoughts to R&D/support, but in the end it's their decision. Just please bare in mind that the physical layout of the data (and even the block/page size) is very relevant in this situation. Thanks for reporting this and insisting. The software products in general would be much better if customers do this more often. Of course that should not be necessarily...

The query above is really a warehouse query. In the XPS world, this is a classic skip scan query where the index is read, the rids sorted and the table read in rid order, skipping over large portions of the table when going from rid to rid. IDS does not have this functionality. Maybe some day they'll get smart and dump IDS and bring back XPS? Just wishful thinking! Given the amount of the table being scanned in order to satisify the query and given how well IDS (and XPS) can scan, I think its pretty obvious why IDS performs the sequential scan significantly faster, even with read ahead on. Scanning an index, even with read ahead on, will issue many random i/o's to read all the leaf pages. Further more, as the rids are yanked off the leaf pages, more random i/os are issued for the data pages. Not very efficient. My guess is the i/o sub system is saturated with all the random i/os going on. Since the i/o sub system wasn't specified, I assume its some archaic direct attached storage being used which does no i/o ordering and i/o submission order is crucial to its performance. One can argue that the optimizer should know that the sequential scan should be faster. Can't speak to that cause I would tend to agree. Better slow than never! Pokey On Jan 28, 2:55 am, Fernando Nunes <domusonl...@gmail.com> wrote: > fandelau wrote: > > On Dec 9 2009, 2:53 pm, Vagner <pontes.vag...@gmail.com> wrote: > >> Hi, > > >> The RA_PAGES parameter is the same in version 11.10 and 11.50? > > Both instances are configured the same. > > >> What is the definition of your DBSPACE ? Did you try 8KB? > > >> This issue is interesting, where is the bootleneck? Do you have WSTATS > >> parameter enabled? Run your query  and sysmaster:sysseswts for your > >> session. > > >> Vagner > > I'll try it and post... > > > On Dec 7 2009, 4:50 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > <SNIP> > >> But well... The PMR is progressing and the OP will be kept informed of > >> any conclusions. > > >> Regards > > > Indeed the PMR is progressing... this is what I got from IBM: the > > developer said that there is work being done regardles of this issue > > to, in this particular case, force the optimizer to choose a light > > scan instead of going for an Index Path. > > I asked the support engineer to ask the following to the developer: > > > "What escapes my understanding is why not an index path? I wish the > > developer could explain me why not. I know that light scans are fast, > > but I doubt that they would be faster than an index in a larger scale > > (few hundred/thousand out of a few billion records). As far as I > > understand B-tree indexing, all the null values would be eliminated > > from the index search, as it wouldn’t be in the list(s) being > > searched, leaving the balanced cardinality data to reach. That’s why > > when I count the indexed column, it finds the qualified index records > > and counts them within seconds. The issue, the way I see it, is > > somewhere between the record physical address retrieval from the index > > and the retrieval of the record itself from disk or buffer. > > > Now, I wouldn’t be stressing this issue so much if it wasn’t that the > > application development team set up a vanilla Oracle database, created > > the table without any bells or whistles, loaded the same data with the > > same indexes, and the data came back within seconds, having Oracle’s > > QO gone for an index path, according to my sources. Yes, I would like > > to see it with my own eyes too (I mean, Oracle outperforming > > Informix), and that’s why I’m in the process of setting up a test of > > my own. I’d be surprised if you guys don’t have an Oracle database > > laying around somewhere... I’m sure you could setup a test to see what > > I’m talking about. In consequence, another problem I have with this is > > that now there is that ominous cloud floating around... Should we > > migrate to Oracle? > > > Last but not least, the biggest reason why I’m pushing this issue is > > because this scenario (index on a column or columns with 80% of the > > values being null, and the other 20% with proper cardinality) is the > > norm in my environment." > > > Lets see what the developer has to say... I'll keep y'all posted. > > > Ramon. > > The issue here is not the number of nulls (although Oracle does not > index nulls, so the index will be much smaller). > The issue here is the physical layout of the data. The tests you're > making with Oracle should have this in consideration. > I wrote before, if you alter your Informix index to cluster, the query > becomes pretty fast, without changing anything else. > > I have created a test case that shows this and I've sent it to the > support team. I can try to push this a bit further... > What your situation shows is that we can optimize the way we do index > searches when we retrieve a reasonable large number of rows. The > performance gains will vary significantly accordingly to the physical > layout of the data, the hardware used etc. > But in any case, I disagree with the proposed solution of choosing a > full scan. That seems to be an easy and blind solution for a specific > situation/environment that can cause problems in others. I will transmit > this thoughts to R&D/support, but in the end it's their decision. > > Just please bare in mind that the physical layout of the data (and even > the block/page size) is very relevant in this situation. > > Thanks for reporting this and insisting. The software products in > general would be much better if customers do this more often. Of course > that should not be necessarily...- Hide quoted text - > > - Show quoted text -

pokeyman wrote: > The query above is really a warehouse query. In the XPS world, this is > a classic skip scan query where the index is read, the rids sorted and > the table read in rid order, skipping over large portions of the table > when going from rid to rid. I didn't know XPS did the sort of the rowids. That's good news because... > > IDS does not have this functionality. Maybe some day they'll get smart > and dump IDS and bring back XPS? Just wishful thinking! > ... IBM is porting XPS features into IDS. This would be a good candidate for the list :) Regards.