Adaptive Server version 15.0.2 uses more procedure cache for several reasons, including:
The query processing engine now looks for additional ways to optimize the query.
The execution engine avoids materialization of worktables and evaluates aggregations in memory as much as possible.
The data change counters maintained for the datachange() function allocate memory from procedure cache. The partition condition tree is cached in the procedure cache. Partition boundary values are allocated in the procedure cache, resulting in a required increase in procedure cache resources.
A configuration parameter, max repartition degree, has been added. This parameter controls the maximum degree to which an intermediate data stream can be repartitioned. The default value of this parameter is 1, which indicates that repartitioning is not set and is bound by the number of online engines configured for Adaptive Server Enterprise. For a query with a large number of tables, Adaptive Server Enterprise version 15.0.2 can put an increased demand on auxiliary scan descriptors.
Set the value of max repartition degree to a value lower than the number of engines to decrease resource usage. You may also need to configure a larger value for the auxiliary scan descriptor pool.
Adaptive Server Enterprise version 15.0.2 avoids worktable materialization and incurs more resources in auxiliary scan descriptors.
During the upgrade process, max memory is increased by Adaptive Server Enterprise if the new total logical memory is greater than max memory. The new value of max memory is set to the new value of total logical memory.
During the upgrade process, number of open partitions is set to the same value as number of open indexes, resulting in increased memory usage. An open partition requires approximately 950 bytes.
