re: Informix limitations, should we be using Oracle?

I must have been offline during this discussion. I ran across it today looking for something else. Although late, I will add my .02. (embedded). The next time one of these come around and I'm not visible - please alert me to it? cheers j. > > Hi, > > We are trying to implement what will become a multi-terrabyte data > warehouse on Informix XPS but have hit a number of significant > problems. We are at the point where we are considering switching > database providers to Oracle but want to be sure that the problems we > are encountering are indeed valid issues. We have prepared a document > of which I have included an extract that details the issues we are > having. If anyone can provide me with feedback on these issues to let > me know if I'm barking up the wrong tree or if indeed they are issues, > it would be greatly appreciated. We are currently running Informix XPS > 8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11. > > > Informix XPS Issues and Comparison to Oracle > > Performance > > As detailed on TPC websites > http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms > http://www.tpc.org/information/benchmarks.asp We all know about benchmarks - regardless - TPC-C is not the benchmark you would want to use to measure data warehouse performance, you would really want TPC-H, (as you mention below) - but even that is flawed in that it insists upon ongoing transactions (which are not normal warehouse processes) during the benchmark. > > Looking at the TPC-H results for a 1000Gb database running on an > HP9000 Superdome > runs 2.65 times faster than XPS. The pricing of the two databases as > reflected in the Price/QphH also shows that Oracle represents 3.5 > times more 'bang for the buck' than XPS. > Alas these results are long gone, IBM is also not in the business of benchmarking XPS, however; having worked with both databases, I can assure you that XPS will scale infinitely while Oracle will not. > > Issues : > > Memory Management - CRITICAL > > XPS has an essential flaw in it's memory management implementation for > parallel Decision Support System Queries. The Resource Grant Manager > configuration makes it necessary to allocate either large memory > segments or small memory segments to all sessions utilising Decision > Support Resources (large joins, sorts, ordering etc). > > It is expected that intention is so that Decision Support System (i.e. > Warehouse queries - DSS) queries can preallocate huge memory segments > through this configuration. The intent of memory allocation is to pre-allocate resources to intensive queries. This is by no means a requirement for XPS, nor is it a bad thing. With judicious use this memory can be put to very advantageous use in index building, hash joins, groups and sorts - I gather Oracle has a similar capability, I have not seen it clearly put to use yet. > > When DSS queries are issued the memory is allocated up to the > DS_MEMORY_TOTAL. When this occurs, all other DSS queries are queued. > It should be noted that the memory allocated is a fixed amount, > regardless of the complexity or priority of the query being executed - > i.e. Simple counts are allocated the same amount of memory as massive > join and sort queries Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO - which is under the control of the DBA. I realize that with Oracle a 'simple count' requires a full table (or index) scan, with Informix it is a single read against the table header and virtually instantaneous. You can also use a light scan for a filtered read (count(*) ... where condition) - this does not chew up memory. Oracle has no equivalent to the light scan - which is on average 4x faster than a traditional read. Yes, you can give a query enough memory so that other queries are gated and will not interfere with your process while it runs. This is preferable to the thrashing which would occur if this were not an option. > > ETL tools parallelise their processing for enhanced performance. Using > XPS, it is not uncommon that queries issued in the same program may > expend all of the DS_TOTAL_MEMORY and other SQL statements within the > same program are queued. This is the cause of the classic Dawa problem > - 'the locked plan'. Is it not a wonderful thing when you can fully utilize the power of the database and the machine with one query? At the same time you can prevent this from occuring. > > This situation is exacerbated by the fact that the other statements > within the program still retain their memory in Informix - so all SQL > statements within the database are 'locked'. Not quite, only those which require DSS resources, so your 'simple count' would go straight through. > > A resolution to this is to create small memory allocations for each > session. Underallocating the memory segment size causes Reports (which > do lots or GROUPING and ORDERING) to run extremely slowly, or exceed > temp space allocation and fail. Exceeding temp space is a DBA matter - much akin to exceeding the size of a rollback segment under Oracle. Either you are properly sized or you are not. XPS, and all Informix engines, will use what memory is available to the process and swap to disk what is not - this is the same thing that Oracle will do. If you don't have enough disk - well you're SOL. > > Oracle (or Informix IDS) does not employ the Resource Grant Manager > architecture. Small queries use small memory, and large queries use > large amounts of memory as required. This means the database slows > down, but does not lock on memory. Actually IDS and XPS have the same memory allocation features (PDQPRIORITY), although under IDS it's called Memory Grant Manager, but they're the same thing. I have never seen XPS 'lock on memory'. > > It should be noted that XPS (Extended Parallel Server) refers to > parallelism in Platforms - it is evident that for SQL queries that it > is NOT optimised for parallelism. > What have you been smoking? Where do you get this 'it is evident'? XPS performs in parallel with everything, across all horizontal and vertical portions of an operation. It exhibits the highest degree of parallelism that has ever been offered to the public. > Removing Sessions > > Informix XPS 8.31 D has an outstanding bug in which it is not possible > to remove a Informix client session with assurance. Failed attempts to > issue the remove session command have resulted in: > ' reboot of database instance > ' reboot of Unix > ' inability to restart the database instance This is an issue which was corrected in 8.32, which was released 2.5 years ago. At your writing XPS was up to version 8.4 > > Neither does it appear possible to link an Informix client session to > a Unix session id / thread - making it impossible to sessions to be > identified and removed at the Operating System level. And this is a good thing. Alas with Oracle (and db2) all user sessions are operating systems processes, this means that context switching is removed from the control of the database and handed to the operating system