Re: Informix limitations, should we be using Oracle?

After Obnoxio's excellent post I don't get to say much :) ...

Answers embedded

freetheshrimps_at_yahoo.com.au (Simon M.) wrote in message news:<f650c990.0210151555.40c31157_at_posting.google.com>... <SNIP>
> 8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11.

Like everyone else says, ick, only 4 CPU's???

>
> 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

I am not a big TPC fan...

<SNIP>

> 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

You can control what percent batch processes and users take up with PDQPRIORITY, I haven't seen many issues with this...
>
> 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'.
>

I guess I don't often see a program doing more than one or two huge queries at once... I have never seen on eprogram use all of DS_TOTAL_MEMORY, maybe I am sheltered. Seems more like a theoretical possibility.

<SNIP>

>
> 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.

XPS is not an OLTP system optimized for 100's of concurrent big queries. How one would try to do lots of concurrent large queries on a 4 cpu box is beyond my intellectual ability :) .

>
> 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 problem existed on 8.30UC3X1. In the year we have been on 8.31,8.32 I have _never_ seen this problem.

>
> 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.

Not true from what I have seen. I use onstat -g ses all the time to match up a session with a unix process.

<SNIP>
> 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.

I haven't seen a crash due to running out of locks since I was on 7.10.

>
>
> 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.

No GUI tools, but some good command line ones!

>
> 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.

iostat and sar both seem to work pretty well at monitoring i/o, last I heard they were standard unix utilities...

>
> 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.

The performance implications are there for Oracle too...

>
> 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.
>

I have never dealt much with EBCDIC, but I haven't seen any loaders faster than Informix's, I am not saying there aren't any... I just haven't seen it.

<SNIP>

>
> XPS - No true database flatfile Import Export

external tables are good enough for me... <SNIP>
>
> Informix
> Tables and related indexes are partitioned on the same key -

As obnoxio said, not true.

<SNIP>

Hope this helps,
Will

P.S. We have been quite happy with the performance we have gotten out of XPS, but we have a bit more than 4 CPU's. I figure you need to run a single node, but I am still fairly confident you can get at least comprable, if not better performance with Informix. There is lots of help on this news group, it just takes awhile to get a response. Received on Fri Oct 18 2002 - 17:30:41 CEST