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.

Identifying query execution issues

You can display database options and other global settings that affect query execution for the root operator node.

Reviewing selectivity performance

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 bypass query. For more information about bypass queries, see How the optimizer works, and Explicit selectivity estimates.

Indicators of poor selectivity occur in the following places:

Reviewing cache performance

If the number of cache reads (CacheRead field) and cache hits (CacheHits field) are the same, then all 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.

Identifying ineffective indexes

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.

Identifying data fragmentation problems

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. So, 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 Execution plan abbreviations.