Spotlight on SAP ASE 2.12 - Getting Started Guide

SHEAPTHRES database manager configuration parameter

The total amount of memory to be used as the workarea for all private sorts across the instance or for all shared sorts in the database at any one time:

• For private sorts—This parameter is an instance-wide soft limit on the total amount of memory that all private sorts can use at any given time. That is, when the total private sort memory used in the instance reaches this limit, the database manager continues to allocate sort heap to private sorts. However, the size of each sort heap is reduced considerably.
• For shared sorts—This parameter is a database-wide hard limit on the total amount of memory consumed by sorts at any given time. That is, when total sort heap for the database reaches this limit, the database manager no longer allocates sort heap space to sorts until the total shared sort memory falls below the SHEAPTHRES limit.

A private sort is performed by an agent assigned to work for a specific application as a result of a connect request or, within a parallel environment, a new SQL request. When the application requires a sort, the database manager provides the sort heap from the agent’s private memory. (The database manager allocates private memory to the agent when the agent is assigned to work for the application.)

A shared sort is performed by agents working on behalf of an application within a partitioned environment or within an instance in which the INTRA_PARALLEL parameter is turned on. When the application requires a sort, the database manager allocates the sort heap from the database shared memory. The agents then share the sort heap to coordinate the sort.