7.2.2 Operands related to performance (set format)
- [7] adb_sys_rthd_num = number-of-processing-real-threads
-
~<integer> ((1 to 4,096)) <<40>>
Specify the number of processing real threads.
A processing real thread is a real thread used by the HADB server when SQL statements and the following targeted commands are executed:
- ■ Targeted commands
-
-
adbimport command
-
adbgetcst command
-
adbidxrebuild command
-
adbexport command
-
adbmergechunk command
-
adbarchivechunk command
-
adbunarchivechunk command
-
adbreorgsystemdata command
-
Specify the value determined using the following formula for this operand. The same applies when the client-group facility is used.
- Formula
-
A × B × C
- Explanation of variables
-
A: Number of connections that execute SQL statements concurrently
B: Value of the adb_sql_exe_max_rthd_num operand
C: Maximum number of SELECT statements to be concurrently executed in a single connection
When executing these targeted commands without using the client-group facility, specify a value in this operand that is at least the minimum number of processing real threads required to execute the command. If you do not specify a value at least equal to the minimum number of processing real threads required, execution of the command might result in an error. For details about the minimum number of processing real threads required to execute each command, see Table 6‒28: Operands and command options for specifying the number of processing real threads to be used for command execution in (2) Operands and command options for specifying the number of processing real threads to be used for command execution under 6.23.2 Points to consider about the number of processing real threads to be used during command execution.
If you will be using the client-group facility, set this operand to a value that is greater than the sum of the guaranteed minimum numbers of processing real threads usable by all groups. If the sum of the guaranteed minimum numbers of processing real threads usable by all groups is greater than the value set for this operand, an error will occur when the HADB server starts.
For details about the guaranteed minimum number of processing real threads usable by each group, see the following explanations of the -e option of the adbcltgrp operand in 7.2.12 Operands and options related to the client-group facility (command format):
-
-e guaranteed-minimum-number-of-processing-real-threads-usable-by-the-specified-client-group
-
-e guaranteed-minimum-number-of-processing-real-threads-usable-by-the-specified-command-group
- Important
-
This operand has a major effect on memory requirements. Whenever you change this operand's value, re-estimate the memory requirements. For details about how to estimate the memory requirements, see 6.3 Estimating the HADB server's memory requirement.
- [8] adb_sys_uthd_num = number-of-pseudo-threads-that-can-be-generated-in-a-real-thread
-
~<integer> ((0 to 4,096))<<128>>
Specify the number of pseudo threads that can be generated in a real thread.
As a rule, specify the default value for this operand.
- Important
-
When 0 is specified in this operand, the HADB server will execute SQL statements without using out-of-order execution. The HADB server will use only connection threads to execute SQL statements.
- [9] adb_sys_memory_limit = maximum-size-of-memory-used-by-the-HADB-server
-
~<integer suffixed by the unit> ((5,120MB to 1,099,511,627,776MB))
Specifies the maximum size of the memory that can be used by the HADB server (shared memory and process memory) with an integer suffixed by the unit.
- Important
-
Specify this operand in the case of an HADB server of version 03-01 or later. This operand and the adb_sys_proc_area_max and adb_sys_rthd_area_max operands in the server definition are mutually exclusive. When you specify this operand, do not specify the adb_sys_proc_area_max operand or the adb_sys_rthd_area_max operand in the server definition.
- ■ Guideline for determining the value to be specified for the adb_sys_memory_limit operand
-
Specify in this operand a value that is equal to or greater than the value determined for Maximum memory capacity used by the HADB server in 6.3 Estimating the HADB server's memory requirement. Alternatively, specify the maximum size of the memory that can be used by the HADB server.
Note that the value specified for the adb_sys_memory_limit operand in the server definition must satisfy the following formula if the adb_sys_shm_huge_page_size operand in the server definition is set to a value other than 0. This is the case where HugePages is used for the shared memory used by the HADB server. Specifying a value that does not satisfy the following formula causes an error to occur when the HADB server starts.
value-specified-for-adb_sys_memory_limit-operand-in-server-definition ≤ kernel-parameter-vm.nr_hugepages × single-page-size-in-HugePages
For details about how to check the value of single-page-size-in-HugePages, see the explanation of the adb_sys_shm_huge_page_size operand in the server definition.
- ■ About the unit of the value specified for the adb_sys_memory_limit operand
-
-
The unit that can be suffixed to the value of this operand is MB (megabyte), GB (gigabyte), or TB (terabyte).
Examples of specifying a value with a unit
set adb_sys_memory_limit = 196608MB
set adb_sys_memory_limit = 128GB
set adb_sys_memory_limit = 64TB
-
If you omit specifying a unit (if you specify only an integer), MB (megabyte) is assumed.
Example of specifying a value without a unit
In the following case, the system assumes that 196608MB was specified:
set adb_sys_memory_limit = 196608
-
The following shows the range of specifiable values for each unit:
- For MB (megabyte)
-
5,120MB to 1,099,511,627,776MB
- For GB (gigabyte)
-
5GB to 1,073,741,824GB
- For TB (terabyte)
-
1TB to 1,048,576TB
-
- ■ Value assumed if the adb_sys_memory_limit operand is not specified
-
If this operand is omitted, the maximum size of process common memory is determined based on the value of the adb_sys_proc_area_max operand in the server definition. The maximum size of real thread private memory per real thread is determined based on the value of the adb_sys_rthd_area_max operand in the server definition.
When this operand is specified, the maximum amount of memory (shared memory and process memory) that can be used by the HADB server is controlled as shown in the following table.
Table 7‒2: List of memory areas controlled by the adb_sys_memory_limit operand No.
Memory area used by the HADB server
Related server definition operand
1
Shared memory
Shared memory management area
--
2
Process common memory#1
Memory for HADB server control
--
3
Other (including memory used for executing SQL statements and commands)
--
4
Real thread private memory#2
Local work table buffer
adb_dbbuff_wrktbl_clt_blk_num
5
Hash grouping area
adb_sql_exe_hashgrp_area_size
6
Hash table area
adb_sql_exe_hashtbl_area_size
7
Hash filter area
adb_sql_exe_hashflt_area_size
8
Other (including work memory used for executing SQL statements and memory used for executing commands)
--
9
Global buffer page
Global buffer
adbbuff -p
10
Buffer for range indexes
adbbuff -a
11
Buffer for table scan
-
adbbuff -v
-
adbbuff -k
12
Global buffer for global work tables
adb_dbbuff_wrktbl_glb_blk_num
13
Process memory
Heap memory
--
- Legend:
-
--: There is no related server definition operand.
- #1
-
The adb_sys_proc_area_max operand in the server definition is related.
- #2
-
The adb_sys_rthd_area_max operand in the server definition is related.
-
- [10] adb_sql_exe_max_rthd_num = maximum-number-of-SQL-processing-real-threads
-
~<integer> ((0 to 4,096)) <<4>>
Specify the maximum number of SQL processing real threads.
An SQL processing real thread is a processing real thread used when executing SQL statements.
For this operand, specify a value equal to or less than the number of CPU cores in the machine on which the HADB server is installed. When you determine the value to be specified, see the following ▪ Consideration on specifying the adb_sql_exe_max_rthd_num operand. Also consider the number of SQL statements that are concurrently executed.
- Important
-
If you change the value specified for this operand, also reassess the value specified for the adb_sys_rthd_num operand. If you specify a value greater than the value of the adb_sys_rthd_num operand for this operand, the value of the adb_sys_rthd_num operand is used.
▪ Consideration on specifying the adb_sql_exe_max_rthd_num operand
If the following condition is satisfied, it might not be possible to use the specified number of processing real threads when executing SQL statements. In such a case, the SQL statements wait until the number of processing real threads specified by this operand can be allocated. The formula to be used depends on whether the client-group facility is used.
Formula (when the client-group facility is not used)
Formula (when the client-group facility is used)
- Explanation of variables
-
A: Value of the adb_sys_rthd_num operand
B: Value of the adb_sys_max_users operand
C: Value of the adb_sql_exe_max_rthd_num operand
D: Maximum number of SELECT statements to be concurrently executed in a single connection (if the resulting value is 0, use 1)
E: Guaranteed minimum number of processing real threads usable by the specified client group or command group
F: Maximum number of concurrent connections for the specified client group or command group
When this operand and the client definition adb_sql_exe_max_rthd_num operand are both specified, the value specified by the client definition adb_sql_exe_max_rthd_num operand is prioritized. However, if the value specified by the client definition adb_sql_exe_max_rthd_num operand is greater than the value specified by this operand, the former is not prioritized, and the value specified by this operand is prioritized.
For details about the adb_sql_exe_max_rthd_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
If the value of the adb_sql_exe_max_rthd_num operand is greater than the maximum number of processing real threads usable by any of the specified client groups and command groups, the maximum number of processing real threads that can be used is assumed to be the same as the value of the adb_sql_exe_max_rthd_num operand for that group.
For details about the maximum number of processing real threads that can be used, see the description of the -r option of the adbcltgrp operand in 7.2.12 Operands and options related to the client-group facility (command format).
The following table shows the relationship between this operand's value and the number of SQL processing real threads used during execution of SQL statements.
Table 7‒3: Number of SQL processing real threads used during execution of SQL statements No.
SQL statement to be executed
adb_sql_exe_max_rthd_num operand's value
0 specified
1 specified
2 or a greater value specified
1
Definition SQL statements
ALTER TABLE statement (for changing an archivable multi-chunk table to a regular multi-chunk table)#1
The number of SQL processing real threads that are used is 0.
The number of SQL processing real threads that are used is 0.
The number of SQL processing real threads that are used is the same as the value of the adb_sql_exe_max_rthd_num operand.
2
DROP INDEX statement#1
3
DROP SCHEMA statement#1
4
DROP TABLE statement#1
5
DROP USER statement#1
6
SCHEMA of the REVOKE statement (revoking schema operation privileges)#1
7
Data manipulation SQL statements
PURGE CHUNK statement#1
The number of SQL processing real threads that are used is the value determined by the formula shown below.#2
8
TRUNCATE TABLE statement#1
9
SELECT statement#3
The number of SQL processing real threads that are used is 1.
The number of SQL processing real threads that are used is the same as the value of the adb_sql_exe_max_rthd_num operand.
10
Other SQL statements#4
The number of SQL processing real threads that are used is 0.
The number of SQL processing real threads that are used is 0.
- #1
-
If a value of 1 or less is specified in this operand, only connection threads are used to execute SQL statements. If a value of 2 or greater is specified in this operand, the number of SQL processing real threads indicated in the table is used to execute SQL statements when segments are released.
- #2
-
The number of SQL processing real threads that are used is determined by the following formula:
- #3
-
If a value of 0 is specified in this operand, SQL statements are executed without applying out-of-order execution. If a value of 1 or greater is specified in this operand, the number of SQL processing real threads indicated in the table are used to execute SQL statements with out-of-order execution applied.
- #4
-
Only connection threads are used to execute SQL statements regardless of the value set for this operand.
- Important
-
If 0 is specified in the adb_sys_uthd_num operand in the server definition, SQL statements are executed in the same manner as when 0 is specified in this operand.
- [11] adb_sql_exe_hashgrp_area_size = hash-group-area-size
-
~<integer> ((0, 4 to 1,000,000)) <<4,800>> (kilobytes)
Specify a hash group area size in kilobytes.
When you execute an SQL statement for which local hash grouping applies (hashing is used to group the items in the GROUP BY clause), the specified amount of hash group area is allocated in the real thread private memory for each SQL processing real thread.
If 0 is specified, local hash grouping is not executed. If a value smaller than 4 is specified, 4 is assumed.
A separate hash group area is allocated for each SQL statement to which local hash grouping applies. Its size is determined by the values specified in each GROUP BY clause specified in the SQL statement.
If the size of the specified area is insufficient for local hash grouping, as much hash grouping is performed as can be processed in the size that was specified. Rows that could not be processed are stored as work tables in the work table DB area.
Use the value determined from the formula below as a guideline for the hash group area size. If an SQL statement contains multiple query specifications with the GROUP BY clause specified, use the largest of the values determined for the individual queries.
- Formula
-
- Explanation of variables
-
-
LGC
Sum total of the data lengths of the grouping columns
See 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.
-
LSF
Data length of the set function result
-
NGR
Number of rows in the grouping result
-
When this operand and the client definition adb_sql_exe_hashgrp_area_size operand are both specified, the value specified by the adb_sql_exe_hashgrp_area_size operand in the client definition is prioritized. For details about the adb_sql_exe_hashgrp_area_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
- [12] adb_sql_exe_hashtbl_area_size = hash-table-area-size
-
~<integer> ((0 to 4,194,304)) <<2,000>> (megabytes)
Specify a hash table area size in megabytes.
When you execute an SQL statement to which hash retrieval is applied, a hash table area is allocated in the real thread private memory for each SQL processing real thread, according to the following formula:
- Formula
-
↑value-specified-for-this-operand ÷ value-specified-for-adb_sql_exe_max_rthd_num-operand↑
If 0 is specified for the adb_sql_exe_max_rthd_num operand, hash retrieval is not applied.
- Note
-
Hash retrieval is processing that uses a hash table area. The following types of processing apply:
-
Hash join as a table joining method
-
Global hash grouping as a grouping method
-
Hash execution as a method for processing subqueries
-
Hash execution as a method for processing SELECT DISTINCT
-
Hash execution as a method for processing the set operation
-
For details about the preceding types of hash retrieval, see the following sections in the HADB Application Development Guide:
-
Table joining methods
-
How to process subqueries
-
Grouping methods
-
Method for processing SELECT DISTINCT
-
Methods for processing set operations
A hash table area is allocated for each SQL statement to which hash retrieval is applied.
If 0 is specified for this operand, hash retrieval is not applied. Specify 0 for this operand only in the following cases:
- Cases in which 0 can be specified for this operand
-
Cases in which a memory shortage error cannot be avoided because the value specified for the following operands cannot be increased (when the KFAA30919-E or KFAA30930-E message is output):
-
adb_sys_rthd_area_max operand
-
adb_sys_memory_limit operand
-
adb_dbbuff_wrktbl_clt_blk_num operand
-
If the specified area size is insufficient for hash retrieval, a hash table is created that is as large as can be processed with the specified area size. Rows that cannot be processed with hash tables are stored as work tables in the work table DB area.
Specify the value determined using the following formula for the hash table area size.
- Formula
-
- Explanation of variables
-
-
maximum-hash-table-area-size-by-SQL-statement
The largest among the hash table area sizes by SQL statement determined for all SQL statements to be executed. Use the following formula to determine the hash table area size by SQL statement:
For details about the number of hash tables, the maximum row length in the hash tables, and the maximum number of intermediate retrieval results, see 6.25.3 Number of work tables created during retrieval using hash tables.
-
maximum-number-of-SQL-processing-real-threads
This is the value specified for the adb_sql_exe_max_rthd_num operand in the server definition. However, if 0 is specified for the adb_sql_exe_max_rthd_num operand, assume 1 for the calculation.
-
When this operand and the client definition adb_sql_exe_hashtbl_area_size operand are both specified, the value specified by the adb_sql_exe_hashtbl_area_size operand in the client definition is prioritized. However, if the value specified by the client definition adb_sql_exe_hashtbl_area_size operand is greater than the value specified by this operand, the former is not prioritized, and the value specified by this operand is prioritized. For details about the adb_sql_exe_hashtbl_area_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
- [13] adb_sql_exe_hashflt_area_size = hash-filter-area-size
-
~<integer> ((0 to 419,430)) <<value of adb_sql_exe_hashtbl_area_size server definition operand ÷ 10>> (megabytes)
Specify the size (in megabytes) of the hash filter area in which a hash filter is to be created. The hash filter is applied when the following types of processing are executed:
-
Hash join as a table joining method
-
Hash execution as a method for processing subqueries
For details about hash join, hash execution, and hash filters, see About hash join and How to process subqueries in the HADB Application Development Guide.
A hash filter area is allocated for each SQL statement to which hash filters are applied when hash join or hash execution is performed. When you execute an SQL statement to which hash filters are applied, a hash filter area is allocated in the real thread private memory for each SQL processing real thread, according to the following formula:
↑value-specified-for-this-operand ÷ value-specified-for-adb_sql_exe_max_rthd_num-operand↑
Guideline and adjustment of the value to be specified
-
As a guideline, use the value determined from the following formula as the value to be specified for this operand:
hash-filter-area-size = ↑value-specified-for-adb_sql_exe_hashtbl_area_size-operand (hash-table-area-size) ÷ 10↑
-
Specify 0 for this operand only if the processing performance is degraded as a result of applying hash filters when hash join or hash execution is performed.
-
If the value of this operand is too small to allocate a sufficient hash filter area, no hash filters are applied when hash join or hash execution is performed. You can check whether hash filters were applied from the values of Hashtbl_filter_num and Hashtbl_sum_filter_check_cnt in the access path statistical information.
In a case where the value specified for this operand is not 0, if the value output to Hashtbl_filter_num is not 0 and the value output to Hashtbl_sum_filter_check_cnt is 0, the hash filter area is insufficient. Because no hash filters have been applied, increase the value of this operand. For details about the access path statistical information, see (e) Information related to hash table areas in (2) Items that are output as access path statistical information in 10.11.3 Examples of output of and output items for access path statistical information.
Notes
-
If 0 is specified for this operand, no hash filters are applied when hash join or hash execution is performed.
-
If 0 is specified for the adb_sql_exe_max_rthd_num operand, hash join or hash execution is not applied.
-
If this operand is specified when the adb_sql_exe_hashflt_area_size operand is specified in the client definition, the value specified by the adb_sql_exe_hashflt_area_size operand in the client definition is prioritized. However, if the value specified by the client definition adb_sql_exe_hashflt_area_size operand is greater than the value specified by this operand, the former is not prioritized, and the value specified by this operand is prioritized. For details about the adb_sql_exe_hashflt_area_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
-
- [14] adb_sql_opt_drvtbl_grping_prior = {LOCAL|GLOBAL}
-
Specify the grouping method that is to be prioritized when an SQL statement that meets all of the following conditions is executed:
-
Only one table reference is specified in the FROM clause.
-
The preceding table reference is an internal derived table that is not expanded.
-
The GROUP BY clause is specified in the outermost query specification.
Normally, this operand does not need to be specified.
If there are many (about 30 or more) SQL processing real threads that can be used to process the SQL statement, specifying GLOBAL might shorten the time required to execute the SQL statement.
-
LOCAL
Specify this to prioritize local hash grouping as the grouping method.
-
GLOBAL
Specify this to prioritize global hash grouping as the grouping method.
The following example describes the relationship between the value of this operand and the grouping method:
Example:
SELECT "C2" FROM (SELECT "C1",COUNT(*) FROM "T1" GROUP BY "C1") AS "DT1" ("C1","C2") GROUP BY "C2"
If you execute the preceding SQL statement by specifying LOCAL for this operand, local hash grouping is used as the method for processing the outer grouping (underlined grouping).
If you execute the preceding SQL statement by specifying GLOBAL for this operand, global hash grouping is used as the method for processing the outer grouping (underlined grouping).
- Multi-node function
-
If you are using the multi-node function, we recommend that you specify the same value for this operand and all HADB servers on all nodes. If you specify a different value on each node, the access path might become different on each node where the SQL statement is executed.
-
- [15] adb_sys_rthd_area_max = maximum-size-of-real-thread-private-memory-that-can-be-allocated
-
~<integer> ((1,024 to 2,000,000,000)) <<1,024>> (megabytes)
Specify in megabytes the maximum size of real thread private memory that can be allocated for each real thread.
There is no need to specify this operand for an HADB server of version 03-01 or later. Instead, specify the adb_sys_memory_limit operand in the server definition. When this operand is specified, the adb_sys_memory_limit operand in the server definition cannot be specified.
If you specify this operand, specify the value that is obtained from the following formula:
- Formula
-
- Explanation of variables
-
-
MACHINE_MEMORY_SIZE
Size of the physical memory installed in the server machine on which the HADB server is installed (megabytes)
-
SHMMAN
Size of the area (in kilobytes) acquired for starting the HADB server that stores information about the shared memory used by HADB
For details about the variable SHMMAN, see (1) Determining the shared memory management area requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.
-
SHM_BUFGLOBAL
Size of the global buffer area acquired for starting the HADB server (kilobytes)
For details about the variable SHM_BUFGLOBAL, see (2) Determining the global buffer page requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.
-
RTHD_NUM
Number of real threads used by the HADB server (threads)
Use the formula below to determine its value. The formula to be used depends on whether the multi-node function is used.
Formula (when the multi-node function is not used)
Formula (when the multi-node function is used)
-
- [16] adb_sys_proc_area_max = maximum-size-of-process-common-memory-that-can-be-allocated
-
~<integer> ((5,120 to 2,000,000,000)) <<8,192>> (megabytes)
Specify in megabytes the maximum size of process common memory that can be allocated.
There is no need to specify this operand for an HADB server of version 03-01 or later. Instead, specify the adb_sys_memory_limit operand in the server definition. When this operand is specified, the adb_sys_memory_limit operand in the server definition cannot be specified.
If you specify this operand, specify the value that is obtained from the following formula:
- Formula
-
- Explanation of variables
-
-
MACHINE_MEMORY_SIZE
Size of the physical memory installed in the server machine on which the HADB server is installed (megabytes)
-
SHMMAN
Size of the area (in kilobytes) acquired for starting the HADB server that stores information about the shared memory used by HADB
For details about the variable SHMMAN, see (1) Determining the shared memory management area requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.
-
SHM_BUFGLOBAL
Size of the global buffer area acquired for starting the HADB server (kilobytes)
For details about the variable SHM_BUFGLOBAL, see (2) Determining the global buffer page requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.
-
RTHD_NUM
Number of real threads used by the HADB server (threads)
Use the formula below to determine its value. The formula to be used depends on whether the multi-node function is used.
Formula (when the multi-node function is not used)
Formula (when the multi-node function is used)
-
- [17] adb_sys_shm_huge_page_size = multiple-of-the-single-page-size-in-HugePages
-
~<integer> ((0 to 2,147,483,647)) <<0>> (kilobytes)
When HugePages is applied to the shared memory used by the HADB server, specify in kilobytes a multiple of the single page size in HugePages.
For this operand, we recommend that you specify the value determined by using the command shown below (the single page size in HugePages). When using this command to determine the value, pay attention to the units. If you specify a value that is not a multiple of the single page size in HugePages, you will not be able to allocate shared memory and thus will not be able to start the HADB server.
- Command to be executed
-
grep Hugepagesize /proc/meminfo
- Execution result example
-
Hugepagesize: 2048 kB
In this example, we recommend that you specify 2048.
If you specify 0 for this operand, HugePages is not applied to the shared memory that the HADB server uses.
To apply HugePages to the shared memory that the HADB server uses, you must set up kernel parameters. For details about setting up the kernel parameters, see 6.2 Estimating the kernel parameters.
- [18] adb_dbbuff_wrktbl_glb_blk_num = number-of-pages-in-global-buffer-for-global-work-tables
-
~<integer> ((5 to 100,000,000)) <<128>>
Specify the number of pages in the global buffer for global work tables.
The global buffer allocated by specifying this operand is used only when an SQL statement that creates global work tables is executed.
For details about how to estimate the value to specify for this operand, see 6.25.1 Estimating the number of pages in the global buffer for global work tables.
For details about global work tables, see Types of work tables under Considerations when executing an SQL statement that creates work tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.
- [19] adb_dbbuff_wrktbl_clt_blk_num = number-of-pages-in-buffer-for-local-work-tables
-
~<integer> ((5 to 100,000,000)) <<256>>
Specify the number of pages in the buffer for local work tables.
The buffer for local work tables allocated by specifying this operand is used only when an SQL statement that creates local work tables is executed. A buffer for local work tables containing the number of pages specified by this operand is allocated for each real thread.
For details about how to estimate the value to specify for this operand, see 6.25.2 Estimating the number of pages in the buffer for local work tables.
For details about local work tables, see Types of work tables under Considerations when executing an SQL statement that creates work tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.
- Note
-
-
If this operand and the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition are both specified, the value specified for the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition takes precedence. For details about the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
-
If this operand and the export option adb_export_wrktbl_blk_num are both specified, the value specified for the export option adb_export_wrktbl_blk_num takes precedence. For details about the export option adb_export_wrktbl_blk_num, see Format of export options in Specification format for the adbexport command under adbexport (Export Data) in the manual HADB Command Reference.
-
You can use the adbls -d lbuf command to check the number of pages in the buffer for the local work table that is actually allocated. For details about the adbls -d lbuf command, see adbls -d lbuf (Display Local Work Table Buffer Information) in the manual HADB Command Reference.
-
While the HADB server is running, you can use the adbmodbuff command to change the number of pages in the buffer for the local work table specified in this operand. For details about the adbmodbuff command, see adbmodbuff (Change Buffer) in the manual HADB Command Reference.
-
- [20] adb_sql_tbldef_cache_size = table-definition-pool-size
-
~<integer> ((500 to 2,048,000)) <<5,120>> (kilobytes)
Specify in kilobytes the size of the table-definition pool that stores table-definition information.
Normally, this operand does not need to be specified. To use cost information for operation, read the following description, and specify an appropriate value.
Table-definition information is the information that is required to access a table. This information includes not only the information about the table but also the information about the columns and indexes. A dictionary table is accessed when table-definition information is created. Once table-definition information is used, it is cached in the table-definition pool and managed by the LRU algorithm.
Table-definition information that has been cached in the table-definition pool can be fetched from the pool for reuse the next time it becomes necessary. Therefore, it is unnecessary to create table-definition information again by accessing the dictionary table. Therefore, if you use the table-definition pool, you can expect fewer I/O operations and shorter CPU usage time.
When a table is accessed, if the table-definition pool does not contain the necessary table-definition information, the table-definition information is created and cached in the table-definition pool. If the table-definition pool does not have sufficient free space for caching more information, the definition information for other tables that are not recently used is excluded, and then the new table-definition information is cached. If no free space can be reserved in the table-definition pool, the table-definition information is not cached.
To avoid this, specify the table-definition pool size determined from the following formula so that the table-definition information of all tables that are used in a transaction executed by HADB can be cached. If exclusion of table-definition information occurs due to insufficient free space in the table-definition pool, the processing performance of the SQL statement might be degraded.
- Formula
-
- Explanation of variables
-
-
m
Number of tables accessed frequently
-
Wi
Table-definition information length (bytes)
Use the following formula to determine its value:
Formula
Wi = 1,012 + (512 + 32,008#1) × col_num + max_rowsz + 1,156 × idx_num + 15,864#2
- #1
-
Add this value if cost information has been collected in the table.
For details about how to check whether cost information has been collected in the table, see (1) Determining the names of all base tables from which cost information was collected and the collection dates and times in C.9 Searching system tables.
- #2
-
Add this value if the processing-target table is an archivable multi-chunk table.
- col_num
-
Number of columns in the table
- max_rowsz
-
Maximum row length (bytes)
Determine the maximum row length based on the formula for the row length ROWSZ in (1) Determining the number of pages for base rows (variable BP(i)) under 5.8.2 Determining the number of pages for storing each type of row.
- idx_num
-
Number of indexes defined for the table
-