PSORT_NPROCS environment variable

The PSORT_NPROCS environment variable specifies the maximum number of threads that the database server can use to sort a query. When a query involves a large sort operation, multiple sort threads can execute in parallel to improve the performance of the query.

When the value of PDQ priority is 0 and PSORT_NPROCS is greater than 1, the database server uses parallel sorts. The management of PDQ does not limit these sorts. In other words, although the sort is executed in parallel, the database server does not regard sorting as a PDQ activity. When PDQ priority is 0, the database server does not control sorting by any of the PDQ configuration parameters.

When PDQ priority is greater than 0 and PSORT_NPROCS is greater than 1, the query benefits both from parallel sorts and from PDQ features such as parallel scans and additional memory. Users can use the PDQPRIORITY environment variable to request a specific proportion of PDQ resources for a query. You can use the MAX_PDQPRIORITY parameter to limit the number of such user requests. For more information about MAX_PDQPRIORITY, see Limiting PDQ resources in queries.

The database server allocates a relatively small amount of memory for sorting, and that memory is divided among the PSORT_NPROCS sort threads. Sort processes use temporary space on disk when not enough memory is allocated. For more information about memory allocated for sorting, see Estimating memory needed for sorting.

Important: For better performance for a sort operation, set PSORT_NPROCS initially to 2 if your computer has multiple CPUs. If the subsequent CPU activity is lower than I/O activity, you can increase the value of PSORT_NPROCS.

For more information about sorts during index builds, see Improving performance for index builds.