Sybase SQL性能诊断

Performance and Tuning Series: Query Processing and Abstract Plans
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1570/html/queryprocessing/title.htm

Query plan/showplan output description
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20022_1251/html/monitoring/X85682.htm


1) set showplan on;
执行sql语句并且显示query plan

2) set showplan on;
   set noexec on;
不执行sql语句，只显示sql语句的query plan

3)对于存储过程，使用set fmtonly on

4) set showplan on;
   set noexec off;
   set statistics plancost on;
   会执行sql语句，打印出query plan,同时打印出query plan的图形表示。

有关set statistics plancost on详细信息，请参见《Adaptive Server 参考手册：命令》。此命令用于比较查询计划的估计开销和实际开销。它将其输出显示为表示查询计划树的半图形化的树。这是一种诊断查询性能问题的非常有用的工具。

Also "set statistics io on" is very useful

5. 其他相关set命令
set statistics {io, subquerycache, time, plancost, simulate} {on | off}
显示各种类型的统计信息
• io — 显示有关语句中引用的每个表的统计信息：
• 访问表的次数（扫描计数）
• 逻辑读取的数目（内存中访问的页）
• 物理读取次数（数据库设备访问）
statistics io 为每个命令显示写入的缓冲区数目。
如果 Adaptive Server 已配置为强制资源限制，则 statistics io 还显示
总的 I/O 开销。
• subquerycache — 为每个子查询显示子查询高速缓存中的高速缓存
命中次数、未命中次数和行数。
• time — 显示 Adaptive Server 分析和编译每个命令所用的时间。在
命令的每一个步骤， statistics time 显示 Adaptive Server 执行此命
令所需的时间。时间以毫秒或时钟周期为单位指定，其确切值与
计算机有关。
• plancost — 以树格式显示查询统计信息。
注释　启用 set statistics plancost 后，Adaptive Server 将 lio、pio
和 row 的名称分别缩写为 l、p 和 r。
• simulate — 指定优化程序应使用模拟统计信息来优化查询。
请参见《性能和调优指南》中的第 34 章“使用 set statistics 命令”。


Understanding the Information You Have Gathered
http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.39996_1250/html/svrtsg/svrtsg170.htm
You have taken a number of steps to get information about your optimizer problem. Here is an explanation of each of these steps:


select @@version
select @@version displays the version of Adaptive Server you are running, including the SWR level and platform.


sp_help
sp_help provides more accurate information about a table than the script you used to create the table and its indexes. In the event that indexes have been added or changed or that columns have been added via alter table, sp_help will show the present state of the table(s).


set showplan on
The set showplan on command shows which query plan the optimizer has chosen for your query. Use set showplan on before running any query or procedure you will be analyzing.

In some cases you may need to issue the set noexec on command to save time when you are running a very long query. The order of these commands is important:

set showplan on
set noexec on
go
<query text...>
go
There are several important items of information to look for when reading showplan output:

Cache Utilization

Adaptive Server uses two major strategies, named LRU and MRU respectively, for its data cache. The type of strategy used in a given query depends on whether a cached page needs to be accessed more than once. showplan’s "Buffer Replacement Strategy" messages show the cache strategy used for data pages and index leaf pages. See “Caches and Object Bindings” in the Performance and Tuning Guide for more information about cache strategies.

If you want to investigate your caches, for example to learn whether a cache is under- or over-utilized, you can use sp_sysmon. See “Data Cache Management” in the Performance and Tuning Guide.

Index Utilization

Was an index used? Which one? Was a table scan done? To answer these questions, check the portion of showplan output following FROM TABLE for messages like "Table Scan" or "Using Clustered Index".

Join Information

When evaluating joins, look for

the order of tables in a join, also known as join order; knowing the order that the optimizer chose for joins is critical to your analysis. When your query joins two or more tables, showplan’s FROM TABLE messages show the order in which the optimizer will join the tables.

Whether it is a nested-loop join or a sort-merge join (applies to 12.0 and later).

Refer to “Using set showplan” in the Performance and Tuning Guide for more information on interpreting showplan results.


set statistics io on
Since any analysis of a performance problem will require knowledge of the number and types of I/Os performed for the query, the set statistics io on command is critical.

NoteIf your query is taking very long to complete, using statistics io and statistics time may not be feasible. If you analyze your long-running query using set noexec on, you cannot obtain I/O information since noexec on stops all the output of statistics io.

The set statistics io on command provides you with the following information:

Physical reads

This is the number of times Adaptive Server accesses the disk. The first time a query is run, the number of physical reads will generally be high. This happens because the required pages are not usually in cache. Subsequent runs of the query can access the pages in cache, and physical reads are minimized, if not avoided. If the number of physical reads remains high during subsequent executions of a query, you will need to take a close look at how the query executes.

In some instances, the size of the data cache may also be a problem. If it is too small, pages have to be read from disk more often. Likewise, configuration of named caches and use of large I/O buffer pools can have an impact on performance. See “Memory Use and Performance” in the Performance and Tuning Guide for details on configuring the data cache to improve performance.

Logical reads

Logical reads are a combination of physical reads and "cache hits" - reads from pages in cache. If your statistics show a number of logical reads and no physical reads, it means that all required pages are in cache, which is the ideal situation. To determine the cache hit ratio (the percentage of pages that were found in cache) for your query, use the formula:

                  Logical reads - (Physical reads * Pages per I/O)
Cache hit ratio = -----------------------------------------------
                                     Logical reads
Use set showplan on to see the I/O size used by the query. With 2K pages, a query using 4K I/O reads 2 pages with each I/O.

Scan count

This is the number of times the table was read (using either a table scan or an index) in order to find rows to satisfy the query or join. In nearly all simple single table queries, the scan count will be 1. When an OR clause is present there will be one scan count for each OR in the query. In the case of a join, the scan count can be crucial.

If the optimizer chose a bad join order, you are likely to see a very high number of scan counts on a large table, causing a very high number of logical reads. However, you should take the table size into account when interpreting scan counts. A high scan count on a small table is preferable to a moderate scan count on a large table. Although the scan count of the small table is high, the physical reads should be low. A 1000-scan count for a 1-page table is better than a 100-scan count of a 1000-page table.

The following example demonstrates how join order and scan count affect the number of reads (on 12.0 and later, the example represents a nested-loop join):

Table A has 1 page and 10 rows that qualify for the join. Table B has 1000 pages and 10 rows that qualify for the join.

If Table B is the outer table of the join Adaptive Server will only need to read through it once in order to find all qualifying rows. The single scan totals 1000 reads. Adaptive Server then reads Table A for each qualifying row found in B. The single page in A is scanned 10 times, equaling 10 reads, with a total of 1010 reads for the query. If A were the outer table Adaptive Server would have to read B once for each of the ten qualifying rows on A: 1000 pages multiplied by 10 scans equals 10,000 reads.

This example assumes that there is no useful index available.

Total writes for this command

This is the total number of writes Adaptive Server did for the query. This count includes inserts, updates and deletes on user tables, temporary tables and work tables. Even queries that do not include data manipulation statements may require writes to work tables or temporary tables, which are counted here.


set statistics time on
set statistics time on provides the following information:

Adaptive Server elapsed time

This is the total accumulated elapsed time that is recorded for the query or command. This can seem long if, for example, a query was blocked by a lock, network traffic or other resource contention. The time the query must wait for the blockage to clear is added to the elapsed time.

Adaptive Server CPU time

This is the amount of time for which the query had exclusive use of the CPU. It reflects the time taken to parse, compile, and execute the query. Functions add to the CPU time. For example, a convert statement will increase the CPU time slightly. Also, compute-intensive queries and queries that perform a large amount of I/O take more CPU time.

The output of set statistics time on may be useful, but it is not usually a significant factor in most optimizer analyses.


dbcc traceon (3604)
This trace flag sends the output of dbcc traceon (302) and dbcc traceon (310) to the screen.


dbcc traceon (302)
This trace flag returns the optimizer's cost estimates for each SARG and join clause in the query. Trace flag 302 is documented in greater detail in “Tuning with dbcc traceon” in the Performance and Tuning Guide.

Here is the information to watch for in dbcc traceon (302) output:

All SARGs and join clauses in the query should be shown in the optimizer's cost estimates. If not, determine why.

Check that row and page counts are accurate, since these counts are important for optimization.

If you think that the page and row counts are off, check the counts. Run optdiag statistics. To improve performance, counts and other statistics are changed in memory and flushed to systabstats periodically by the housekeeper task. You can also flush in-memory statistics to systabstats by running optdiag statistics or executing sp_flushstats table_name.


dbcc traceon (310)
dbcc traceon (310) gives the optimizer cost estimates for permutations of a join or joins. Examine the dbcc traceon (310) output to determine whether the query is “connected.” If so, it indicates that the join will not result in a cartesian product. The statement “query is connected” will appear after the optimizer has performed cost estimates on all possible indexes, as indicated in the output of dbcc traceon (302) .


IO statistics output explanation
below is one row of the output when set statistics io on for q query
Table: Worktable1 scan count 1, logical reads: (regular=6391 apf=0 total=6391), physical reads: (regular=7919 apf=0 total=7919), apf IOs used=0


http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20022_1251/html/monitoring/monitoring99.htm
Statistics for reads
statistics io reports the number of logical and physical reads for each table and index included in a query, including worktables. I/O for indexes is included with the I/O for the table.


Table 4-1 shows the values that statistics io reports for logical and physical reads.

Table 4-1: statistics io output for reads
logical reads
regular
Number of times that a page needed by the query was found in cache; only pages not brought in by asynchronous prefetch (APF) are counted here.

apf
Number of times that a request brought in by an APF request was found in cache.

total
Sum of regular and apf logical reads.


physical reads
regular
Number of times a buffer was brought into cache by regular asynchronous I/O

apf
Number of times that a buffer w.as brought into cache by APF.

total
Sum of regular and apf physical reads.

apf IOs used
Number of buffers brought in by APF in which one or more pages were used during the query.