Home | Previous Page | Next Page   Tuning Database Server Performance > Setting Global Priorities for Queries >

Using the Admission Policy

The setting of the DS_ADM_POLICY configuration parameter determines the admission policy that helps determine query-scheduling order and whether or not to permit starvation. Starvation occurs when a query is delayed indefinitely behind other queries at a higher scheduling level.

The DS_ADM_POLICY configuration parameter can be set to either STRICT or FAIR.

STRICT Policy

With the STRICT policy, the query with the highest scheduling level is the query selected to run. If more than one query is at the same level, the RGM selects the one with the earliest arrival time.

The STRICT policy allows query starvation to occur because a constant stream of high-priority queries can indefinitely delay a query with a lower-priority scheduling level.

FAIR Policy

With the FAIR policy, the RGM defines a fairness value for each waiting query. The fairness value takes into account the following factors:

The query selected to run is the query that has been least fairly treated.

The RGM calculates the fairness value for a query by multiplying the time that a query has waited by its scheduling level. For example, if its requested memory is available, a query with a scheduling level of 20 that has waited sixty seconds runs before a query with a scheduling level of 40 that has waited only twenty seconds. Thus, this policy avoids starvation.

For queries that have the same fairness value, RGM tends to choose queries with lower PDQPRIORITY settings instead of those with higher PDQPRIORITY settings so that more queries can run simultaneously, but the FAIR policy also avoids starvation of higher PDQPRIORITY queries.

Specifying the Admission Policy

The default admission policy is FAIR. With the FAIR policy, the RGM takes into account how long a query has been waiting. The RGM determines the next candidate query by the largest fairness value, as calculated by the following formula:

fairness = (sched_lvl / 100) * wait_time
sched_lvl
is the value that you specify for the query in the SET SCHEDULE LEVEL statement in SQL. The default value is 50.
wait_time
is the number of seconds that the query has been in the wait queue.

Figure 21 shows an excerpt from sample output for onstat -g rgm that shows three queries in the wait queue.

Figure 21. Wait Queue Sample from onstat -g rgm
Resource Grant Manager (RGM)
============================

DS_ADM_POLICY:                      FAIR
DS_MAX_QUERIES:                     10
MAX_PDQPRIORITY:                    100
DS_TOTAL_MEMORY:                    16000 KB
Number of Coservers:                2
DS Total Memory Across Coservers:   32000 KB

...
RGM Wait Queue:   (len = 3)
---------------
Lvl   Session     Plan    PdqPrio   Local Cosvr   Candidate   Wait Time
100    1.16        6       80-100                              1
40     1.18        7       50-70                               10
20     1.12        4       10-20                               20
...

The RGM obtains the following fairness values for these three queries.

Query Plan Fairness Value PDQPRIORITY Range
6 100 / 100 * 1 = 1 80 to 100
7 40 / 100 * 10 = 4 50 to 70
4 20 / 100 * 20 = 4 10 to 20

Queries 7 and 4 have the same fairness values. However, the lower PDQPRIORITY setting of query 4 requests less memory, so query 4 is the query that the RGM chooses to run.

Understanding the Effect of PDQPRIORITY on Query Admission

After the RGM chooses a candidate query, it determines whether it can execute the query.

If the minimum PDQPRIORITY resource request for the query is available, the RGM executes the query after setting its PDQPRIORITY to the largest available percentage within the range that PDQPRIORITY specifies.

If the minimum PDQPRIORITY setting for the query exceeds the available percentage of system resources, the RGM does not execute the query until enough resources are available.

To eliminate reliance on PDQPRIORITY settings, consider executing the SET ENVIRONMENT IMPLICIT_PDQ ON statement. For queries executed when this environment option is in effect, the optimizer determines how much memory the query requires and distributes the memory proportionally among query operators for efficient memory use.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]