Optimization Method

The algorithm used to choose an execution strategy. Values returned:

• Bypass costed
• Bypassed costed simple
• Bypass heuristic
• Bypassed then optimized
• Optimized
• Reused
• Reused (simple)

Costed Best Plans

When the query optimizer enumerates different query execution strategies, it tracks the number of times it finds a strategy whose estimated cost is cheaper than the best strategy found prior to the current one. It is difficult to predict how often this will occur for any particular query, but a lower number indicates significant pruning of the search space by the optimizer's algorithms, and, typically, faster optimization times. Since the optimizer starts the enumeration process at least once for each query block in the given statement, Costed Best Plans represents the cumulative count. See How the optimizer works.

If the values for Costed Best Plans, Costed Plans, and Optimization time are 0, then the statement was not optimized by the SQL Anywhere optimizer. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement, or the plan for the statement was cached. See Query processing phases.

Costed Plans

The number of different access plans considered by the optimizer for this request whose costs were partially or fully estimated. As with Costed Best Plans, smaller values normally indicate faster optimization times and larger values indicate more complex SQL queries.

If the values for Costed Best Plans, Costed Plans, and Optimization Time are 0, then the statement was not optimized. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement. See Query processing phases.

Optimization Time

The elapsed time spent optimizing the statement.

If the values for Costed Best Plans, Costed Plans, and Optimization Time are 0, then the statement was not optimized. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement. See Query processing phases.

Estimated Cache Pages

Estimated current cache size available for processing the statement.

For the purposes of reducing inefficient access plans, the optimizer assumes that one-half of the current cache size is available for processing the selected statement.

CurrentCacheSize

The database server's cache size in kilobytes at the time of optimization.

QueryMemMaxUseful

The number of pages of query memory that are useful for this request. If the number is zero, then the statement's execution plan contains no memory-intensive operators and is not subject to control by the server's memory governor. See The memory governor.

QueryMemNeedsGrant

Indicates whether the memory governor must grant memory to one or more memory-intensive query execution operators that are present in this request's execution strategy. See The memory governor.

QueryMemLikelyGrant

The estimated number of pages from the query memory pool that would be granted to this statement if it were executed immediately. This estimate can vary depending on the number of memory-intensive operators in the plan, the database server's multiprogramming level, and the number of concurrently-executing memory-intensive requests. See The memory governor.

QueryMemPages

The total amount of memory in the query memory pool that is available for memory-intensive query execution algorithms for all connections, expressed as a number of pages. See The memory governor.

QueryMemActiveMax

The maximum number of tasks that can actively use query memory at any particular time. See The memory governor.

QueryMemActiveEst

The database server's estimate of the steady state average of the number of tasks actively using query memory. See The memory governor.

isolation_level

The isolation level of the statement. The isolation level of the statement may differ from other statements in the same transaction, and may be further overridden for specific base tables through the use of hints in the FROM clause. See isolation_level option [database] [compatibility].

optimization_goal

Indicates if query processing is optimized for returning the first row quickly, or minimizing the cost of returning the complete result set. See optimization_goal option [database].

optimization_level

Controls amount of effort made by the query optimizer to find an access plan. See optimization_level option [database].

optimization_workload

The Mixed or OLAP value of the optimization_workload setting. See optimization_workload option [database].

max_query_tasks

Maximum number of tasks that may be used by a parallel execution plan for a single query. See max_query_tasks option [database].

user_estimates

Controls whether to respect or ignore user estimates that are specified in individual predicates in the query text. See user_estimates option [database].