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:
- Set the RUNTIMEPRIORITY to MEDIUM
- Set the RUNTIMETHRESHOLD to 5
=> ALTER RESOURCE POOL ad_hoc_pool RUNTIMEPRIORITY medium RUNTIMEPRIORITYTHRESHOLD 5;