sa_diagnostic_query table

The sa_diagnostic_query table is owned by the dbo user, and stores optimization information for queries, especially the context in which they were optimized. A row in this table represents an invocation of the optimizer for a query. Plans captured at optimization time are stored here.

Some of the values in this table mirror database option values.

There are two versions of this table: sa_diagnostic_query, and sa_tmp_diagnostic_query.

Columns
Column name Column type Column constraint Table constraints
logging_session_id UNSIGNED INT NOT NULL Primary key. Foreign key references sa_diagnostic_statement
query_id UNSIGNED BIGINT NOT NULL Primary key. Foreign key references sa_diagnostic_statement.
statement_id UNSIGNED BIGINT NOT NULL
user_object_id UNSIGNED BIGINT NOT NULL
start_time TIMESTAMP NOT NULL
cache_size_bytes UNSIGNED BIGINT
optimization_goal TINYINT
optimization_level TINYINT
user_estimates TINYINT
optimization_workload TINYINT
available_requests TINYINT
active_requests TINYINT
max_tasks TINYINT
used_bypass TINYINT
estimated_cost_ms TINYINT
plan_explain LONG VARCHAR
plan_xml LONG VARCHAR
sql_rewritten LONG VARCHAR

logging_session_id   The ID of the logging session during which the query or request occurred.

query_id   A number uniquely identifying the query.

statement_id   A number uniquely identifying a statement in a query.

user_object_id   The object ID of the user under which this query was executed. If the query was run from a procedure, this would be the user ID of the procedure owner.

start_time   The time at which this query was optimized.

cache_size_bytes   The size, in bytes, of the cache at the time this query was optimized.

optimization_goal   Determines whether query processing is optimized towards returning the first row quickly, or minimizing the cost of returning the complete result set. This value reflects the value of the optimization_goal database option.

To see possible values for this column, see optimization_goal option [database].

optimization_level   Controls the amount of effort made by the SQL Anywhere query optimizer to find an access plan for a SQL statement. This value reflects the value of the optimization_level database option.

To see possible values for this column, see optimization_level option [database].

user_estimates   Controls whether or not user selectivity estimates in query predicates are respected or ignored by the query optimizer. This value reflects the value of the user_estimates database option.

To see possible values for this column, see user_estimates option [database].

optimization_workload   Determines whether query processing is optimized towards a workload that is a mix of updates and reads or a workload that is predominantly read-based. This value reflects the value of the optimization_workload database option.

To see possible values for this column, see optimization_workload option [database].

available_requests   Used internally to compute the level of intra-query parallelism.

active_requests   Used internally to compute the level of intra-query parallelism.

max_tasks   Used internally to compute the level of intra-query parallelism.

used_bypass   Whether a simple query bypass was used. A value of 1 indicates a bypass was used; a value of 0 indicates that the query was fully optimized.

estimated_cost_ms   The estimated cost, in milliseconds.

plan_explain   A text plan representation of this query.

plan_xml   A graphical plan representation of the query (if one was recorded).

sql_rewritten   Text of a query after applying optimizations. A value will only be present in this column if optimization logging is enabled.

See also