This section explains tuning of the buffer length for SQL objects (pd_sql_objet_cache_size operand value). When SQL object buffer tuning is performed, the following must be taken into account:

Relationship between an SQL object and an SQL object buffer

HiRDB analyzes each SQL statement in a UAP and creates an execute-form SQL object when the UAP executes. If the same UAP is executed again by another user and if an SQL object corresponding to a specified SQL statement is found in an SQL object buffer, the time required for creating the SQL object is eliminated, thereby reducing SQL statement processing time.

Once an SQL object is deleted from its buffer, it must be analyzed again to re-create it during execution, resulting in a low SQL object buffer hit rate. Therefore, when an SQL statement whose database accesses count is low is executed, a long processing time is required. Furthermore, if the buffers do not contain the table definition information necessary to analyze an SQL statement, the data dictionary tables must be accessed. If many tables or columns are accessed or data is accessed many times, the amount of locked resources increases, thereby increasing the processing time.

SQL object buffers

In the case of a HiRDB parallel server configuration, multiple SQL objects are created for a single SQL statement. The characteristics of SQL objects in the back-end and front-end servers are discussed below.

• Back-end server
  SQL objects are created in execute form for each corresponding back-end server (including a floating server). Of all the SQL objects buffered in the front-end server, only those SQL objects for a particular back-end server are buffered in the SQL object buffers of that back-end server; therefore, fewer SQL object buffers are required for a back-end server than for the front-end server.
  
• Front-end server
  The SQL object buffers for the front-end server contain all SQL objects for the back-end servers. A large number of SQL object buffers is required in order to take advantage of the buffering effects, because cost-based optimization creates SQL objects for selectable access procedures.
  The same applies to stored procedures and stored functions. It should be noted that the size of an SQL object is larger for a stored procedure or a stored function than for a normal SQL.
  

Performance can be improved efficiently if this information is analyzed together with the following statistical information:

• SQL object information
  This information is collected in order to analyze the buffer length required for each SQL object.
  
• SQL information
  This information is collected in order to determine whether the buffer length is too small.
  

Organization of this subsection

(1) Analysis method (1)

(2) Analysis method (2)

(3) Analysis method (3)

(4) Analysis method (4)