DB2 parameter sortheap sheapthres

Sortheap and sheapthres sortheap are a database configuration parameter that defines the maximum number of private memory pages used by private or shared sorting. Each sorting has an independent sorting heap, which is allocated by the database manager as needed. Generally, we can understand that when the memory size required for a sorting task exceeds sortheap, the sorting overflow occurs, if the statistical information is out of date, or the data is biased, and published statistics are not collected, once DB2 requests a too small heap, however, if the actual sorting operation exceeds the requested amount, it also overflows. Therefore, it is important to keep the statistics up-to-date. In addition, it should be ensured that sorting is not the result of a lost index. If the sorting is private, this parameter will affect the agent's private memory. If the sorting is shared sorting, this parameter will affect the database's shared memory. Each sort has a separate sort heap allocated by the database manager as needed. Sort data in the sorting heap. If the optimizer is used to guide the allocation of the sorting heap size, the size of the sorting heap allocated by the optimizer is smaller than the size specified by this parameter. Sheapthres is also a Database Manager configuration parameter. Private sorting and shared sorting use different memory sources. The size of the shared sorting memory zone is pre-determined in a static manner based on the sheapthres value when the database is connected for the first time. The size of the private sorting memory area is unrestricted. For private sorting and shared sorting, sheapthres parameters are applied in different ways: for private sorting, sheapthres is an instance-level "soft" limit on all memory that can be consumed by private sorting at any given time. When the total consumption of private sorting memory of the Instance reaches this limit, the memory allocated for other private sorting requests will be greatly reduced. For shared sorting, sheapthres is a database-level "hard" limit on all memory that can be consumed by shared sorting at any given time. When this limit is reached, other shared sorting memory requests are not allowed until the total shared memory consumption falls below the limit specified by sheapthres. Examples of operations using sorting heap include hash connections and operations for tables in the memory. Explicit sheapthres definition prevents the database manager from using too much memory for large sorting. 1. we recommend that you use the database system monitor to track sorting activities; Use appropriate indexes to minimize the use of sorting heaps; Increase the sortheap value when large sorting is required frequently; if you increase sortheap, check whether the sheapthres parameter in the Database Manager configuration file needs to be adjusted. The optimizer uses the sorting heap size to determine the access path. After changing this parameter, consider re-binding the application (using the rebind package command); ideally, you should sort the heap Threshold Parameter (sheapthres) reasonably set to a multiple of the maximum sortheap value set in the Database Manager instance. This parameter must be at least twice the maximum sortheap defined by any database in the instance. 2. to change the values of sortheap and sheapthres, run the following command: -- sortheap is a database-level parameter -- DB2-V update dB CFG for db_name using sortheap a_value -- sheapthres is an instance-level parameter -- DB2-V update dBm CFG using sheapthres B _value DB2-V terminate 3. optimization steps for OLTP applications should not perform large sorting. Large sorting consumes too much CPU and I/O resources. Generally, the default sortheap size (256 4 kb pages) is enough. In fact, for highly concurrent OLTP, you may want to lower this default value. When you need further research, you can issue the following command: DB2-V update monitor switches using sort on, and then let your application run for a while, and then run: DB2-V get snapshot for database on sample view the following output: Total sort heap allocated = 0 total sorts = 1 Total sort time (MS) = 0 sort overflows = 0 active sorts = 0 commit statements attempted = 1 rollback statements attempted = 0 dynamic statements attempted = 4 static statements attempted = 1 binds/precompiles ATT Empted = 0 based on the output, you can calculate the number of sorting tasks for each transaction and calculate the percentage of sorting that overflows the memory that can be used for sorting. Sortspertransaction = (total sorts)/(commit statements attempted + rollback statements attempted) percentsortoverflow = (sort overflows * 100)/(total sorts) Experience: If sortspertransaction is greater than 5, it may indicate that each transaction is too sorted. If percentsortoverflow is greater than 3%, serious and unexpected large sorting may occur. In this case, adding sortheap only hides the performance problem, but cannot fix it. The correct solution to this problem is to improve the access solution for problematic SQL statements by adding the correct index. For OLTP, it is best to set it to 128 at the beginning; for OLAP, it is set to between 4096 and 8192. If there are many "Sort overflows" (two digits), you may need to add sortheap. If the value of "Number of hash join overflows" is not 0, the sortheap is increased successively by 256 until it is 0. If the value of "number of small hash join overflows" is not 0, the sortheap is increased in proportion to 10% until the number of overflow connections in the hash is 0.