Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

 

Code Block
titleSum of maximum disk temporary tablespaces
languagesql
SELECT 'DISK_TEMP_TBS_MAX_SUM'
     , SUM(DECODE(F.MAXSIZE, 0, F.CURRSIZE, F.MAXSIZE)*TBS.PAGE_SIZE) AS 'MAX_SIZE(BYTE)'  
  FROM V$DATAFILES F,
       V$TABLESPACES TBS
 WHERE F.SPACEID = TBS.ID
   AND TBS.TYPE IN (5, 6);

 

 

 

 

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.

 

Panel

TEMP_MAX_PAGE_COUNT = Sum of maximum disk temporary tablespaces / 8192

 

 

 

Tip

Example of TEMP_MAX_PAGE_COUNT calculation

If the total sum of the maximum disk temporary tablespaces is 17179869184 bytes (16GB), 17179869184/8192 = 2097152, and the value of the TEMP_MAX_PAGE_COUNT property is 2097152. 

TEMP_MAX_PAGE_COUNT setting value for each total maximum of disk temporary tablespaces

TEMP_MAX_PAGE_COUNT = 1048576 for 8G

TEMP_MAX_PAGE_COUNT = 2096128 for 16G

TEMP_MAX_PAGE_COUNT = 4192256 for 32G

TEMP_MAX_PAGE_COUNT = 8388608 for 64G

 

 

 

 

3. Change TEMP_MAX_PAGE_COUNT property

TEMP_MAX_PAGE_COUNT can be changed at the system level during Altibase operation.

 

Code Block
languagesql
ALTER SYSTEM SET TEMP_MAX_PAGE_COUNT = value; 

 

Check V$PROPERTY to check the changed value.

 

Code Block
languagesql
SELECT NAME, VALUE1 FROM V$PROPERTY WHERE NAME = 'TEMP_MAX_PAGE_COUNT';

 

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

 

Code Block
themeDJango
languagebash
shell> cd $ALTIBASE_HOME/conf
shell> vi altibase.properties        # If TEMP_MAX_PAGE_COUNT does not exist in the altibase.properties file, if it is added, the existing value is changed. 
TEMP_MAX_PAGE_COUNT = value;         

 

 

 

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.

 

Code Block
languagesql
ALTER SYSTEM SET TOTAL_WA_SIZE = value; 
ALTER SYSTEM SET SORT_AREA_SIZE = value; 
ALTER SYSTEM SET HASH_AREA_SIZE = value; 

 

Check V$PROPERTY to check the changed value.

 

Code Block
languagesql
SELECT NAME, VALUE1 FROM V$PROPERTY WHERE NAME IN ('TOTAL_WA_SIZE', 'SORT_AREA_SIZE', 'HASH_AREA_SIZE');

 

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

 

Code Block
shell> cd $ALTIBASE_HOME/conf
shell> vi altibase.properties        # Find and change each property in the altibase.properties file. 
TOTAL_WA_SIZE                        = 134217728 # default = 134217728
SORT_AREA_SIZE                       = 1048576   # default = 1048576 
HASH_AREA_SIZE                       = 4194304   # default = 4194304        


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.

Code Block
titleAltibase 7 or later
languagesql
SELECT NAME, ALLOC_SIZE, MAX_TOTAL_SIZE FROM V$MEMSTAT WHERE NAME = 'Temp_Memory';
Code Block
titleAltibase 6.3.1, 6.5.1
languagesql
SELECT NAME, ALLOC_SIZE, MAX_TOTAL_SIZE FROM V$MEMSTAT WHERE NAME = 'Storage_Disk_Buffer';

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.

Code Block
titleLinux, SunOS
themeDJango
languagebash
shell> ps -o vsz,rss -p process_id 
Code Block
titleHP-UX
themeDJango
languagebash
shell> export UNIX95=1
shell> ps -o vsz,rss -p process_id 
Code Block
titleAIX
languagebash
firstlineDJango
shell> ps -o vsz,rssize -p process_id