Abstract query plans allow customers to modify queries to use a specific plan without altering the query code. The steps for using abstract query plans, which are transparent to applications, are:
Identify the queries that have problems in the 15.0 server.
Enable abstract query plans to capture query plans in the earlier version of the server and run the queries that pose problem with the 15.0 server.
Extract the abstract query plans from the ap_stdout group.
Review and modify the abstract queries to adjust for partial plan use or other issues that may be affecting the query’s performance.
In the 15.0 server, use create plan to load the query plans into the default ap_stdin group in the 15.0 server.
Enable abstract plan load on the 15.0 server.
Disable abstract plan cache on the 15.0 server to avoid IO during optimization.
Re-run the queries in the 15.0 server to determine if the abstract query plans are used.
As Adaptive Server introduces new features that improve query performance, you may need to adjust the abstract query plans.
Work with Sybase Technical Support to resolve any original optimization issue.
For more information about abstract query plans, see the Performance and Tuning Guide:Optimizer and Abstract Plans.
These steps are useful to resolve problems that delay an upgrade beyond the window of opportunity for diagnosing the problem with the query, rewriting the query, and resolving any issues that occur. You can use abstract plans to replace set forceplan on, which allows you more control than the join-order processing enforcement that forceplan implements, which is illustrated in the following examples.
This example scans the lineitem table without an index.
select count(*) from orders, lineitem where o_orderkey = l_orderkey
QUERY PLAN FOR STATEMENT 1 (at line 1). 3 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |NESTED LOOP JOIN Operator (Join Type: Inner Join) | | |SCAN Operator | | FROM TABLE | | orders | | Table Scan. | | Forward Scan. | | Positioning at start of table. | |SCAN Operator | | FROM TABLE | | lineitem | | Table Scan. | | Forward Scan. | | Positioning at start of table.
This method may not create the best available query plan, and may run faster if you use the l_idx1 index on lineitem. Try rewriting the query to force the index:
select count(*) from orders, lineitem (index l_idx1) where o_orderkey = l_orderkey
QUERY PLAN FOR STATEMENT 1 (at line 1). 3 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |NESTED LOOP JOIN Operator (Join Type: Inner Join) | | |SCAN Operator | | FROM TABLE | | orders | | Table Scan. | | Forward Scan. | | Positioning at start of table. | | |SCAN Operator | | FROM TABLE | | lineitems | | Index : l_idx1 | | Forward Scan. | | Positioning by key. | | Keys are: | | l_orderkey ASC
Although using the force parameter often solves query plan issues, it requires that you change the application code. Even if changing the code is not a problem for you, this can take much longer than using an abstract query plans.
The following examples run the same query as above, but uses abstract plans to improve them.
First, enable abstract plans:
set option show_abstract_plan on go dbcc traceon(3604) go
Adaptive Server generates the abstract query plans, which you edit and then force to use an index.
select count(*) from orders, lineitem where o_orderkey = l_orderkey go The Abstract Plan (AP) of the final query execution plan: ( nl_join ( t_scan orders ) ( t_scan lineitem ) ) ( prop orders ( parallel 1 ) ( prefetch 2 ) (lru ) ) ( prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) )
You can modify the abstract plan to change the query processor’s behavior, and pass the query plan to the query processor using the PLAN clause. The syntax is:
SELECT/INSERT/DELETE/UPDATE ...PLAN '( ... )
To improve the plan, replace the table scans (t_scan)
with index accesses and specify the tables using (prop table_name) (the
example is indented for display purposes):
select count(*) from orders, lineitem where o_orderkey = l_orderkey plan "( nl_join ( t_scan orders ) ( t_scan lineitem ) ) ( prop orders ( parallel 1 ) ( prefetch 2 ) (lru ) ) ( prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) )
To force the index scan, use (i_scan index_name table_name) parameter:
1> select count(*) from orders, lineitem where o_orderkey = l_orderkey plan "( nl_join ( t_scan orders ) ( i_scan l_idx1 lineitem ) ) ( prop orders ( parallel 1 ) ( prefetch 2 ) (lru ) ) ( prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) )
