1. 统计信息的重要性
sybase使用基于成本的Query优化器, 它使用表，index，columns的统计信息来估计Query的成本，选择最佳方法。如果统计信息有误或者不准确，就可能导致SQL执行低效。有些统计信息在执行update，insert时就会更新，但是有些需要执行update statistics命令时才会执行。如果遇到性能问题，并且SQL查询计划看上去正确的时候，头一个要问的问题就是统计信息是否准确，是否需要update statistics.我们可以使用optdiag命令来检查 上传执行update statistics的时间。另一个常用的命令是 delete statistics， 删除一个index时，如果没有删除这个index相关的统计信息。如果key的存储与删掉的index有关，那么这个不准确的统计信息就导致一定的性能问题。2. 存储统计信息的表: systabstats 和 sysstatistics 表
systabstats 作为对象存储表或索引的有关信息, 即大小、行数等等。 通过查询处理、数据定义语言和 update statistics 命令可对其进行更新。sysstatistics 将与这些值有关的信息存储在特定列中。optdiag 实用程序显示 systabstats 和 sysstatistics 表的统计信息。optdiag 也可用于更新 sysstatistics 的信息。只有系统管理员可以运行 optdiag。optdiag [binary] [simulate] statistics{-i input_file |database[.owner[.[table[.column]]]][-o output_file]}[-U username] [-P password][-I interfaces_file][-S server][-v] [-h] [-s] [-Tflag_value][-z language] [-J client_charset][-a display_charset]3. optdiag 查询表\索引的统计信息
OptDiag/15.5/EBF 17218 SMP/P/NT (IX86)/Windows 2003/ase155/2391/32-bit/OPT/Mon Nov 09 14:32:38 2009Adaptive Server Enterprise/15.5/EBF 17218 SMP/P/NT (IX86)/Windows 2003/ase155/2391/32-bit/OPT/Mon Nov 09 14:18:14 2009Server name: "CNNBHZ1055"Specified database: "re"Specified table owner: not specifiedSpecified table: "inv_ref"Specified column: not specifiedTable owner: "dbo"Table name: "inv_ref"###########################################################OUTPUT FROM ASE12.5, NOT ASE15.5#########################################################Table owner: "dbo"Statistics for table: "titles"Data page count: 662Empty data page count: 10Data row count: 4986.0000000000000000Forwarded row count: 18.0000000000000000Deleted row count: 87.0000000000000000Data page CR count: 86.0000000000000000OAM + allocation page count: 5First extent data pages: 3Data row size: 238.8634175691937287Derived statistics:Data page cluster ratio: 0.9896907216494846###################################################Statistics for index: "inv_ref_Key" (clustered)Index column list: "inv_ref_id", "owner_id" Data page count: 2111 Empty data page count: 0 Data row count: 35360.0000000000000000 Forwarded row count: 0.0000000000000000 Deleted row count: 0.0000000000000000 Data page CR count: 264.0000000000000000 OAM + allocation page count: 68 First extent leaf pages: 0 Data row size: 114.7445984162895900 Index height: 2 Parallel join degree: 0.0000000000000000 Unused page count: 8 OAM page count: 1 Derived statistics: Data page cluster ratio: 1.0000000000000000 Space utilization: 0.9533793601918899 Large I/O efficiency: 1.0000000000000000Statistics for index: "inv_ref_idx1" (nonclustered)Index column list: "search_name" Leaf count: 595 Empty leaf page count: 0 Data page CR count: 30147.0000000000000000 Index page CR count: 77.0000000000000000 Data row CR count: 32243.0000000000000000 First extent leaf pages: 0 Leaf row size: 33.5776300904977350 Index height: 2 Derived statistics: Data page cluster ratio: 0.0655430126020201 Index page cluster ratio: 0.9961538461538462 Data row cluster ratio: 0.0937471803663268 Space utilization: 0.9898167600373483 Large I/O efficiency: 0.9961685823754789