SQL/MP Query Guide

Selectivity and Cost Estimates

HP NonStop SQL/MP Query Guide—524488-003

5-13

Cost of Record Overhead

The cost of record overhead is the processor time, expressed in terms of physical I/Os,

associated with handling rows. This estimate includes the cost of setting up various

control blocks and is dependent on the number of rows examined. The cost equals the

following:

overhead per row × the number of rows to examine

For example, suppose that a processor can perform 2,000,000 instructions per second,

and its disks can perform 30 I/Os per second. If 2,000 instructions are required before

a row can be examined, the overhead per row would be approximately 0.03 I/Os

(2,000 instructions would take one millisecond and is approximately the amount of time

needed to perform 0.03 physical I/Os). If 10,000 rows must be examined, the cost of

row overhead would be 300.

Cost of Messages

The cost of messages is the processor time, expressed in terms of physical I/Os, spent

in sending messages between the file system and the disk process. This measurement

is dependent on the type of SBB being used.

SQL computes the cost of messages as the following:

cost per message × number of messages

The cost per message is a weighing factor computed similarly to overhead per record.

The cost of messages is influenced by the number of columns projected and the

number of rows selected for VSBB. If grouping or aggregation takes place in the disk

process, a considerable reduction in messages occurs over aggregation that takes

place in the executor.

Cost of Data Transfer

The cost of data transfer is the estimated elapsed time, expressed in terms of physical

I/Os, for transferring data from the disk process (possibly remote) to the file system. In

general, transfer cost is negligible for local transfers; it becomes substantial with

remote transfers. This cost is computed as the following:

transfer rate × amount of data to be transferred

For example, suppose that 4,000 bytes are to be transferred from a remote node to the

local node and that the two nodes are connected by one 4 kilobits per second

communication line. Using a disk transfer rate of 30 I/Os per second, the cost of data

transfer is 240.