ODBC Server Installation and Management Manual

Managing NonStop ODBC Server Resources
HP NonStop ODBC Server Installation and Management Manual—429395-002
5-16
Governing
You can use multiple cost ranges (ESTIMATED_COST values in the preceding
example) to categorize queries into different cost groups—such as small, medium, and
large queries—and specify an action to take effect for each cost range.
When a statement is prepared, the NonStop ODBC Server determines what action to
take based on the estimated cost returned by the SQL/MP optimizer and then performs
it before the statement is executed.
When multiple cost ranges are defined, the action to take is associated with the lower
bound of the cost. To illustrate this, suppose the same governing policy settings apply
as used in the previous example:
GOV_NAME GOV_ATTRIBUTE LIMIT_VALUE GOV_ACTION QST_LOG_ON
Policy-A estimated_cost 500 priority --10 N
Policy-A estimated_cost 3000 priority --30 Y
Policy-A estimated_cost 5000 stop N
Suppose the current priority is 150, that a statement such as
SELECT * FROM T
has an estimated cost of 1000, and a statement such as
SELECT * FROM S WHERE ...
(a more complex statement) has an estimated cost of 8000.
In this scenario, SELECT * FROM T runs at priority 140. Because its cost is 1000,
which is between 500 and 3000, the action to take is defined by the limit value of 500,
which is to lower the current priority (150) by 10. Because the cost of SELECT * FROM
S WHERE … is greater than 5000, the action specified is to not execute the statement
at all.
Governing
At run time, the resource governing facility controls resource use by either stopping
query execution or by changing the priority of a query. You as system administrator can
limit the time (CPU time or elapsed time) permitted for a statement or limit the I/O
consumed for a statement.
Resource governing can be defined by specifying the governing attributes to be limited
and the amount of resource to limit in the governing policy table ZNSGOV. Multiple
limits and actions can be defined, but only one action is triggered. When the first limit is
exceeded, the governing action associated with the first attribute is triggered (it is an
OR condition, not an AND condition). A warning message is returned to the client
stating that a particular governing action is in effect. If the action is to change the
priority of a query, an additional EMS message is logged to $0.
The following example illustrates how the resource policies are enforced. Suppose the
initial priority is 150.
User profile:
PROFILE_NAME GOV_MODE_ON GOV_NAME QST_LOG_ON
coreprof Y Policy-A N