6.3.8 Determining the memory requirement for executing the adbgetcst command
When executing the adbgetcst command, the HADB server uses the types of memory listed below. Determine the requirement for each type of memory.
- ■ Shared memory
-
-
Process common memory (PROC_GETCOSTSZ)
-
Real thread private memory (RTHD_GETCOSTSZ)
-
The following subsections describe the formulas for determining the required amounts of these types of memory.
- Organization of this subsection
(1) Determining the process common memory requirement (for executing the adbgetcst command)
Use the following formula to determine the process common memory (PROC_GETCOSTSZ) required for executing the adbgetcst command.
- Formula (kilobytes)
-
PROC_GETCOSTSZ = Σ(PROC_MNG + PROC_CST)#
- Explanation of variables
-
-
PROC_MNG
Process common memory to be used for managing the collection of cost information
Determine the value as explained in (a) Determining the variable PROC_MNG.
-
PROC_CST
Process common memory to be used as a buffer when collecting cost information
Determine the value as explained in (b) Determining the variable PROC_CST.
-
- #
-
If you execute multiple adbgetcst commands concurrently, determine the memory requirement for each adbgetcst command. Then, add up the total memory requirements.
(a) Determining the variable PROC_MNG
The variable PROC_MNG is used to determine the amount of process common memory to be used for managing the collection of cost information. Use the following formula to determine its value.
Formula (kilobytes)
|
|
![[Figure]](GRAPHICS/F0000431.GIF)
- #
-
Add this value when the audit trail facility is enabled.
Explanation of variables
- DIC
-
Memory for acquiring the definition information of the table to be processed
Determine the value by referring to the explanation of the variable DIC in (a) Determining the variable PROC_IMPT in (1) Determining the process common memory requirement (for executing the adbimport command) in 6.3.6 Determining the memory requirement for executing the adbimport command.
- IOA
-
Memory to be used for database access
- Formula (kilobytes)
-
![[Figure]](GRAPHICS/F0000432.GIF)
- TBL
-
Memory to be used for managing the tables to be processed
- Formula (kilobytes)
-
![[Figure]](GRAPHICS/F0000433.GIF)
-
tbl_num
Number of tables to be processed
-
- DBSTA
-
Memory to be used for DB status analysis
- Formula (kilobytes)
-
![[Figure]](GRAPHICS/F0000434.GIF)
- PROC_AUDINFSZ
-
Determine the value of the variable PROC_AUDINFSZ according to (r) Determining the variable AUDINF in (3) Determining the process common memory requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.
(b) Determining the variable PROC_CST
The variable PROC_CST is used to determine the amount of process common memory to be used as a buffer when collecting cost information.
If the adbgetcst command is executed with the -t option omitted, determine the variable PROC_CST for all tables in the schema of the authorization identifier specified in the -u option. Then use the largest such value as the value of the variable PROC_CST.
Use the following formula to determine its value.
- Formula (kilobytes)
-
PROC_CST = DBHTBLINF + DICTBLINF
Explanation of variables
- DBHTBLINF
-
Memory to be used for collecting the cost information of a table to be processed
Formula (kilobytes)
![[Figure]](GRAPHICS/F0000435.GIF)
- col_num
-
Number of columns in the table to be processed
- sql_rthd_num
-
Use the following formula to determine the value:
value-specified-for-cost-information-collection-option-adb_getcst_rthd_num - 1
- coldata(i)
-
Data length of each column
Determine the value according to Table 6‒9: Data length of each data type in (c) Determining the variable RTHD_EXESQLSZ under (2) Determining the real thread private memory requirement (during normal operation) in 6.3.4 Determining the memory requirement during normal operation.
- hash_size
-
Substitute 65,536.
- cardinality_num
-
Substitute 20,000.
- DICTBLINF
-
Memory used for storing the cost information of the table to be processed in the system table
Formula (kilobytes)
![[Figure]](GRAPHICS/F0000436.GIF)
- col_num
-
Number of columns in the table to be processed
- b_tree_idx_num
-
Number of B-tree indexes defined for the table to be processed
- text_idx_num
-
Number of text indexes defined for the table to be processed
- row_size
-
Row size in the table to be processed
Formula
![[Figure]](GRAPHICS/F0000437.GIF)
-
coldata(i): Data length of each column
Determine the value according to Table 6‒9: Data length of each data type in (c) Determining the variable RTHD_EXESQLSZ under (2) Determining the real thread private memory requirement (during normal operation) in 6.3.4 Determining the memory requirement during normal operation.
-
(2) Determining the real thread private memory requirement (for executing the adbgetcst command)
Use the following formula to determine the amount of real thread private memory (RTHD_GETCOSTSZ) required for executing the adbgetcst command.
- Formula (kilobytes)
-
RTHD_GETCOSTSZ = COSTSQL
- Explanation of variables
-
- COSTSQL
-
Memory to be used by a retrieval SQL statement executed when collecting cost information
When the adbgetcst command is executed, the following SQL statement is executed in order to collect cost information from the table to be processed:
SELECT COUNT(*) FROM "target-table-name" SELECT MIN("column-name-1"),MAX("column-name-1") FROM "target-table-name" : SELECT MIN("column-name-n"),MAX("column-name-n") FROM "target-table-name" SELECT * FROM "target-table-name"Note: column-name-1 to column-name-n are the names of the columns defined for the target table.
Determine for each processing-target table the amount of memory required to execute a retrieval SQL statement by referring to (c) Determining the variable RTHD_EXESQLSZ and (g) Determining the variable RTHD_EXESQLDICSZ in (2) Determining the real thread private memory requirement (during normal operation) under 6.3.4 Determining the memory requirement during normal operation. Then substitute the largest among the determined values.