Re: What is happening to Informix?

Regarding this aspect, I think some examples may help to understand some differences... Not sure if I mentioned this here before... Sometime ago a query was catched running for something like two days (timings may be a bit imprecise) ... The DBA inquired the user who said that it usually took around 7 days (!!!). After some extrapolation I'd say it would take around 30 days (the data set was larger than usual). Naturally all the team got interested in that query... And we analyzed it... It was terribly simple, but the data was really nasty and caused a query plan that was horrible. By re-writing the query one of the teams DBA ("junior" but I'd change most "seniors" for this one) it run in 30m (exact time here). He spent maybe another 30m analyzing the query... As I said, this team is demanding and does not accept this as a final solution... The engine was clearly under-performing. So they contacted me when I got on site... More anaylsis, and yes... the query plan sucks and Informix could not get a better one... I decided to open a PMR, but again, since we're in a room full of all kinds of DBAs I decided to chat with the Oracle team leader. Trust me. This one is very good... The only problem with him is his bad taste for database technology :) I gave him the data in the tables, he loaded it, and started to experiment all kinds of directives... Now... Oracle (don't recall the version) has a neat feature... As it runs the query it estimates the time it will take... started out with a few minutes and he gave up when the estimation got to around two months. Again, a very strange dataset and the fact that we had a join between two tables with a condition like: (tab1.col1 = tab2.col1 or tab1.col1 is NULL and tab2.col1 IS NULL) killed the query performance. For the readers understanding, this condition will prevent any database (I was able to check for Informix, DB2, Oracle and Netezza) of using an HASH join... So you get a full scan or an index join (the index contained columns included in the other conditions). Again, this was trivial to solve by changing the SQL. But the really important part: Informix tech support accepted the PMR, validated my input, reproduced the problem in house (no data, just the statistics) and agreed that some improvements should be made in the optimizer. from what I recall, if you're unfortunate to have this problem, but you're fortunate to have 11.70.xC6 (when it get out), the query plan choose by the engine will take around 1H (in the same hardware as before). It was not feasible to reach the query plan after the SQL change because it used a UNION... They did this for a reported performance issue that was not causing problems at the customer. Of course it was not considered urgent. It will be a "future" "bug" fix and it took a few weeks to get the feedback. After all this I was chatting with the Oracle DBA and explained him what we had done... and I asked him if he didn't consider to open a problem... He laugh. Got it? On Thu, Aug 23, 2012 at 10:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > I agree with Fernando. I have open cases with Oracle and Sybase dating > back to 1995 and 1991 respectively. OK, they probably closed them by now, > but the point is that I NEVER GOT AN RESOLUTION TO THOSE TWO PROBLEMS! > Informix had the best support in the industry BEFORE 1991 and honestly, > even though I don't think the past 11 years with IBM have been a picnic, > support HAS gotten better not worse (OK except for that first year or two > when you-know-who was trying to kill the product). > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. > > > > On Thu, Aug 23, 2012 at 5:31 PM, Fernando Nunes <domusonline@gmail.com>wrote: > >> If you feel better, than it's good. For the readers, this will probably >> be a politically incorrect post... >> >> >> On Thu, Aug 23, 2012 at 8:45 PM, <davidegrove@gmail.com> wrote: >> >>> I'm sorry, I just have to vent a bit. >>> >>> For the second time in 6 months, we have just experienced Informix >>> crashing (assert failures) due to iniput of a syntactically and >>> semantically correct SQL statement. The first time, we were on 11.5 (FC7, >>> I think), tech support said the problem would be fixed in the next release. >>> Two days ago (a few hours after we upgraded to 11.7FC5) another crash due >>> to inability of Informix to handle proper SQL (this one had some LEFT OUTER >>> JOINs). This time we got a "W" release, and the problem is solved... >>> >> >> Any crash, and I really mean any crash is a reason to be upset. But you >> somehow seem to imply that it would be ok if the statement had some kind of >> problem. And this really surprises me. A database engine has a lot of >> things to do... It has to parse the statement, check the parameters (for >> prepared statements), check the security, calculate the query plan and >> execute it. All this steps are done by code, and any code may contain bugs. >> From my point of view, parsing the code and the parameters is the easier >> part. Calculate the query plan and execute it is a much more complex task >> and as such much more error prone. Should it crash? Of course not, but it's >> a much more probable phase than the ones before. >> >> >>> >>> BUT, it is simply astounding to me, and unfathomable, that a mature >>> database engine could be crashed by the input of proper SQL. >>> >> >> A crash before is much more strange. And I'm sure it happened before also. >> >> >>> >>> I am left defensless in responding to a new hotshot develooper who has >>> nothing but contempt for Informix ("a toy database") because his SQL has >>> crashed the system twice in 6 months. In the past 10 years, Informix has >>> been like a rock. I can't even recall one crash. But, recently... what's >>> happening? >>> >> >> You could suggest that developer to google for other database bug list... >> He will surely get a different perspective. Once he does that, ask him to >> open a problem at their support and compare the time it takes, and the >> answers he gets. From your said history you hit two different bugs that IBM >> was able to identify and fix. I spent half to two thirds of my customer >> facing time (most of my working time) in an environment with at least 4 >> different types of databases. Interestingly enough apparently only Informix >> has open problems with support... why? Not because it crashes (I could get >> you uptimes of really interesting environments), but simply because they >> constantly change the use and test new features and we hit issues and >> doubts. And specially because they almost gave up opening tickets in other >> vendors because of the terrible supp