Re: Informix limitations, should we be using Oracle?

freetheshrimps_at_yahoo.com.au (Simon M.) wrote in message news:<f650c990.0210151555.40c31157_at_posting.google.com>... ...
> 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.

You probably should use a newer version of XPS. 8.40 is the latest version of XPS.

> Informix XPS Issues and Comparison to Oracle
 ...
> 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

If you use implicit PDQ priority, XPS will allocate memory according to the specific memory need of each query. If all memory is already allocated, only queries which require memory have to wait. All DSS queries which don't require memory (e.g. queries with PDQ 0 and please note that PDQ 0 queries can be parallel queries in XPS) can start running immediately.

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

see above.

...

>
> 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 executes everything in parallel by default.  

[ Many more wrong assumptions about XPS deleted ]

Andreas Received on Wed Oct 16 2002 - 19:04:59 CEST