Re: Informix limitations, should we be using Oracle?

Tim,
But this is an Oracle newsgroup so clearly Oracle is better. :-) (reading comp.databases.oracle) Even though I've never used Informix, clearly Oracle is better.
Jim
"Tim Schaefer" <tim_at_datad.com> wrote in message news:KV3r9.130936$nE3.60074_at_atlpnn01.usenetserver.com...
> You state that you are using XPS on only one node.
> Why aren't you clustering?
>
> You are using a multi-node product on one box, like
> buying a multi-engine boat and only using one motor
> then complaining that nobody can ski because the
> boat is too big.
>
> You are also wrong about flatfile support, XPS has
> table loading via external tables. Geez, did you
> even read the docs and understand the product?
>
> I don't think you actually even have XPS, only read
> about it in a magazine. Your comments suggest you
> don't even have XPS installed. The benchmarks are
> also outdated, but to even bother commenting on the
> rest of your post would suggest you even know what
> XPS is.
>
> Tim
>
>
> "Simon M." <freetheshrimps_at_yahoo.com.au> wrote in message
 news: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 :
> >
> > 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.
> >
> > 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
> > 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'.
> >
> > 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
> > 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
> >
> > XPS - No true database flatfile Import Export
> > Varchar fields not treated as ANSI standard
> > No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
> > Additional processing
> > Stored procedures are not prepared - Object dependence not checked
> > until runtime
> > Page size can only be 2 or 4k
> >
> > Oracle
> > 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
> >
> > Warehouse functional comparisons
> >
> > Informix
> > Tables and related indexes are partitioned on the same key -
> > inflexible configuration
> > GK Indexes - Index prejoin only
> > Necessary to manage security utilising both OS and database levels
> > Sagent supports ODBC only for Informix
> >
> > Oracle
> > Partitioning tables and indexes on alternate keys - flexible
> > configuration for performance
> > Materialised Views - Database managed JOINS of tables - Physically
> > existing summary tables for enhanced performance. Time savings in
> > aggregations
> > Security and permission totally managed within the database
> > Sagent support for High Speed Loading into Oracle
>
>
>
Received on Wed Oct 16 2002 - 04:30:26 CEST