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||Description|
|logging_session_id||UNSIGNED INT||The ID of the logging session during which the query or request occurred.|
|query_id||UNSIGNED BIGINT||A number uniquely identifying the query.|
|statement_id||UNSIGNED BIGINT||A number uniquely identifying a statement in a query.|
|user_object_id||UNSIGNED BIGINT||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||TIMESTAMP||The time at which this query was optimized.|
|cache_size_bytes||UNSIGNED BIGINT||The size, in bytes, of the cache at the time this query was optimized.|
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.
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.
Controls whether 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.
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.
|available_requests||TINYINT||Used internally to compute the level of intra-query parallelism.|
|active_requests||TINYINT||Used internally to compute the level of intra-query parallelism.|
|max_tasks||TINYINT||Used internally to compute the level of intra-query parallelism.|
|used_bypass||TINYINT||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||TINYINT||The estimated cost, in milliseconds.|
|plan_explain||LONG VARCHAR||A text plan representation of this query.|
|plan_xml||LONG VARCHAR||A graphical plan representation of the query (if one was recorded).|
|sql_rewritten||LONG VARCHAR||Text of a query after applying optimizations. A value will only be present in this column if optimization logging is enabled.|
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|