DS_MAX_SCANS configuration parameter

Use the DS_MAX_SCANS configuration parameter to limit the number of PDQ scan threads that the database server can execute concurrently.

onconfig.std value
DS_MAX_SCANS 1048576 or (1024 * 1024)
values
10 - (1024 * 1024)
units
Number of PDQ scan threads
takes effect
After you edit your onconfig file and restart the database server.
When you reset the value dynamically in your onconfig file by running the onmode -wf command.
When you reset the value in memory by running the onmode -wm command.

Usage

When a user issues a query, the database server apportions some number of scan threads, depending on the following values:
  • The value of PDQ priority (set by the environment variable PDQPRIORITY or the SQL statement SET PDQPRIORITY)
  • The ceiling that you set with DS_MAX_SCANS
  • The factor that you set with MAX_PDQPRIORITY
  • The number of fragments in the table to scan (nfrags in the formula)
The Memory Grant Manager (MGM) tries to reserve scan threads for a query according to the following formula:
reserved_threads = min (nfrags, (DS_MAX_SCANS *
                   PDQPRIORITY / 100 *
                  MAX_PDQPRIORITY / 100) )

If the DS_MAX_SCANS part of the formula is greater than or equal to the number of fragments in the table to scan, the query is held in the ready queue until as many scan threads are available as there are table fragments. Once underway, the query executes quickly because threads are scanning fragments in parallel.

For example, if nfrags equals 24, DS_MAX_SCANS equals 90, PDQPRIORITY equals 50, and MAX_PDQPRIORITY equals 60, the query does not begin execution until nfrags scan threads are available. Scanning takes place in parallel.

If the DS_MAX_SCANS formula falls below the number of fragments, the query might begin execution sooner, but the query takes longer to execute because some threads scan fragments serially.

If you reduce DS_MAX_SCANS to 40 in the previous example, the query needs fewer resources (12 scan threads) to begin execution, but each thread needs to scan two fragments serially. Execution takes longer.


Copyright© 2019 HCL Technologies Limited