Restricting resource usage of ad hoc query application

You recently made your data warehouse available to a large group of users who are inexperienced with SQL.

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;