Using set sort_resources on

The set sort_resources on command can help you understand how the sort manager performs parallel sorting for create index statements. You can use it before creating an index to determine whether you want to increase configuration parameters or specify additional consumers for a sort.

After you use set sort_resources on, Adaptive Server does not actually create indexes, but analyzes resources, performs the sampling step, and prints detailed information about how Adaptive Server would use parallel sorting to execute the create index command. Table 9-2 describes the messages that can be printed for sort operations.

Table 9-2: Basic sort resource messages

Message

Explanation

See

The Create Index is done using sort_type

sort_type is either “Parallel Sort” or “Serial Sort.”

“Requirements and resources overview”

Sort buffer size: N

N is the configured value for the number of sort buffers configuration parameter.

“Sort buffer configuration guidelines”

Parallel degree: N

N is the maximum number of worker processes that the parallel sort can use, as set by configuration parameters.

“Caches, sort buffers, and parallel sorts”

Number of output devices: N

N is the total number of database devices on the target segment.

“Disk requirements”

Number of producer threads: N

N is the optimal number of producer processes determined by the sort manager.

“Worker process requirements for parallel sorts”

Number of consumer threads: N

N is the optimal number of consumer processes determined by the sort manager.

“Worker process requirements for parallel sorts”

The distribution map contains M element(s) for N partitions.

M is the number of elements that define range boundaries in the distribution map. N is the total number of partitions (ranges) in the distribution map.

“Creating a distribution map”

Partition Element:N
value

N is the number of the distribution map element. value is the distribution map element that defines the boundary of each partition.

“Creating a distribution map”

Number of sampled records: N

N is the number of sampled records used to create the distribution map.

“Creating a distribution map”