6.2.7 Operands related to buffers

15) pd_sql_object_cache_size = SQL-object-buffer-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((22-256000))
  • 64-bit mode: ((22-2000000))
Specifies in kilobytes the size of the buffer area (shared memory) in which SQL objects are to be placed.
Specification guidelines
  • SQL objects are saved in a buffer until the user's transaction has terminated. The buffer must be large enough to store the SQL objects of all transactions that will be executed concurrently.
  • When the hit rate is low for SQL object buffers, performance might be degraded due to the overhead required for SQL parsing processing.
  • SQL analysis can be reduced by saving the SQL objects of static SQLs in the buffer after transaction termination (until the buffer runs out of space) and sharing them among multiple users who execute the same UAP. To effectively utilize the buffer, allocate it so that the SQL objects of frequently-used UAPs are resident in the buffer.
  • To estimate the buffer size, first determine the buffer size needed for UAP execution from the length of the SQL objects from the SQL statements to be issued by the UAP. Then, compute the buffer size by considering the number of UAPs that will be executed concurrently and the number of concurrently executing users.
  • For details about how to estimate the length of the SQL object from a single SQL statement, see C.2 Formulas for determining size of SQL object buffer (pd_sql_object_cache_size).
Operand default
If this operand is omitted, the specification of the same operand in the server common definition or in the system common definition, in that order, is assumed. If the same operand is also omitted in the server common definition and the system common definition, the following value is assumed:
  • 32-bit mode: (value of pd_max_users + n) [Figure] 22
  • 64-bit mode and recommended mode: (value of pd_max_users + n) [Figure] 40
  • 64-bit mode and 0904 compatibility mode: (value of pd_max_users + n) [Figure] 22
n: If a multiple front-end server configuration will be applied to a HiRDB parallel server configuration, 4. For others, 3.
Tuning the specified value
For details about tuning the SQL object buffer size, see the HiRDB Version 9 System Operation Guide.
Effects on individual estimation formulas
If the value of the pd_sql_object_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
16) pd_table_def_cache_size = table-definition-information-buffer-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((100-65535))
  • 64-bit mode: ((100-2000000))
Specifies in kilobytes the size of the buffer (shared memory) for table and sequence generator definition information. This information is used during SQL statement pre-processing. Definition information stored in this buffer is managed by the LRU method.
Advantages
  • Table and sequence generator definition information that has been used is retained in memory as long as possible so that it can be used again without an input operation.
  • Performance improves when a large number of dynamic SQLs are used.
  • The number of communications with the dictionary server is reduced.
Specification guidelines
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted in the server common definition, the default is the following value:
  • 32-bit mode: [Figure]
  • 64-bit mode and recommended mode: 16000
  • 64-bit mode and 0904 compatibility mode: [Figure]
n: If a multiple front-end server configuration will be applied to a HiRDB parallel server configuration, 4. For others, 3.
Tuning the specified value
For details about how to tune the size of the table definition information buffer, see the HiRDB Version 9 System Operation Guide.
Effects on individual estimation formulas
If the value of the pd_table_def_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
17) pd_auth_cache_size = user-privilege-information-buffer-size
~<unsigned integer>((1-100)) (kilobytes)
Specifies in kilobytes the size of the buffer (shared memory) for user privilege information.
Specification guidelines
  • The user privilege information buffer stores CONNECT privilege, DBA privilege, and audit privilege information. If this buffer contains no information, information is obtained from a dictionary table during HiRDB connection, thus lengthening the response time. Therefore, specify a buffer size that can store the information for the users who are always connected.
  • Storing the user privilege information of each user requires 68 bytes. Use this information when computing the total buffer size.
Tuning the specified value
For details about how to tune the size of the user privilege information buffer, see the HiRDB Version 9 System Operation Guide.
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted from the server common definition, 10 (or 1 for 0904 compatibility mode) is assumed.
Effects on individual estimation formulas
If the value of the pd_auth_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
18) pd_view_def_cache_size = view-analysis-information-buffer-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((0-32000))
  • 64-bit mode: ((0-2000000))
Specifies in kilobytes the size of the buffer (shared memory) for view analysis information.
Advantage
View analysis information that has been used is kept in the shared memory and can be used subsequently without an I/O operation.
Specification guideline
The total size of the view analysis information for frequently-used view tables is specified. For details about determining the size of the view analysis information buffer, see C.3 Formulas for determining size of view analysis information buffers (pd_view_def_cache_size).
Tuning the specified value
For details about how to tune the size of the buffer for view analysis information, see the HiRDB Version 9 System Operation Guide.
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted in the server common definition, the default is the following value:
  • 32-bit mode: [Figure]
  • 64-bit mode and recommended mode: 30000
  • 64-bit mode and 0904 compatibility mode: [Figure]
n: If a multiple front-end server configuration will be applied to a HiRDB parallel server configuration, 4. For others, 3.
Effects on individual estimation formulas
If the value of the pd_view_def_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
19) pd_type_def_cache_size = user-defined-type-information-buffer-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((100-65536))
  • 64-bit mode: ((100-2000000))
This operand is applicable to user-defined types; specification of this operand is recommended when user-defined types will be used.
Specifies in kilobytes the size of the buffer (shared memory) for information on user-defined types. When a user-defined type is used, information on it is stored in this buffer. This information is used during pre-processing of SQL statements.
Advantages
  • When user-defined type information is stored in this buffer, it is not necessary to access the dictionary table when the same user-defined type is used subsequently, thus reducing the number of input operations and the CPU usage time.
  • The number of communications with the dictionary server is reduced.
  • Specification of this operand improves performance when many dynamic SQLs are used.
Specification guidelines
Specify the total size of the definition information for frequently-used user-defined types that are defined in tables. The following formula can be used to determine the definition information size for one user-defined type:
[Figure]{((0.3 + 0.2 [Figure] a + 0.1 [Figure] b) + 3) [Figure] 4}[Figure][Figure] 4 (kilobytes)
a: Number of user-defined type attributes
b: Number of subtypes that have inherited a supertype
Tuning the specified value
For details about how to tune the size of the buffer for user-defined type information, see the HiRDB Version 9 System Operation Guide.
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted in the server common definition, the default is 0.
Effects on individual estimation formulas
If the value of the pd_type_def_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
20) pd_routine_def_cache_size = routine-definition-information-buffer-size
~<unsigned integer> (kilobytes)
  • 32-bit mode: ((0, 20-65536))
  • 64-bit mode: ((0, 20-2000000))
Specifies in kilobytes the size of the buffer (shared memory) for storing the following types of definition information (this information is used during pre-processing of SQL statements):
  • Plug-in facility definition information
  • System definition scalar facility definition information
  • Routine definition information
Advantages
  • When these types of definition information are stored in this buffer, it is not necessary to access the dictionary table when the same information is used subsequently, thus reducing the number of I/O operations and CPU usage time.
  • The number of communications with the dictionary server is reduced.
Application criteria
Specify this operand when a large number of the following types of SQL statements are used:
  • SQL statements that use a plug-in
  • SQL statements that use the system definition scalar facility
  • SQL statements that use a routine
Specification guidelines
For details about how to determine the value for this operand, see C.6 Formulas for determining size of routine definition information buffer (pd_routine_def_cache_size).
Tuning the specified value
For details about how to tune the size of the routine definition information buffer, see the HiRDB Version 9 System Operation Guide.
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted from the server common definition, 300 (or 100 for 0904 compatibility mode) is assumed.
Note
If the value specified for this operand is smaller than the total of the sizes of the definition information for all plug-ins, the definition information on plug-in facilities will not be allocated in the buffer.
Effects on individual estimation formulas
If the value of the pd_routine_def_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server
21) pd_registry_cache_size = registry-information-buffer-size
~<unsigned integer>((0-65536)) (kilobytes)
This operand is related to plug-ins. If you use a plug-in that uses registry information, we recommend that you use this operand. We also recommend that you use this operand if you use HiRDB Text Search Plug-in.
Specify the size of the buffer (shared memory) for storing registry information (in kilobytes). When registry information is used, it is stored in the buffer. Registry information is used during the execution of an SQL statement.
Advantages
  • Once registry information is stored in this buffer, it is not necessary to access the registry when the same information is used subsequently, thus reducing the number of I/O operations and CPU usage time.
  • The number of communications with the dictionary server is reduced.
  • Specifying this operand can improve performance when a plug-in that makes frequent use of registry information is used.
Specification guidelines
Use the following formula to determine the registry information buffer size:
[Figure](0.3 + a)[Figure][Figure] b (kilobytes)
a: Average registry key length (kilobytes)
The average registry key length can be determined with the following SQL statement:

SELECT AVG(KEY_LENGTH) FROM MASTER.SQL_REGISTRY

Because the result of this SQL statement is output in bytes, convert it to kilobytes.
b: Number of registry keys registered
The number of registry keys registered can be determined with the following SQL statement:

SELECT COUNT(*)FROM MASTER.SQL_REGISTRY

Tuning the specified value
For details about how to tune the size of the buffer for registry information, see the HiRDB Version 9 System Operation Guide.
Operand default
If this operand is omitted, the specification of the same operand in the server common definition is assumed. If the same operand is also omitted in the server common definition, the default is 10.
Effects on individual estimation formulas
If the value of the pd_registry_cache_size operand is changed, the following estimation formula is affected:
HiRDB Version 9 Installation and Design Guide:
  • Formula for the shared memory used by a front-end server