Statement Cache vs Procedure Cache for ad hoc queries in Sybase ASE
Statement Cache
For ad hoc SQL queries, ASE wraps the SQL statement with a lightweight stored procedure (LWP) and changes any local variables into procedure parameters. ASE will assign an object ID to this LWP. The statement cache will save the SQL text and the corresponding LWP object ID.
For an incoming statement, ASE computes a hash value from the statement, and uses this hash value to search for a matching statement in the statement cache.
- If a match is found in the statement cache, then search for the object ID in the procedure cache.
- If a match is not found, ASE first wraps up the statement in LWP, assigns an object ID, and caches this statement in the statement cache. Then the LWP is transferred to procedure cache.
Procedure cache
The procedure cache is a MRU/LRU (most recently used/least recently used) chain of stored procedure query plans. The entries are identified by the object ID.
When there is no plan for the corresponding object ID in the procedure cache, ASE compiles the procedure and caches the plan. The plan is compiled using the assigned runtime values for the local variables. If the plan exists but is invalid, ASE will build a new LWP using the text of the cached SQL statement.
Statement Cache Matching
ASE 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. So if two users submit the same query, as the bindings are different (for example, both may have table tb1, but obviously they're different objects, since they belong to different database), they're still treated as different queries.
It's also possible that two queries were identical except for one or more literal values. By default they're treated as different queries. By "enable literal autoparam", the 2nd query can reuse the plan generated for the 1st query. For example, the query "select * from tb1 where col =1" will be saved internally as "select * from tb1 where col = @@@v0_INT". In this way we can reduce the compilation time, the entries and storage in the statement and procedure cache.
Why wrap up statement in LWP?
Surely we can use a single statement cache that includes everything: statement text, plan, literal form, etc. In ASE, we decide that statement cache should only cache the text, and perform object binding. The procedure cache take care of all plan related info. By doing this functionality separation, we have a clearer framework, and we can do memory allocation in a more consistent way.
When to enable literal parametrization?
For OLTP, queries are often simple and homogeneous. We may consider using this "enable literal autoparam" configuration and reuse previous plans, to save the compilation time and storage.
For Business Warehouse, queries are often more complex and skewed. The plans may be quite different for different parameters after optimization. So it is desired that queries be recompiled optimized for each set of different parameters.
Streamlined dynamic SQL:
The statement cache can also be used to store dynamic SQL statements (prepared statements) converted to LWPs. Because the statement cache is shared among all connections, dynamic SQL statements can be reused across connections
To enable using the statement cache to store dynamic SQL statements, set the "streamlined dynamic SQL" configuration options.