Informix Dynamic Server 11: Advanced Functionality for Modern Business

8 Informix Dynamic Server 11: Advanced Functionality for Modern Business

Included in the memory structures for an instance are cached disk access plans

for the IDS cost-based optimizer. In most OLTP environments, the same SQL

operations are executed throughout the processing day, although with slightly

different variable conditions, such as customer number and invoice number.

Each time an SQL operation executes, the data server optimizer must determine

the fastest way to access the data. Obviously if the data is already cached in the

memory buffers, it is retrieved from there; if not, disk access is required. When

this occurs, the optimizer has to decide on the quickest way to get the requested

data. It needs to evaluate whether an index exists pointing directly to the

requested data or if the data has been intelligently fragmented on disk restricting

the possible number of dbspaces through which to look. When joining data from

several tables, the optimizer evaluates which table provides the data to which the

others join and so on. While not really noticeable to users, these tasks take time

to execute and affect response time.

Informix Dynamic Server provides a caching mechanism where data I/O plans

can be stored for reuse by subsequent executions of the same operation. Called,

appropriately enough, the

SQL Statement Cache, this allocation of instance

memory stores the SQL statement and the optimizer’s determination of the

fastest way to execute the operation. The size of this cache is configurable, as

well as when an individual SQL operation is cached. Generally speaking, most

configurations cache the operation after it has executed three or more times to

prevent filling the cache with single-use operations. The cache can be flushed

and refreshed if needed while processing continues.

With dynamic reconfiguration of memory allocations, intelligent buffer

management, caching of SQL access plans, and a number of other technologies,

Informix Dynamic Server provides unmatched efficiency and scalability of system

memory resources.

1.1.3 DSA components: Intelligent data fragmentation

The parallelism and scalability of the DSA processor and memory components

are supported by the ability to perform asynchronous I/O across database tables

and indexes that have been logically partitioned. To speed up what is typically

the slowest component of data processing, IDS uses its own asynchronous I/O

(AIO) feature, or the operating system’s kernel AIO when available. Because I/O

requests are serviced asynchronously, Virtual Processors do not have to wait for

one I/O operation to complete before starting work on another request. To

ensure that requests are prioritized appropriately, four specific classes of Virtual

Processors (VPs) are available to service I/O requests:

򐂰 Logical log I/O

򐂰 Physical log I/O