Adaptive Server can generate an abstract plan for a query, and save the text and its associated abstract plan in the sysqueryplans system table. Using a rapid hashing method, incoming SQL queries can be compared to saved query text, and if a match is found, the corresponding saved abstract plan is used to execute the query.
An abstract plan describes the execution plan for a query using a language created for that purpose. This language contains operators to specify the choices and actions that can be generated by the optimizer. For example, to specify an index scan on the titles table, using the index title_id_ix, the abstract plan says:
( i_scan title_id_ix titles)
Abstract plans provide a means for System Administrators and performance tuners to protect the overall performance of a server from changes to query plans. Changes in query plans can arise due to:
Adaptive Server software upgrades that affect optimizer choices and query plans
New Adaptive Server features that change query plans
Changing tuning options such as the parallel degree, table partitioning, or indexing
The major purpose of abstract plans is to provide a means to capture query plans before and after major system changes. The sets of before-and-after query plans can be compared to determine the effects of changes on your queries. Other uses include:
Searching for specific types of plans, such as table scans or reformatting
Searching for plans that use particular indexes
Specifying full or partial plans for poorly-performing queries
Saving plans for queries with long optimization times
Abstract plans provide an alternative to options that must be specified in the batch or query in order to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without having to modify the statement syntax. While matching query text to stored text requires some processing overhead, using a saved plan reduces query optimization overhead.
