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 bypass query.

Indicators of poor selectivity occur in the following places:

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.

It is often not obvious from query execution plans whether indexes help improve performance. Some of the scan-based query operations used in SQL Anywhere provide excellent performance for many queries without using indexes.

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.

• REORGANIZE TABLE statement
• CREATE STATISTICS statement
• How the optimizer works
• Explicit selectivity estimates
• Reduce table fragmentation
• Execution plan components
• Selectivity information in the graphical plan
• Tip: Use indexes effectively
• Index Consultant
• Tip: Use the cache to improve performance