The SESAM/SQL DBH reserves an area of main memory specially for storing SQL access plans.
SQL access plan
An SQL access plan is an evaluation rule for an SQL statement.
When a static SQL statement is used, an SQL access plan is created the first time the statement is issued. If the statement executes successfully, the plan is not deleted immediately, but stored in the plan buffer. If the same SQL statement is executed again, the relevant plan is already available and need not be generated again; this leads to a considerable performance gain.
A dynamic SQL statement generates an SQL access plan in connection with the EXECUTE IMMEDIATE or PREPARE statements (see the “SQL Reference Manual Part 1: SQL Statements”). When the EXECUTE IMMEDIATE statement is used, the associated plan is generated and executed immediately. When the PREPARE statement is used, the plan remains in the plan buffer at least until the end of the transaction so that follow-up statements can also use it (see also the “Performance” manual).
If the same dynamic SQL statement is processed with EXEC, IMMEDIATE or PREPARE in a subsequent transaction, the associated plan is reused, if it is still in the plan buffer.
Size of the plan buffer
The plan buffer comprises a primary buffer used by the DBH to store SQL access plans, and a secondary buffer, which contains administrative information. The size of the plan buffer depends on two factors:
the DBH option SQL-SUPPORT and its operand PLANS, which defines the minimum number of concurrent SQL access plans
the DBH option COLUMNS, which defines the area size for retrieval statements.
System administrators can tune both settings to suit the requirements of the current session (see the “Database Operation” manual).
If the plan buffer is too small, the SQL access plans are displaced according to the LRU principle: The plan that has been used the least recently is overwritten by a new plan.
The SESAM/SQL utility SESMON supplies operating statistics for the plan buffer. The “SQL INFORMATION” screen displays a selection of information, including the size of the plan buffer and details of how it is currently used.