Restricting Resource Usage of Ad Hoc Query Application

Scenario

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

Solution

The simplest solution is to create a standalone resource pool for the ad hoc applications so that the total MEMORYSIZE is fixed. Recall that in a standalone pool, MAXMEMORYSIZE is set equal to MEMORYSIZE so no memory can be borrowed from the GENERAL pool. Associate this user pool with database users from which the application uses to connect to the database. Also set RUNTIMECAP to limit the maximum duration of an ad hoc query.

Other solutions include limiting the memory usage of individual users such as in the Preventing Runaway Queries.

Example

To create a standalone resource pool for the ad hoc users, set the MEMORYSIZE equal to the MAXMEMORYSIZE:

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

After the pool has been created, associate the ad hoc users with the adhoc_pool:

=> ALTER USER app1_user RESOURCE POOL adhoc_pool;
=> ALTER RESOURCE POOL adhoc_pool MEMORYSIZE '10M' MAXMEMORYSIZE '10M';
\i vmart_query_04.sql
vsql:vmart_query_04.sql:12: ERROR: Insufficient resources to initiate plan 
on pool adhoc_pool [Request Too Large:Memory(KB) 
Exceeded: Requested = 84528, Free = 10240 (Limit = 10240, Used = 0)]

The query will not borrow memory from the GENERAL pool and gets rejected with a 'Request Too Large' message.