Overview

This document describes the cause and solution of the occurrence of 'ERR-11183 (70019) Insufficient page descriptor area in the temp table' that occurs when executing a query.

Version

• Altibase Server 6.3.1 or later
• Altibase server up to 7.1.0.5.0
  • Altibase 7.1.0.5.1 or later reflects BUG-48369, so this error does not occur.

Symptom

This can occur when a disk table is used in a query statement and a query statement that requires SORT or HASH operation processing is executed. 

Cause

This error occurs when the following conditions are satisfied.

• The maximum size of the disk temporary tablespace is set larger than TEMP_MAX_PAGE_COUNT, and
• When a query statement that requires a disk temporary tablespace with a size exceeding TEMP_MAX_PAGE_COUNT is executed.

Altibase server allocates and uses a certain size in memory for fast operation when SORT/HASH operation is required in the process of query processing for disk tables. If all the memory of the specified size is used and additional space for SORT/HASH operations is needed, use a disk temporary tablespace. At this time, the total number of pages that can be used as a disk temporary tablespace is determined by the TEMP_MAX_PAGE_COUNT property.

Example of error occurrence: Insufficient page descriptor area in the temp table

The maximum size of the disk temporary tablespace is set to 2G, and the value of the TEMP_MAX_PAGE_COUNT property is set to 32767 (256MB),

When a query statement whose size for SORT operation exceeds the TEMP_MAX_PAGE_COUNT property value of 32767 (256MB) is executed.

If the disk temporary tablespace usage is queried while executing the query, it can be seen that the disk temporary tablespace usage is close to the TEMP_MAX_PAGE_COUNT property value.

Solution

Change the value of the TEMP_MAX_PAGE_COUNT property according to the maximum value of the disk temporary tablespace.

1. Check the maximum value of disk temp tablespace
TEMP_MAX_PAGE_COUNT should be set on the assumption that all disk temporary tablespaces will be used to the maximum. Therefore, check the sum of the maximum values of all disk temporary tablespaces created in the Altibase server with the following query.

2. Calculate TEMP_MAX_PAGE property appropriate value
The unit of the TEMP_MAX_PAGE_COUNT property is the number of pages, and the value is calculated using the formula below.

3. Change TEMP_MAX_PAGE_COUNT property

Check V$PROPERTY to check the changed value.

Altibase server configuration file is also changed so that the value changed to ALTER SYSTEM is reflected even after restarting the Altibase server.

4. Considerations when changing the TEMP_MAX_PAGE_COUNT property
The following three properties are affected by TEMP_MAX_PAGE_COUNT.
TOTAL_WA_SIZE
SORT_AREA_SIZE
HASH_AREA_SIZE

So, if TEMP_MAX_PAGE_COUNT is changed, these properties must be changed as well.

The recommended values for each property are as follows. The recommended value is calculated according to the default ratio, and the appropriate value of the property may be changed during operation.

TOTAL_WA_SIZE: 256 times TEMP_MAX_PAGE_COUNT
SORT_AREA_SIZE: 2 times TEMP_MAX_PAGE_COUNT
HASH_AREA_SIZE: 8 times TEMP_MAX_PAGE_COUNT

For example, when changing to TEMP_MAX_PAGE_COUNT = 1048576, the recommended value of each property is as follows.

TOTAL_WA_SIZE = 1048576*256 = 268435456 (Unit is bytes)
SORT_AREA_SIZE = 1048576*2 = 2097152 (unit is byte)
HASH_AREA_SIZE = 1048576*8 = 8388608 (Unit is byte)

Like TEMP_MAX_PAGE_COUNT, the following three properties can be changed at the system level during Altibase operation.

Check V$PROPERTY to check the changed value.

The altibase server configuration file is also changed so that the value changed to ALTER SYSTEM is reflected even after restarting the Altibase server.

5. Effect of property change

In the case of the TOTAL_WA_SIZE property, the ALLOC_SIZE and MAX_TOTAL_SIZE of V$MEMSTAT increase immediately after setting, and the memory of the Altibase server process also increases.

The area where the memory increases depend on the Altibase version. In the case of Altibase 7, the Temp_Memory area increases, and in the case of Altibase 6.3.1 and 6.5.1, the Storage_Disk_Buffer area increases.

 How to check V$MEMSTAT is as follows.

In the case of Unix/Linux, check whether the memory of the Altibase server process is increased with the ps command.

When checking immediately after TOTAL_WA_SIZE is changed, vsz increases, and when sort/hash operation occurs, rss increases.