Statistics crashing updating resource allocation
The client was amazed, and everyone wanted to know exactly how this happened. It is an Informix Engine capability designed to allow a DBA to designate and manage memory on systems with many large queries: specifically, Decision Support Systems (DSS systems).
This article arose from all the research, documentation and testing involved with an extended period of performance tuning a large data warehouse, and getting my certification. When using PDQ, the DBA designates a chunk of memory as being available only to PDQ queries, then designates how much each query is allowed to have.
There are two ways of handling this: When tuning a system that is running almost exclusively OLTP type queries, it is best to allocate as much memory to BUFFERS as possible, and avoid using the PDQ.
Since I/O is a very common bottleneck on systems, this can result in a HUGE power boost to your system.I found a whole bunch of settings I figured could be tweaked, but nothing major, until I came across a large block of memory allocated to something called PDQ that was otherwise not configured. After some frantic reading, this PDQ thing seemed to be just the break I was looking for.I activated the PDQ, setting it for 10 concurrent queries, and set the PDQPRIORITY environment variable equal to 100 in the stress testers login script.The following functions can be done in parallel using PDQ: Scans, Inserts, Joins, Sorts, Aggregates and Groups.This can give some advantage on a single processor server, kicking off a Join Thread while waiting on a Scan thread to complete.