CEO query

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

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.