Restricting Resource Usage of Ad Hoc Query Application

Scenario

You recently made your data warehouse available to a large group of users who are inexperienced with SQL. Some users run reports that operate on a large number of rows and overwhelm the system. You want to throttle system usage by these users.

Solution

1. Create a resource pool for ad hoc applications where MAXMEMORYSIZE is equal to MEMORYSIZE. This prevents queries in that resource pool from borrowing resources from the GENERAL pool. Also, set RUNTIMECAP to limit the maximum duration of ad hoc queries:

=> CREATE RESOURCE POOL adhoc_pool 
	MEMORYSIZE '200M' 
       MAXMEMORYSIZE '200M' 
	RUNTIMECAP '20 seconds'
	PRIORITY 0 
	QUEUETIMEOUT 300 
	PLANNEDCONCURRENCY 4;
=> SELECT pool_name, memory_size_kb, queueing_threshold_kb 
	FROM V_MONITOR.RESOURCE_POOL_STATUS WHERE pool_name='adhoc_pool';
 pool_name  | memory_size_kb | queueing_threshold_kb 
------------+----------------+-----------------------
 adhoc_pool |         204800 |                153600
(1 row)
				

2. Associate this resource pool with database users who use the application to connect to the database.

   => ALTER USER app1_user RESOURCE POOL adhoc_pool;