ERROR: Insufficient resources to execute plan on pool...

[Josh]

Hi,

I keep getting errors when I try and execute a query. For instance: ERROR: Insufficient resources to execute plan on pool general [Request exceeds session memory cap: 2537432KB > 972800KB].

What does this error mean and how do I get rid of it?

Thanks

[JimKnicely]

This error indicates that the user executing the query has a memory cap setting that is insufficient for this query. The query will need about 2.5GB and the user has a max memory cap of 1GB.

You can check the user's memory cap (MEMORYCAP) setting by issuing the SHOW ALL command in vSQL.

Code: Select all

dbadmin=> show all;
            name             |                      setting
-----------------------------+---------------------------------------------------
 locale                      | en_US@collation=binary (LEN_KBINARY)
 standard_conforming_strings | on
 escape_string_warning       | on
 datestyle                   | ISO, MDY
 intervalstyle               | plain
 timezone                    | US/Eastern
 search_path                 | "$user", public, v_catalog, v_monitor, v_internal
 transaction_isolation       | READ COMMITTED
 transaction_read_only       | false
 resource_pool               | general
 memorycap                   | UNLIMITED
 tempspacecap                | UNLIMITED
 runtimecap                  | UNLIMITED
 enabled roles               | dbadmin, pseudosuperuser
 available roles             | dbadmin, pseudosuperuser

In my case I have unlimited resources. The DBA can change the resource cap for your user with the ALTER USER command.

Example:

Code: Select all

ALTER USER xxx MEMORYCAP '2.5G';

[JimKnicely]

Update on my previous post... it seems that you have to use integer values when defining the MEMORYCAP. The 2.5G I specified won't work:

Code: Select all

dbadmin=> ALTER USER mark MEMORYCAP '2.5G';
ROLLBACK:  '2.5G' is not a valid size description
dbadmin=> ALTER USER mark MEMORYCAP '2.6G';
ROLLBACK:  '2.6G' is not a valid size description
dbadmin=> ALTER USER mark MEMORYCAP '2G';
ALTER USER
dbadmin=> ALTER USER mark MEMORYCAP '3G';
ALTER USER

[DataWhisperer]

This can also be caused from having too many delete vectors in your projections.

Code: Select all

dbadmin=> select node_name, schema_name, projection_name, sum( deleted_row_count ) num_deleted_row_count, sum( used_bytes ) num_used_bytes from delete_vectors group by 1, 2, 3 order by 1, 2, 3;

If the num_deleted_row_count is over 10% of your total table size, then set the ahm and purge your tables/projections. It can take time to purge, so be careful to do it during non-loading times, and lower usage, it will lock the projections/tables.

We purge after every ETL.