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)
To use this abstract plan with a query, you can modify the
query text and add a PLAN clause:
select * from titles where title_id = “On Liberty” plan “(i_scan title_id_ix titles)”
This alternative requires a change to the SQL text; however, the method described in the first paragraph, that is, the sysqueryplans-based way to give the abstract plan of a query, does not involve changing the query text.
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 main purpose of abstract plans is to provide a means to capture query plans before and after major system changes. You can then compare sets of before-and-after query plans 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 to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without modifing the statement syntax. While matching query text to stored text requires some processing overhead, using a saved plan reduces query optimization overhead.
