Configuration parameters and environment variables that affect CPU utilization

Some configuration parameters and environment variables affect CPU utilization. You might need to adjust the settings of these parameters and variables when you consider methods of improving performance.

The following configuration parameters in the database server configuration file have a significant impact on CPU utilization:
  • DS_MAX_QUERIES
  • DS_MAX_SCANS
  • FASTPOLL
  • MAX_PDQPRIORITY
  • MULTIPROCESSOR
  • NETTYPE
  • OPTCOMPIND
  • SINGLE_CPU_VP
  • VPCLASS
  • VP_MEMORY_CACHE_KB
The following environment variables affect CPU utilization:
  • OPTCOMPIND
  • PDQPRIORITY
  • PSORT_NPROCS

The OPTCOMPIND environment variable, when set in the environment of a client application, indicates the preferred way to perform join operations. This variable overrides the value that the OPTCOMPIND configuration parameter sets. For details on how to select a preferred join method, see Optimizing access methods.

The PDQPRIORITY environment variable, when set in the environment of a client application, places a limit on the percentage of CPU VP utilization, shared memory, and other resources that can be allocated to any query that the client starts.

A client can also use the SET PDQPRIORITY statement in SQL to set a value for PDQ priority. The actual percentage allocated to any query is subject to the factor that the MAX_PDQPRIORITY configuration parameter sets. For more information about how to limit resources that can be allocated to a query, see Limiting PDQ resources in queries.

PSORT_NPROCS, when set in the environment of a client application, indicates the number of parallel sort threads that the application can use. The database server imposes an upper limit of 10 sort threads per query for any application. For more information about parallel sorts and PSORT_NPROCS, see Configure dbspaces for temporary tables and sort files.