Re: Informix limitations, should we be using Oracle?

Jim,

I wasn't debating whether Oracle or Informix is better, but merely pointed out that the original poster made statements that indicated they didn't know what the f*** they were talking about regarding XPS. Personally I think XPS is superior to Oracle but for reasons that would probably surprise you, such as Informix is easier to use than Oracle, has a better architecture, is a newer engine than even Oracle 9i, etc. But XPS probably isn't better at slide presentations, where Oracle reigns supreme.

Thanks,

Tim

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:6b4r9.54285$Fz.163137_at_rwcrnsc51.ops.asp.att.net...
> 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:35:14 CEST