Re: Informix limitations, should we be using Oracle?

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:12:47 CEST