INFORMIX系统效能改善第一步

 

作者 傅凯文,谭永贻/库柏信息   

 

本文介绍 IBM Informix® Dynamic Server 数据库效能改善中最基础之手段 UPDATE STATISTICS 以及其主要功能与执行时机。

简介

在 IBM Informix® Dynamic Server (以下简称 IDS) 系统上，SQL 执行效能系于 IDS optimizer 所决定的执行计划 (SQL execution plan)；而影响执行计划的决定的两个重要参考数据为统计数据(statistics) 和资料分布 (distributions)。如果说这两个参考数据和实际数据情况有太大的出入，Informix optimizer 可能会做出低效率的执行路径 (execution path)，进而促使 SQL 执行效能不彰。 UPDATE STATISTICS 是更新这两个数据的必要手段。

适当执行 UPDATE STATISTICS 之后的 IDS optimizer 做出的最高效率的执行计划而使 SQL 在 IDS 上执行更有效率。

IDS UPDATE STATISTICS 指令在 SQL 执行计划有两个主要的功能：

1. 更新在系统型录 (system catalogs) 统计数据 optimizer 以此决定执行计划。

2. 更新资料分布情形。 IDS optimizer 以此决定执行计划最有效路径 (lowest cost)。 

UPDATE STATISTICS 指令使用时机

前面提到， IDS optimizer 在决定执行计划时，会参考统计数据和数据分布。统计数据从统计数据和数据分布并不是实时的，只有在执行 UPDATE STATISTICS 时才会更新，也就是说这些信息绝大部份的时候会和实际情况有所出入。至于什么情况下该使用这个指令呢?

1. 资料数量 (number of rows) 有大量变化情形。

2. IDS 进行数据转移 (migration) 之后。 

执行 UPDATE STATISTICS 的执行选择

假使数据库有 1000+ 表格 (table) ，为所有的 1000+ 表格执行 UPDATE STATISTICS 将耗用太多系统资源与时间，有实际执行上之困难。所以如何选择需要被执行 UPDATE STATISTICS 之关键表格 (table) 才是执行 UPDATE STATISTICS 的关键决定。下列来自库柏信息 DBSonar 所提供之报表中，能帮你找到最常用以及最需要被执行 UPDATE STATISTICS 之关键表格，其范例如下： 

DB	Table	Nrows in Update Statistics	Accurated NRows	Change Rate %	N Read	N Write	Seqscan
ERP	Table1	0	41398	100.0	205815	70238	8
ERP	Table2	1	1859	99.9	37449384	6206787	810
ERP	Table3	400000	18644384	97.9	115655415	17922233	0
ERP	Table4	246	4777	94.8	311858759	127744455	20102

以上 Table1 与 Table3 因其 Nrows in Update Statistics 与 Accurated NRows 差异甚大，是亟待被执行 UPDATE STATISTICS 之表格。譬如 Table3 是一个被大量读取 (115,655,415 次) 与写入 (17,922,233 次) 的表格， IDS 系统型录认为 Table3 只有 400,000 列 (rows) 而事实上 Table3 已有 18,644,384 列， DBA 执行下列指令以更新在系统型录统计数据。

UPDATE STATISTICS LOW FOR TABLE table3;

UPDATE STATISTICS 的等级

这个指令可以分成三种等级 (level)： LOW, MEDIUM 和 HIGH 。这三种等级的区别就是执行 UPDATE STATISTICS 时所收集的数据取样的多寡。所谓的 HIGH level ，就是统计整个 table 的资料， MEDIUM 则是只随机选取部份的资料进行统计。取样的数据越多，透过统计数据和数据分布做出来的执行计划才会越精准越有效率；不过相对的，统计和计算取样的时间成本也会比较高。 UPDATE STATISTICS 的语法如下：

 

UPDATE STATISTICS [LOW|MEDIUM|HIGH]; 

UPDATE STATISTICS [LOW|MEDIUM|HIGH] FOR TABLE; 

UPDATE STATISTICS [LOW|MEDIUM|HIGH] FOR TABLE table_name; 

UPDATE STATISTICS [LOW|MEDIUM|HIGH] FOR TABLE table_name(col_name); 

UPDATE STATISTICS [LOW|MEDIUM|HIGH] DISTRIBUTIONS ONLY; 

UPDATE STATISTICS FOR PROCEDURE procedure_name; 

DISTRIBUTIONS ONLY 是指更新数据分布。只要没有加上 DISTRIBUTIONS ONLY 参数，都会产生统计资料；然而只有在 level MEDIUM 或是 HIGH 才会产生数据分布。 IDS optimizer 既然会采用这两个数据做为选择执行计划的参考依据，我们可以透过一些小技巧，让 optimizer 去选择我们希望它选择的执行计划。例如说，针对 table 所有的字段执行 UPDATE STATISTICS LOW ；但是针对 query request 中 WHERE 条件有使用到的字段执行 UPDATE STATISTICS MEDIUM ；对 index 所使用到的第一个字段执行 UPDATE STATISTICS HIGH 。有了这样不同等级的区别， optimizer 选择走 index 的执行计划的机会就会相对提高了。 UPDATE STATISTICS 执行后统计数据和数据分布是会不断覆盖过去的，所以同一个 table 有执行一种以上 level 的 UPDATE STATISTICS 时，切记先从 LOW, MEDIUM, HIGH 的顺序执行，以免 MEDIUM 或 HIGH 的数据被 LOW 所覆盖。 

结论

在数据库系统中，最主要的工作就是执行 client 端送来的 SQL ；而 optimizer 就身负决定执行 SQL 效能的关键角色。为了让 IDS optimizer 做出最有效率的执行计划，定期执行 UPDATE STATISTICS 就是数据库管理员最重要的职责之一。执行相关细节和执行前后效果差异，将来会在后续的专栏中继续讨论。 

参考数据

- Understanding and Tuning Update Statistics