Tuesday, January 15, 2013

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.


Anonymous Anonymous said...

Hi Matt,

Thank you for the blog page on Sybase ASE Statement Cache (SC), Proc Cache (PC), LWPs, and Streamline Dynamic SQL (SDS). I have some questions:- (1) Enabling the new function in 15.7, SDS, turns the nice framework of keeping SQL texts in SC and LWPs in PC separately on its head for the benefit of sharing LWPs across user connections - what is the draw-back of this deviation from keeping SC and PC usage separate? (2) SC storage is sourced from PC and PC entities are managed by a single MRU/LRU chain - (2a) when searching for LWPs of matched cached statements, access to the single PC chain is subjected to serialisation (by spinlock) and (2b) new cached statements need free PC space to stored their SQL texts since SC co-locates with PC in shared memory, SC free space is allocated from PC's single chain - what is the implication of serialised access contention due to (2a) and (2b) on regular query plan handling? - (3) Are all the elements associated with cached statements, LWPs, SDSs etc. kept entirely in shared memory or do any of the elements written to disk? (4) Do all the cached statement, LWP, SDS objects find home in both Engine Local/Global Cache? Are they exclusively found in EGC only and are subjected to serialised access control? (5) Do all such objects stay in memory? When do they get flushed when Streamline Dynamic SQL is enabled?

7/11/2013 12:40 PM  
Blogger Qing (Matt) Zhang said...

Thank you for the comment! Let me try answering your questions (but please understand I cannot give you too much design details.)
(1) I don't quite get your question. For SDS, SC and PC are for still separate usage (on shared memory pool though). It's just that in previuos design, we don't share dynamic sql plan across different connections, and now we do. The sql text is in SQT cache, and plan in Procedure cache. So still separate usage.
(2) There's no fixed boundary between SC and PC, and we have management modules doing the synchroinzation for the allocation.
(3) They are assigned in memory pool as cache info. You don't write cached info back to disk.
(4) Each engine may ask for some cache blocks from the shared memory pool assigned for it exclusively.
(5) The cache block is recollected from the LRU chain when no free memory is available for caching new objects, and no one is using it.

7/19/2013 2:52 PM  
Anonymous Anonymous said...

What about set statements inside a SP. Like changing isolation level using a set command. Would that force a recompile?

10/23/2013 6:26 AM  
Anonymous Anonymous said...

Thank you for you reply, Matt. I would like to expand on my question & your answer in point (1):-
(1) I don't quite get your question. For SDS, SC and PC are for still separate usage (on shared memory pool though). It's just that in previuos design, we don't share dynamic sql plan across different connections, and now we do. The sql text is in SQT cache, and plan in Procedure cache. So still separate usage.

What about the LWPs of the cached statements? Before SDS feature was introduced and when it is turned off in 15.7, they are not sharable since they are not kept in SC. The new SDS feature is to allow the LWPs to be sharable between client connects as they are now kept in SC.

11/10/2013 2:10 PM  
Anonymous Anonymous said...

Hi Matt,

According to Sybase's 2012 SDS patent, there are 2 LWP elements i.e. LWP object for holding cached statement SQL texts and LWP query tree (or plan). I guess LWP query tree must reside in PC. I am under the impression that LWP sql text object resides in dynamic sql cache associated with a client connect unless SDS is enabled, then, it resides in SC for sharing & re-use between connection. Do I understand this correctly? If so, SDS would increase contention for free buffers with high influx of incoming statements. Should DBA increase PC and SC sizes when SDS feature is enabled as a result?

11/10/2013 2:29 PM  
Anonymous Anonymous said...

Hi Matt,

Do you have any comment on my previous question whether DBAs should increase the PC & SC sizes?

From my experience, both PC and SC sizes must be adjusted to achieve the balance of:-
(A) having sufficient spare PC buffers at peak workload windows i.e. PC configured size minus PC utilisation High Water Mark (HWM) should be around 20-25% to avoid thrashing / conflict between allocation & deallocation of objects in PC and SC causing high PC spinlock contention --> extremely high CPU usage
(B) having sufficient descriptors for the LWPs, more importantly, avoiding high influx of used-once-and-discard cached statements causing high cached statement spinlock contention --> moderately high CPU usage

4/04/2014 7:44 AM  
Blogger Qing (Matt) Zhang said...

Sorry I left Sybase/SAP last Oct. and was busy with my work with a startup company, and didn't get time to review the comments in details.

In general it would be desirable to have more SC/PC if you have the resource. In this case you may save the cost of recompile, which is the major cost.

4/13/2014 3:10 AM  

Post a Comment

Links to this post:

Create a Link

<< Home