Adaptive Server versions 15.7 ESD #2 and later allow you to share query plans, avoiding the need for Adaptive Server to create or recompile query plans that are identical to existing plans. Shared query plans are cloned from primary query plans under concurrent system.
Use sp_configure to enable Adaptive Server to use shared query plans. The syntax is:
sp_configure 'enable plan sharing', 1
enable plan sharing is part of the enable functionality group. See the System Administration Guide, Volume 1 for information about setting parameters that are part of this group.
You should see a performance improvement as Adaptive Server shares query plans instead of reusing or recompiling them. You may see a slight change to procedure cache memory usage as primary query plans are pinned in the cache while Adaptive Server uses their shared query plans.
For a query plan to be shareable:
It must be a lightweight procedure (LWP), either:
For dynamic SQL, (often used for ODBC/JDBC prepared statements), or:
Resulting from the operation of the statement cache, where qualifying statements are transformed into LWPs for reuse
It is a lava query plan
It cannot include instead-of-tiggers
It may include only:
declare, insert, delete, update, merge, or select statements
A single statement, unless the first statement is a declare statement, in which case the query plan may include two statements
Serial query plans
It cannot access plans that reference Java objects
The show_cached_plan_in_xml function describes the sharing of plans under the <planSharing> element.
shareable – plan
may be shared.
notShareable – plan
may not be shared.
primary – primary
plan (clones of which are being shared).
shared – shared
plan (clone of a primary plan).
