Neoview Control Query Default (CQD) Reference Guide (R2.5)

12 Executor
This section describes this CQD: “EXE_MEMORY_LIMIT_PER_CPU” (page 47).
EXE_MEMORY_LIMIT_PER_CPU
Runtime controlsCategory
Specifies the amount of virtual memory (per CPU) that a query can obtain at execution
time.
The total amount of virtual memory of a query consists of those allocated for big memory
operators BMOs (HashJoin, HashGroupBy, Sort, and MergeJoin), as well as those used by
non-BMOs (ProbeCache, PA, Exchange, and PhysicalSequence). BMOs get their memory
through a quota system.
Description
Unit is megabytes of memory.Integer
The default value is ‘1024’ (MB).
Values
For permanent tables created by DBAs the size and growth of tables should be well known.
For such tables being managed and monitored by DBAs there should not be a need to limit
the size of the table, because you don’t want the application to come to a screeching halt
if the table is full and cannot grow anymore. That was the original motivation to use the
MAX TABLE SIZE setting on the CREATE TABLE, as a guideline to allocate the appropriate
contiguous extents on disk to accommodate the planned maximum size the table could
grow to. However, you may still consider using this CQD for even such tables, because
there could be times when a wayward process could spuriously insert data into a table,
which you may want to prevent. In that case this setting would need to be high enough
not to cause file full conditions for legitimate growth of your tables. You may set this limit
at the system level.
The purpose of this CQD is to help govern table creation. Currently there is no quota system
to prevent you from using more space than you are allowed to use on the system. This
CQD helps prevent you from creating large tables on the system and consuming space.
However, it cannot prevent a you from creating many large tables that meet this limit. This
CQD can be set on service levels that are used for end users who have the capability of
creating tables on the system. Different size limits can be set on different service levels for
different groups of users (roles).
This CQD influences only maximum extents for object like tables. Index partition size
settings are based on its base table settings.
Usage
Not applicableProduction usage
Setting this CQD does not allow a table to grow beyond the size specified by this value
unless the table is altered to increase the number of maximum extents for the table. Altering
the maximum extents requires assistance from your HP representative.
When the table reaches this limit, a file full error is returned and the transaction updating
the table is aborted.
Impact
Query (DDL statement) or service level. It could be set at the system level in some cases
for DBA managed tables, as described in Usage.
Level
There is a MAX TABLE SIZE setting on CREATE TABLE. It is used to determine the initial
space allocated for the table, in order to avoid too many non-contiguous (fragmented)
extents being allocated as the table grows. That setting does not limit the maximum size
the table can grow. It allows the table to grow to the size of the disk. This CQD prevents
the table from growing beyond the size specified in the CQD. These settings can be used
together. MAX TABLE SIZE can be used to influence the initial extent allocation to
accommodate the expected size of the table. This CQD can be used to limit the growth of
the table beyond a certain size.
Conflicts/Synergies
Not applicableAddressing the real
problem
EXE_MEMORY_LIMIT_PER_CPU 47