Limiting the number of PDQ scan threads that can run concurrently

The DS_MAX_SCANS configuration parameter limits the number of PDQ scan threads that can run concurrently. This configuration parameter prevents the database server from being flooded with scan threads from multiple decision-support queries.

To calculate the number of scan threads allocated to a query, use the following formula:

scan_threads = min (nfrags, (DS_MAX_SCANS * pdqpriority / 100 
   * MAX_PDQPRIORITY / 100) )

nfrags: is the number of fragments in the table with the largest number of fragments.
pdqpriority: is the PDQ priority value set by either the PDQPRIORITY environment variable or the SQL statement SET PDQPRIORITY.

Reducing the number of scan threads can reduce the time that a large query waits in the ready queue, particularly when many large queries are submitted concurrently. However, if the number of scan threads is less than nfrags, the query takes longer once it is underway.

For example, if a query needs to scan 20 fragments in a table, but the scan_threads formula lets the query begin when only 10 scan threads are available, each scan thread scans two fragments serially. Query execution takes approximately twice as long as if 20 scan threads were used.