Prioritizing Short Queries at Run Time

Scenario

You recently created a resource pool for users who are not experienced with SQL and who frequently run ad hoc reports. You have managed resource allocation by creating a standalone resource pool that will prevent these queries for borrowing resources from the GENERAL pool, but now you want to manage resources at run time and ensure that short queries always run with a high priority and are never queued as a result of limited run-time resources.

Solution

Set the RUNTIMEPRIORITY for the resource pool to MEDIUM or LOW. Set the RUNTIMEPRIORITYTHRESHOLD for the resource pool to the duration of queries you want to ensure always run at a high priority. For instance, if you set this value to 5, all queries that complete within 5 seconds will run at high priority. Any other query that exceeds 5 seconds will drop down to the RUNTIMEPRIORITY assigned to the resource pool (MEDIUM or LOW).

Example

To ensure that all queries with a duration of less than 5 seconds always run at a high priority, modify adhoc_pool as follows:

=> ALTER RESOURCE POOL ad_hoc_pool RUNTIMEPRIORITY medium RUNTIMEPRIORITYTHRESHOLD 5;