The default value for the PDQ priority of individual applications is 0, which means that PDQ processing is not used. The database server uses this value unless one of the following actions overrides it:
The PDQPRIORITY environment variable and the MAX_PDQPRIORITY configuration parameter work together to control the amount of resources to allocate for parallel processing. Setting these configuration parameters correctly is critical for the effective operation of PDQ.
The MAX_PDQPRIORITY configuration parameter allows the database server administrator to limit the parallel processing resources that DSS queries consume. Thus, the PDQPRIORITY environment variable sets a reasonable or recommended priority value, and MAX_PDQPRIORITY limits the resources that an application can claim.
The MAX_PDQPRIORITY configuration parameter specifies the maximum percentage of the requested resources that a query can obtain. For instance, if PDQPRIORITY is 80 and MAX_PDQPRIORITY is 50, each active query receives an amount of memory equal to 40 percent of DS_TOTAL_MEMORY, rounded down to the nearest quantum. In this example, MAX_PDQPRIORITY effectively limits the number of concurrent decision-support queries to two. Subsequent queries must wait for earlier queries to finish before they acquire the resources that they need to run.
An application or user can use the DEFAULT tag of the SET PDQPRIORITY statement to use the value for PDQ priority if the value has been set by the PDQPRIORITY environment variable. DEFAULT is the symbolic equivalent of a -1 value for PDQ priority.
You can use the onmode command-line utility to change the values of the following configuration parameters temporarily:
These changes remain in effect only as long as the database server remains up and running. When the database server is initialized, it uses the values listed in the ONCONFIG file.
For more information about the preceding parameters, see Effect of Configuration on Memory Utilization. For more information about onmode, see your IBM Informix: Dynamic Server Administrator's Reference.
If you must change the values of the decision-support parameters on a regular basis (for example, to set MAX_PDQPRIORITY to 100 each night for processing reports), you can use a scheduled operating-system job to set the values. For information about creating scheduled jobs, see your operating-system manuals.
To obtain the best performance from the database server, choose values for the PDQPRIORITY environment variable and MAX_PDQPRIORITY parameter, observe the resulting behavior, and then adjust the values for these parameters. No well-defined rules exist for choosing these environment variable and parameter values. The following sections discuss strategies for setting PDQPRIORITY and MAX_PDQPRIORITY for specific needs.
The MAX_PDQPRIORITY configuration parameter limits the PDQ priority that the database server grants when users either set the PDQPRIORITY environment variable or issue the SET PDQPRIORITY statement before they issue a query. When an application or an end user attempts to set a PDQ priority, the priority that is granted is multiplied by the value that MAX_PDQPRIORITY specifies.
Set the value of MAX_PDQPRIORITY lower when you want to allocate more resources to OLTP processing. Set the value higher when you want to allocate more resources to decision-support processing. The possible range of values is 0 to 100. This range represents the percent of resources that you can allocate to decision-support processing.
At times, you might want to allocate resources to maximize the throughput for individual OLTP queries rather than for decision-support queries. In this case, set MAX_ PDQPRIORITY to 0, which limits the value of PDQ priority to OFF. A PDQ priority value of OFF does not prevent decision-support queries from running. Instead, it causes the queries to run without parallelization. In this configuration, response times for decision-support queries might be slow.
If applications make little use of queries that require parallel sorts and parallel joins, consider using the LOW setting for PDQ priority.
If the database server is operating in a multiuser environment, you might set MAX_PDQPRIORITY to 1 to increase interquery performance at the cost of some intraquery parallelism. A trade-off exists between these two different types of parallelism because they compete for the same resources. As a compromise, you might set MAX_PDQPRIORITY to some intermediate value (perhaps 20 or 30) and set PDQPRIORITY to LOW. The environment variable sets the default behavior to LOW, but the MAX_PDQPRIORITY configuration parameter allows individual applications to request more resources with the SET PDQPRIORITY statement.
Set PDQPRIORITY and MAX_PDQPRIORITY to 100 if you want the database server to assign as many resources as possible to parallel processing. This setting is appropriate for times when parallel processing does not interfere with OLTP processing.
You can use different numeric settings for PDQPRIORITY to experiment with the effects of parallelism on a single application. For information on how to monitor parallel execution, see Monitoring PDQ Resources.
The database server reduces the PDQ priority of queries that contain outer joins to LOW (if set to a higher value) for the duration of the query. If a subquery or a view contains outer joins, the database server lowers the PDQ priority only of that subquery or view, not of the parent query or of any other subquery.
The database server lowers the PDQ priority of queries that require access to a remote database (same or different database server instance) to LOW if you set it to a higher value. In that case, all local scans are parallel, but all local joins and remote accesses are nonparallel.
The database server freezes the PDQ priority that is used to optimize SQL statements within SPL routines at the time of procedure creation or the last manual recompilation with the UPDATE STATISTICS statement. To change the client value of PDQPRIORITY, embed the SET PDQPRIORITY statement within the body of your SPL routine.
The PDQ priority value that the database server uses to optimize or reoptimize an SQL statement is the value that was set by a SET PDQPRIORITY statement, which must have been executed within the same procedure. If no such statement has been executed, the value that was in effect when the procedure was last compiled or created is used.
The PDQ priority value currently in effect outside a procedure is ignored within a procedure when it is executing.
It is suggested that you turn PDQ priority off when you enter a procedure and then turn it on again for specific statements. You can avoid tying up large amounts of memory for the procedure, and you can make sure that the crucial parts of the procedure use the appropriate PDQ priority, as the following example illustrates:
CREATE PROCEDURE my_proc (a INT, b INT, c INT) Returning INT, INT, INT; SET PDQPRIORITY 0; ... SET PDQPRIORITY 85; SELECT ... (big complicated SELECT statement) SET PDQPRIORITY 0; ... ;Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]