>>-SET--ENVIRONMENT--TMPSPACE_LIMIT--+-ON----------------------------------+--;-><
+-OFF---------------------------------+
'-'--percent_of_optimizer_estimate--'-'
To manage individual sessions or queries, execute the SQL statement SET ENVIRONMENT and its options to instruct the database server how to manage resource allocation. The SET ENVIRONMENT statement lets you specify the following kinds of resource management for each query run by a session:
If you rely on the active setting of PDQPRIORITY to manage memory allocation for queries, the database server tries to grant a query the maximum amount of memory allowed by the combined factors of PDQPRIORITY, MAX_PDQPRIORITY, and DS_TOTAL_MEMORY. For a carefully tuned SQL query, you might be able to determine an optimal PDQPRIORITY value and set this value before the query runs. However, relying on PDQPRIORITY might result in over-allocation and inefficient use of memory.
To avoid over-allocation of memory and allow the optimizer to estimate the memory requirements of each query based on data-distribution statistics, enable the IMPLICIT_PDQ option of the SET ENVIRONMENT statement. IMPLICIT_PDQ is particularly useful if users execute ad hoc queries that you cannot tune. If a large query runs often, careful tuning usually results in better performance than relying on IMPLICIT_PDQ.
In addition, when the optimizer determines the memory allocation, it also distributes the allocated memory proportionally among the query operators based on their relative memory needs. The result is more efficient use of memory.
The syntax is as follows:
If IMPLICIT_PDQ option is enabled, the database server automatically estimates the memory requirements of each query and distributes the allocated memory proportionally among the query operators. The optimizer uses the following formula to calculate the maximum amount of memory that it can allocate:
maximum memory = MAX_PDQPRIORITY/100 * DS_TOTAL_MEMORY
If IMPLICIT_PDQ is set to a percent, the database server scales its memory estimate by the specified percent. If the optimizer estimates seem low, you can specify an percent greater than 100 to force the optimizer to raise its estimates.
In making these estimates, the server may grant more or less memory than a query needs. If the server grants too little memory, some overflow may occur. Although overflow does impact query performance, the memory estimates should be close enough so that there is no serious impact on performance. If the amount of overflow is excessive, the value of IMPLICIT_PDQ may be increased. While using a value greater than 100 may reduce or eliminate overflow, it may also increase the amount of memory used by queries that do not overflow and do not require the extra memory.
If you set a low percent for this environment option, you might increase the amount of query-operator overflow to TEMP space. If the TMPSPACE_LIMIT environment option is also set and the query cannot acquire as much TEMP space as it needs for overflow, the query fails. For more information, see How the SET ENVIRONMENT Options Interact.
For information about setting the IMPLICIT_PDQ option to the SET ENVIRONMENT statement, refer to IBM Informix: Guide to SQL Syntax.
After careful analysis of query processing behavior, you might want to provide more scan threads for specific queries. To tune the number of scan threads, execute the SET ENVIRONMENT MAXSCAN statement to specify the total number of scan threads started on each coserver.
As a general guideline, for large queries set MAXSCAN to three times the number of CPU virtual processors. For example, if you have four CPU virtual processors, set MAXSCAN to 12.
If SET ENVIRONMENT COMPUTE_QUOTA is enabled, MAXSCAN is always set to 1. To increase the number of scan threads with the MAXSCAN option, first turn off the COMPUTE_QUOTA option. For information about how to specify MAXSCAN, refer to the IBM Informix: Guide to SQL Reference.
To require the optimizer to estimate memory allocation but use the current value of PDQPRIORITY as an upper and optional lower limit, enable the BOUND_IMPL_PDQ environment option as well as the IMPLICIT_PDQ environment option. You might require the optimizer to allocate memory within bounds set by PDQPRIORITY if you want to ensure that no query is granted more or less than certain percentages of shared memory.
The optimizer estimates the memory allocation for each query according to the following formula:
maximum memory = PDQPRIORITY * MAX_PDQPRIORITY/100 * DS_TOTAL_MEMORY
If PDQPRIORITY is set to a range of values, as described in Specifying the Amount of Memory That a Query Can Request, the optimizer uses the following formulas to calculate the range within which it can allocate memory:
minimum memory = PDQPRIORITY(low) * MAX_PDQPRIORITY/100 * DS_TOTAL_MEMORY maximum memory = PDQPRIORITY(high) * MAX_PDQPRIORITY/100 * DS_TOTAL_MEMORY
If the IMPLICIT_PDQ environment option is not on, the BOUND_IMPL_PDQ environment option is ignored.
The syntax is as follows:
For information about setting the BOUND_IMP_PDQ option to the SET ENVIRONMENT statement, refer to the IBM Informix: Guide to SQL Syntax.
When the IMPLICIT_PDQ environment option is in effect, the following additional information appears in the SET EXPLAIN output:
Implicit PDQ Priority Set Enviroment IMPLICIT_PDQ <value> % Ideal Implicit PDQ Priority <value> % Requested Implicit PDQ Priority <value> % Granted Implicit PDQ Priority <value> % Ideal Query Memory <value> KB/coserver Requested Query Memory <value> KB/coserver Granted Query Memory <value> KB/coserver
The output of onstat -g sql for a specific session contains information about the explicit PDQPRIORITY and schedule level.
Use this information, as described in the following table, to determine how effective the IMPLICIT_PDQ and BOUND_IMPL_PDQ settings are for your workload.
| Output Line | Description |
|---|---|
| Set Environment IMPLICIT_PDQ | The value specified as an argument to SET ENVIRONMENT IMPLICIT_PDQ |
| Ideal Implicit PDQ Priority | The PDQPRIORITY value that the optimizer determines would be ideal for the query |
| Requested Implicit PDQ Priority | The PDQPRIORITY value requested for this query, taking the setting of IMPLICIT_PDQ into account |
| Granted Implicit PDQ Priority | The granted PDQPRIORITY value |
| Ideal Query Memory | The number of kilobytes of memory that the optimizer determines would be ideal for the query |
| Requested Query Memory | The number of kilobytes of memory requested, as determined by the setting of IMPLICIT_PDQ |
| Granted Query Memory | The number of kilobytes of memory actually granted for the query |
The requested and granted PDQPRIORITY might differ if BOUND_IMPL_PDQ is on, if IMPLICIT_PDQ is set to a value that scales the memory allocation, or for similar reasons.
In a mixed-query workload, some users execute queries that are too small to require full parallelization, which uses all CPU virtual processors on all coservers but the queries are not appropriate for serial execution. Queries executed serially, such as most OLTP queries, are not managed by the Resource Grant Manager (RGM). Serial queries are granted only 128 kilobytes of memory per operator, access data only on the local coserver, and are executed on only that coserver.
For sessions that execute such queries you might enable the COMPUTE_QUOTA option of the SET ENVIRONMENT statement. With compute quotas, the RGM allocates memory appropriately for queries, but each query operator is executed on only one CPU virtual processor on each coserver. Compute quotas are most useful for database servers made up of a small number of coservers, each of which has many CPUs.
The syntax is as follows:
For information about the SET ENVIRONMENT statement and its options, see the IIBM Informix: Guide to SQL Syntax.
If users of your database server can execute ad hoc queries, one or two large queries might use all of the available temporary space for query-operator overflow, thus preventing other queries from completing execution. To avoid such problems, you can specify the amount of temporary dbspace that a single query can use for overflow on each coserver.
Temporary space quotas do not affect creation of SCRATCH or TEMP temporary tables or temporary tables that the database server uses when it unnests a query. Temporary space quotas affect only memory-intensive query operators that sometimes overflow to disk, such as hash join, group, and sort operations.
Temporary space limits are controlled by the following two settings.
This configuration parameter specifies the number of kilobytes of temporary space that is permitted for each query on each coserver.
You might set this configuration parameter to a percent of the available temporary space on each coserver.
Temporary space limits are not in effect unless you enable TMPSPACE_LIMIT option of the SQL statement SET ENVIRONMENT for each session that you want to control. For information about setting this environment option when a session connects to a database, see Specifying the Initial Environment for Sessions.
The DS_TOTAL_TMPSPACE parameter specifies the number of kilobytes of temporary dbspace available on each coserver for operator overflow of individual queries. The DS_TOTAL_TMPSPACE value is ignored if the TMPSPACE_LIMIT environment option is not set. If DS_TOTAL_TMPSPACE is not specified, temporary dbspace quotas are not enabled.
The simplest way to control the amount of temporary space used for query-operator overflow is to specify a value for DS_TOTAL_TMPSPACE that is much less than the actual amount of configured temporary space and set the TMPSPACE_LIMIT environment option to ON for all sessions.
To tune session use of temporary space, you can change the value of DS_TOTAL_TMPSPACE dynamically. For information, see Changing Global Resource Limits Temporarily
If DS_TOTAL_TMPSPACE is set, setting TMPSPACE_LIMIT to 100 percent does not mean that a query can use all temporary space on a coserver. A query is limited to using the amount of temporary space specified by DS_TOTAL_TMPSPACE.
By default, the TMPSPACE_LIMIT environment option is OFF and temporary space quotas are not enforced. To specify temporary dbspace limits for a session, set the TMPSPACE_LIMIT environment option. The syntax is as follows:
The onstat -g rgm command displays information about the setting of DS_TOTAL_TMPSPACE. If the SET ENVIRONMENT TMPSPACE_LIMIT command is in effect for a session, information about the temporary space quota assigned to the session also appears in the onstat -g rgm output.
SET EXPLAIN output includes information about the number of kilobytes as well as the number of pages written as overflow to temporary space. For information about interpreting this output, see Analyze Complex Query Plans.
Before you set any environment options for sessions, consider the ways in which they are interconnected and decide what methods are appropriate to manage your database server. Your performance goals and the variety of queries run against your databases should determine your decision.
Consider the following examples:
However, if you set IMPLICIT_PDQ to 50, the RGM grants only 50 percent of its estimated memory requirements. As a result, queries might overflow to temporary disk space and run more slowly. If you also set TMPSPACE_LIMIT to a low value, some queries might fail because they do not have enough memory for execution or enough disk space for overflow.
In addition, if you turn on BOUND_IMPL_PDQ, the optimizer grants memory within the range specified by the most recent value of PDQPRIORITY.
The following table summarizes the environment options for managing a mixed query workload.
If set to a percent value, the database server allocates only that percent of the estimated memory requirement. In this case, the work of query operators overflows to disk if the work cannot be performed in the allocated memory. In this case, make sure that you allow enough temporary space to accommodate query-operator overflow. Queries fail if they cannot get either the memory or the temporary space that they need.
Although this option restrains the optimizer estimates enabled by IMPLICIT_PDQ, you must still make sure that temporary space is available for possible operator overflow.
This option might make compute-intensive queries run more slowly, but does not affect temporary disk space use.
If memory available to DSS queries is limited, some large queries might overflow to disk. If not enough temporary space is available, the queries will fail.