CEO query

Scenario

The CEO runs a report every Monday at 9AM, and you want to be sure that the report always runs.

Solution

To ensure that a certain query or class of queries always gets resources, you could create a dedicated pool for it as follows:

1. Using the PROFILE command, run the query that the CEO runs every week to determine how much memory should be allocated:

   => PROFILE SELECT DISTINCT s.product_key, p.product_description
   -> FROM store.store_sales_fact s, public.product_dimension p
   -> WHERE s.product_key = p.product_key AND s.product_version = p.product_version
   -> AND s.store_key IN (
   ->  SELECT store_key FROM store.store_dimension
   ->  WHERE store_state = 'MA')
   -> ORDER BY s.product_key;
   

2. At the end of the query, the system returns a notice with resource usage:

   NOTICE:  Statement is being profiled.HINT:  select * from v_monitor.execution_engine_profiles where
   transaction_id=45035996273751349 and statement_id=6;
   NOTICE:  Initiator memory estimate for query: [on pool general: 1723648 KB,
   minimum: 355920 KB]
   

3. Create a resource pool with MEMORYSIZE reported by the above hint to ensure that the CEO query has at least this memory reserved for it:

   => CREATE RESOURCE POOL ceo_pool MEMORYSIZE '1800M' PRIORITY 10;
   CREATE RESOURCE POOL
   => \x
   Expanded display is on.
   => SELECT * FROM resource_pools WHERE name = 'ceo_pool';
   -[ RECORD 1 ]-------+-------------
   name                | ceo_pool
   is_internal         | f
   memorysize          | 1800M
   maxmemorysize       |
   priority            | 10
   queuetimeout        | 300
   plannedconcurrency  | 4
   maxconcurrency      |
   singleinitiator     | f
   

4. Assuming the CEO report user already exists, associate this user with the above resource pool using ALTER USER statement.

   => ALTER USER ceo_user RESOURCE POOL ceo_pool;
   

5. Issue the following command to confirm that the ceo_user is associated with the ceo_pool:

   => SELECT * FROM users WHERE user_name ='ceo_user';
   -[ RECORD 1 ]-+------------------
   user_id       | 45035996273713548
   user_name     | ceo_user
   is_super_user | f
   resource_pool | ceo_pool
   memory_cap_kb | unlimited
   

If the CEO query memory usage is too large, you can ask the Resource Manager to reduce it to fit within a certain budget. See Query budgeting.