You can use the graphical plan with statistics to identify database performance issues. For detailed field descriptions of the graphical plan with statistics, see Node Statistics field descriptions, and Optimizer Statistics field descriptions.
You can display database options and other global settings that affect query execution for the root operator node.
The selectivity of a predicate (conditional expression) is the percentage of rows that satisfy the condition. The estimated selectivity of predicates provides the information on which the optimizer bases its cost estimates. Accurate selectivity estimates are critical for the proper operation of the optimizer. For example, if the optimizer mistakenly estimates a predicate to be highly selective (for example, a selectivity of 5%), but in reality, the predicate is much less selective (for example, 50%), then performance might suffer. Although selectivity estimates might not be precise, a significantly large error might indicate a problem.
If you determine that the selectivity information for a key part of your query is inaccurate, you can use CREATE STATISTICS to generate a new set of statistics for the column(s). In rare cases, you may want to supply explicit selectivity estimates, although this approach can introduce problems when you later update the statistics.
Selectivity statistics are not displayed if the query is determined to be a simple query, so that the optimizer is bypassed. For more information about simple queries, see How the optimizer works, and Explicit selectivity estimates.
Indicators of poor selectivity occur in the following places:
RowsReturned, actual and estimated RowsReturned is the number of rows in the result set. The RowsReturned statistic appears in the table for the root node at the top of the tree. If the estimated row count is significantly different from the actual row count, the selectivity of predicates attached to this node or to the subtree may be incorrect. .
Predicate selectivity, actual and estimated Look for the Predicate subheading to see predicate selectivities. For information about reading the predicate information, see Viewing selectivity in the graphical plan.
If the predicate is over a base column for which there is no histogram, executing a CREATE STATISTICS statement to create a histogram may correct the problem. See CREATE STATISTICS statement.
If selectivity error remains a problem, you may want to consider specifying a user estimate of selectivity along with the predicate in the query text.
Estimate source The source of selectivity estimates is also listed under the Predicate subheading in the Statistics pane.
When the source of a predicate selectivity estimate is Guess, the optimizer has no information to use to determine the filtering characteristics of that predicate, which may indicate a problem (such as a missing histogram). If the estimate source is Index and the selectivity estimate is incorrect, your problem may be that the index is unbalanced; you may benefit from defragmenting the index with the REORGANIZE TABLE statement. See REORGANIZE TABLE statement.
If the number of cache reads (CacheRead field) and cache hits (CacheHits field) are the same, then all of the objects processed for this SQL statement are resident in cache. When cache reads are greater than cache hits, it indicates that the database server is reading table or index pages from disk as they are not already resident in the server's cache. In some circumstances, such as hash joins, this is expected. In other circumstances, such as nested loops joins, a poor cache-hit ratio might indicate there is insufficient cache (buffer pool) to permit the query to execute efficiently. In this situation, you might benefit from increasing the server's cache size.
For more information about cache management, see Increase the cache size.
It is often not obvious from query execution plans whether indexes help improve performance. Some of the scan-based algorithms used in SQL Anywhere provide excellent performance for many queries without using indexes.
For more information about indexes and performance, see Use indexes effectively and Index Consultant.
The Runtime and FirstRowRunTime actual and estimated values are provided in the root node statistics. Only RunTime appears in the Subtree Statistics section if it exists for that node.
The interpretation of RunTime depends on the statistics section in which it appears. In Node Statistics, RunTime is the cumulative time the corresponding operator spent during execution for this node alone. In Subtree Statistics, RunTime represents the total execution time spent for the entire operator subtree immediately beneath this node. Hence, for most operators RunTime and FirstRowRunTime are independent measures that should be separately analyzed.
FirstRowRunTime is the time required to produce the first row of the intermediate result of this node.
If a node's RunTime is greater than expected for a table scan or index scan, you may improve performance by executing the REORGANIZE TABLE statement. You can use the sa_table_fragmentation() and the sa_index_density() system procedures to determine whether the table or index are fragmented.
For more information, see REORGANIZE TABLE statement and Reducing table fragmentation.
For more information about code words used in the plan, see Abbreviations in the plan.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |