Informix limitations, should we be using Oracle?
Date: 15 Oct 2002 16:55:21 -0700
Message-ID: <f650c990.0210151555.40c31157_at_posting.google.com>
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
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.
Issues :
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.
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
ETL tools parallelise their processing for enhanced performance. Using
XPS, it is not uncommon that queries issued in the same program may
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'.
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.
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.
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.
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
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.
Oracle, however, runs each session as an easily identifiable Unix process, and sessions can be removed either through DBA commands, or at the Operating System Level.
Exceeding lock count
Informix locks database records using memory latches. A maximum number of memory latches is specified in the config file. When this limit is exceeded the database server crashes.
This is exacerbated when the database restarts and attempt to rollback the transaction that caused the locks to be exceeded - this again exceeds the maximum locks. In our experience, the only solution is to destroy and recreate the database.
Oracle does not use memory latches to lock records. It's standard row-level-locking is managed in the rollback segment architecture. Exceeding the rollback segment will not crash the database - it causes the session to rollback the current SQL transaction.
Lack of diagnostic/monitoring tools
No user friendly or graphical monitoring tools are available. This makes diagnosis and performance monitoring extremely difficult.
Many monitoring and diagnostic tools are available for Oracle. Many of them are free. Administration, diagnosis and monitoring is vastly simplified.
Raw partitions exacerbate the problem.
Raw partitions and storage management
Standard data storage in Informix is via RAW unix partitions. These partitions cannot be monitored via standard Unix utilities. They are more difficult to manage and create - Unix 'root' privileges are required to manage them. Furthermore, raw partitions used by Informix cannot be resized or grown. A completely new partition must be allocated.
Informix can use cooked partitions, but it is not recommended by Informix for performance reasons.
Oracle standard data storage is via datafiles, which are 'normal' unix files. These files can be monitored for growth and utilisation much as any other Unix file. No special system privileges are required to add or resize datafiles.
Fast Loading
Our data warehouse employs fast loading capabilities (bypassing ODBC) in order to upload Mainframe EBCDIC data.
This feature is particular in Informix to XPS; in Oracle it is standard functionality.
The reject output from Informix is a single-file mixture of ASCII error report and EBCDIC data. It is impossible to re-upload rejected records from an Informix EBCDIC reject file - All packed decimal, for instance, is corrupt.
Oracle produces two output files when EBCDIC uploading - an error log, and an EBCDIC reject file - ready for reprocessing.
We currently have to reprocess complete datasets, or reject complete datasets. With Oracle this would not be necessary.
The administration of the External Table definitions in Informix is excessively onerous also. For instance - 6 sales invoice files for three states necessitates 18 external table definitions and 18 internal table definitions. Addition of EBCDIC datafiles for other states necessarily implies additional external and internal definitions.
With Oracle only 6 internal tables are necessary - generic to the Invoice, they could be used for all states. This vastly simplifies Data Dictionary management.
Limitation comparisons
Informix XPS
XPS - No true database flatfile Import Export
Varchar fields not treated as ANSI standard
No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
Oracle
18 characters for database objects
256 byte maximum row size in Indexes
255 byte limit on varchar fields
SQL Restrictions -
Cannot SELECT NULL
No general DUAL table
No MINUS operations
No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
EMBEDDED QUERY
No Full OUTER JOIN
No Implicit ROWID
Stored procedures are not prepared - Object dependence not checked
until runtime
Page size can only be 2 or 4k
30 characters - more meaningful names
Unlimited
4000 limit
No comparable restrictions
Flatfile IMPORT EXPORT available for backups and rebuilds
No comparable ANSI incompatibility
DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
Stored procedures are compiled as PCODE
Page size can be as large as 64K - 16 times fewer reads for DSS table
scans