5.3.7 Operands related to buffers

18) pd_sql_object_cache_size = SQL-object-buffer-size
[Figure]<unsigned integer> (KB)
  • 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.
  • 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, it should be allocated 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 D.2 Formulas for determining size of SQL object buffer (pd_sql_object_cache_size).
Tuning the specified value
For details about how to tune the SQL object buffer size, see the HiRDB Version 8 System Operation Guide.
19) pd_table_def_cache_size = table-definition-information-buffer-size
[Figure]<unsigned integer> (KB)
  • 32-bit mode: ((100-65535))
  • 64-bit mode: ((100-2000000))
Specifies in kilobytes the size of the buffer (shared memory) for table definition information that has been used. Table definition information is used during pre-processing of an SQL statement.
Advantages
  • Table definition information that has been used is kept in the memory as long as possible and thus can be used subsequently without an input operation.
  • Performance improves when a large number of dynamic SQLs are used.
Specification guidelines
Tuning the specified value
For details about how to tune the size of the table definition information buffer, see the HiRDB Version 8 System Operation Guide.
20) pd_auth_cache_size = user-privilege-information-buffer-size
[Figure]<unsigned integer>((1-100)) (KB)
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 8 System Operation Guide.
21) pd_view_def_cache_size = view-analysis-information-buffer-size
[Figure]<unsigned integer> (KB)
  • 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.
Tuning the specified value
For details about how to tune the size of the buffer for view analysis information, see the HiRDB Version 8 System Operation Guide.
22) pd_type_def_cache_size = user-defined-type-information-buffer-size
[Figure]<unsigned integer> (KB)
  • 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.
  • Specification of this operand improves performance when many dynamic SQLs are used.
Specification guidelines
The specified size should be the total of the sizes 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 (KB)
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 8 System Operation Guide.
23) pd_routine_def_cache_size = routine-definition-information-buffer-size
[Figure]<unsigned integer> (KB)
  • 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.
Application criteria
This operand should be specified 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 D.5 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 8 System Operation Guide.
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.
24) pd_registry_cache_size = registry-information-buffer-size
[Figure]<unsigned integer>((0-65536)) (KB)
This operand is related to plug-ins. If you use a plug-in that uses registry information, Hitachi recommends the use of this operand. Hitachi also recommends the use of this operand if you use HiRDB Text Search Plug-in.
Specify the size of the buffer (shared memory) for storing registry information (units: KB). 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.
  • 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 (KB)
a: Average registry key length (KB)
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:

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 8 System Operation Guide.