To process ad hoc SQL statements using the statement cache:
Adaptive Server parses the statement.
If the statement should be cached (see “Caching conditions”), Adaptive Server computes a hash value from the statement. Adaptive Server uses this hash value to search for a matching statement in the statement cache (see “Statement matching criteria”).
If a match is found in the statement cache, Adaptive Server skips to step 4.
If a match is not found, Adaptive Server proceeds to step 2.
Adaptive Server caches the SQL statement text.
Adaptive Server wraps the SQL statement with a lightweight stored procedure and changes any local variables into procedure parameters. The internal representation of the lightweight procedure is not yet compiled into the plan.
Adaptive Server converts the SQL statement into an execute statement for the corresponding lightweight procedure.
If there is no plan in the cache, Adaptive Server compiles the procedure and caches the plan. Adaptive Server compiles the plan using the assigned runtime values for the local variables.
If the plan exists but is invalid, Adaptive Server returns to step 3 using the text of the cached SQL statement.
Adaptive Server then executes the procedure. Substituting the lightweight procedure increments the @@nestlevel global variable.
Adaptive Server matches an ad hoc SQL statement to a cached statement by the SQL text and by login (particularly if both users have sa_role), user ID, database ID, and session state settings. The relevant session state consists of settings for these set command parameters:
forceplan
jtc
parallel_degree
prefetch
quoted_identifier
table count
transaction isolation level
chained (transaction mode)
Settings for these parameters determine the behavior of the plan Adaptive Server produces for a cached statement. See the Reference Manual: Commands.
You must configure set chained on/off in
its own batch if you enable the statement cache.
Adaptive Server caches statements according to these conditions:
Adaptive Server currently caches select, update, delete, and insert select statements with at least one table reference.
Statements are not cached if the abstract plan dump or abstract plan load parameters are enabled. That is, you cannot enable the statement cache and enable the abstract plan load and abstract plan dump at configuration parameters the same time
Adaptive Server does not cache select into statements, cursor statements, dynamic statements, plain insert (not insert select) statements, and statements within stored procedures, views, and triggers. Statements that refer to temporary tables are not cached, nor are statements with language parameters transmitted as binary large object (BLOB) datatypes. Statements that are prohibitively large are not cached. Also, select statements that are part of a conditional if exists or if not exists clause are not cached.
Each cached statement requires approximately 1K memory in the statement cache, depending on the length of the SQL text. Each cached plan requires at least 2K of memory in the procedure cache. To estimate the statement cache memory required, account for the following for each statement to be cached:
The length of the SQL statement, in bytes, rounded up to the nearest multiple of 256.
Approximately 100 bytes overhead.
The size of the plan in the procedure cache. This size is equivalent to the size of a stored procedure plan containing only the cached statement. There may be duplicates of the plan for a single cached statement being used concurrently by two or more users.
