ERR-0109D
Insufficient memory
Version
All versions
Explanation
Unable to execute query.
This error occurs when the execution of a certain query fails due to insufficient temporary memory.
# Example
create table t (c1 integer , c2 integer ); create table t2 (c1 integer , c2 integer ); create table t3 (c1 integer , c2 integer ); create index t_idx_01 on t(c1, c2); create index t2_idx_01 on t2(c1, c2); create index t3_idx_01 on t3(c1, c2); insert into t select level , level from dual connect by level < 301; insert into t2 select level , level from dual connect by level < 301; insert into t3 select level , level from dual connect by level < 301; alter system set EXECUTE_STMT_MEMORY_MAXIMUM = 1048576; |
# 5.5.1 or above
iSQL> select count (*) from ( select count (*) from t, t2, t3 group by t.c2, t2.c2, t3.c2); |
iSQL> select count (*) from ( select count (*) from t, t2, t3 group by t.c2, t2.c2, t3.c2); |
Cause
The following error description can be viewed with the AltiErr utility:
$ altierr 0x0109D 0x0109D ( 4253) idERR_ABORT_InsufficientMemory Insufficient memory # *Cause: Insufficient memory # *Action: Please make sure that the system has enough available memory. |
The temporary area is used when executing a query including a GROUP BY or ORDER BY clause for memory. However, this temporary area also uses memory.
This error occurs due to EXECUTE_STMT_MEMORY_MAXIMUM while allocating memory to execute a query.
Action
Altibase uses memory space for temporary memory tables, instead of disk space.
Consequently, the necessary memory space must be secured at query execution with the following property:
EXECUTE_STMT_MEMORY_MAXIMUM ( default value: 1G) |
The unit is bytes and an error occurs if more memory than the value specified for this property is used during the execution stage of query execution.
This property is set as the maximum value to prevent unnecessary memory increase.
How to Fix the Error
1. Check the increase in Query_Execute.
iSQL> SELECT * FROM v$memstat WHERE name = 'Query_Execute' ; NAME ALLOC_SIZE ALLOC_COUNT MAX_TOTAL_SIZE ---------------------------------------------------------------------------------------------------- Query_Execute 5091105640 75431 5091105640 |
2. Check the current value of EXECUTE_STMT_MEMORY_MAXIMUM
iSQL> set vertical on ; iSQL> select name , value1 from v$property where name = 'EXECUTE_STMT_MEMORY_MAXIMUM' ; NAME : EXECUTE_STMT_MEMORY_MAXIMUM VALUE1 : 1073741824 |
3. Using the ALTER statement, set a value (in bytes) larger than ALLOC_SIZE for EXECUTE_STMT_MEMORY_MAXIMUM.
Set an appropriate value by estimating how much the execution space will extend for the query.
iSQL> ALTER SYSTEM SET EXECUTE_STMT_MEMORY_MAXIMUM=5368709120; (5G) Alter success. |
4. To apply the change permanently, the value has to be changed in the altibase.properties file as well($ALTIBASE_HOME/conf/altibase.properties).
Reference
The EXECUTE_STMT_MEMORY_MAXIMUM property specifies the maximum memory that a single statement can use for execution, not the pre-allocation memory that a single statement can use.
Memory usage can increase as much as specified by the property. Therefore, the user is recommended to set an appropriate value.
Copyright ⓒ 2000~2016 Altibase Corporation. All Rights Reserved.
These documents are for informational purposes only. These information contained herein is not warranted to be error-free and is subject to change without notice. Decisions pertaining to Altibase's product characteristics, features and development roadmap are at the sole discretion of Altibase. Altibase may own related patents, trademarks, copyright or other intellectual property rights of products and/or features discussed in this document.