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.
| 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.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |