The output of showplan describes the optimized plan for a given query. An optimized query plan specifies the access methods and the degree of parallelism that the optimizer suggests when the query is compiled. At execution time, there may be fewer worker processes available than are required by the optimized query plan. This can occur when:
There are not enough worker processes available for the optimized query plan.
The server-level or session-level limits for the query were reduced after the query was compiled. This can happen with queries executed from within stored procedures.
In these circumstances, Adaptive Server may create an adjusted query plan to compensate for the available worker processes. An adjusted query plan is generated at runtime and compensates for the lack of available worker processes. An adjusted query plan may use fewer worker processes than the optimized query plan, and it may use a serial access method instead of a parallel method for one or more of the tables.
The response time of an adjusted query plan may be significantly longer than its optimized counterpart. Adaptive Server provides:
A set option, process_limit_action, which allows you to control whether runtime adjustments are allowed.
Information on runtime adjustments in sp_sysmon output.
