Thursday, September 8, 2011

Where is Query Plan stored in Sybase?

Adaptive Server Enterprise or Sybase ASE before processing and executing a query prepares a query plan for each of the user submitted query and then actually executes the query based on the query plan. This is a pretty simple concept, however what is interesting to know here is - Where is the Query Plan stored in Sybase?

Some people might say that the query plan is saved in system tables however that is not completely true. The reason is that the query is the final blue print over which the Sybase server tries to execute the query based on a parse tree. Now, someone might ask out of curiosity that what is a parsed tree then? When a user submits a query in Sybase, the query is first checked syntactically and then a parse tree is prepared for the query execution and the object id's are mapped to the parse tree. While executing the query after preparing the parse tree Sybase server prepares many sets of query plans and estimates its costs. Once the query plan with minimum cost is created the Sybase server selects this query plan and uses it. This query plan exists in the procedure cache of the Sybase server which is recreated and flushed on each server restart. So, if our Sybase server shuts down and restarted again a new query plan will be created on the next execution of the stored procedure from the stored parse tree.

No comments:

Post a Comment