理解优化程序

要理解解释程序,您必须首先了解优化程序的工作方式。优化程序是 Oracle 数据库的一个函数,该函数会选择它认为是最有效的方法来执行一条 SQL 语句。这是处理任何数据操作语言 (DML) 语句中的一个关键步骤:SELECT、INSERT、UPDATE 或 DELETE。执行 SQL 语句的方法通常不止一种,如可以变换访问表或索引的顺序。Oracle 执行语句的过程很大程度上会影响语句执行的速度。通过生成解释计划,可以显示优化程序选择的访问路径。

优化程序有四个主要的操作模式:规则、成本 (第一行)、成本 (所有行)和选择所选的模式指导该优化程序的策略:

vtc00090000.gif 规则评估可能的执行路径并基于一系列的语法规则对它们确定等级。

vtc00090000.gif 成本 (第一行) 以最有效检索第一行数据的方式执行该语句。其重点在于检索的速度。

vtc00090000.gif 成本 (所有行) 以对整体性能产生最少影响的方式执行该语句。其重点在于总吞吐量的速度。

vtc00090000.gif 选择在已有表被分析的情况下调用“成本 (所有行)”模式;如果没有表被分析,则调用“基于规则”模式。

可以通过在数据库的 init.ora 文件中指定 OPTIMIZER_MODE 参数来设定一个默认优化程序目标,或者利用一个 ALTER SESSION 命令在会话级中对它进行修改。另外,可以通过向某一特定 SQL 语句中添加提示来为该语句设定优化程序。以下列出了基于规则和基于成本两种方法的区别。

基于规则的方法

如果使用基于规则的方法,则优化程序根据可用访问路径和这些路径的级别来选择一个执行计划。可以利用基于规则的优化方法访问相关数据和对象类型。Oracle 的访问路径等级是启发式的。如果有多于一种的方法执行一条 SQL 语句,那么基于规则的方法通常会使用具有更低级别的操作。一般情况下,低级别的操作会比高级别构造的操作执行得更快。

基于成本的方法

如果使用基于成本的方法,则优化程序会根据两个方面来决定哪个执行计划最有效:基于该语句访问的方案对象 (表、簇或索引/分区) 的数据字典中的统计信息考虑信息因素和有效的访问路径。基于成本的方法也考虑语句“注释”项中的提示和优化建议。

基于成本的方法包括以下步骤:

  1. 优化程序根据可用访问路径和提示产生一组将来可能使用的执行计划。

  2. 优化程序根据数据字典中表、簇和索引的数据分配和存储特性的统计信息估计每个执行计划的成本。

  3. 这个成本是一个估计值,它与使用该执行计划执行语句所需的资源期望使用量成比例。优化程序根据估计的、使用该计划执行语句所需的计算机资源计算成本,这些资源包括 (但不仅限于) I/O、CPU 时间和内存。

需要更高成本的串行执行计划比那些较小成本的计划占用更多的执行时间。然而,当使用并行执行计划时,资源的使用并不与占用的时间直接相关。

4. 优化程序比较各个执行计划的成本,然后选择需要最小成本的计划。

注:您必须用 ANALYZE 命令来分析数据库的方案,以保证统计信息为当前信息。如果不定期分析数据库方案,优化程序就可能使用无效的统计信息,从而不能选择最有效的访问方法。

基于成本方法的目标

默认情况下,基于成本方法的目标是最大吞吐量或满足语句处理所有行所需的最小资源使用量。

Oracle 也可以选择如下标准优化语句:最小的相应时间或处理 SQL 语句访问的第一行所需的最小资源使用量。

基于成本方法的统计信息

基于成本的方法利用统计信息估计各执行计划的成本。这些统计信息对数据分配以及表、列、索引和分区的存储特性进行量化。可以使用 ANALYZE 命令生成这些统计信息。优化程序使用这些统计信息估计用某一特定执行计划执行一条 SQL 语句所需的 I/O、CPU 时间和内存量。

什么时候使用基于成本的方法

Oracle SQL Analyze 允许您对基于规则的方法和基于成本的方法都进行测试。不过在通常情况下,应该对所有新的应用程序使用基于成本的方法;而基于规则的方法适用于在进行基于成本的优化之前所编写的应用程序。基于成本的优化会更加有效,这是因为它的评价是基于数据库性能的最新信息进行的。通常,它选择的执行计划与基于规则的方法所选择的计划一样好,甚至比之更好,尤其是对于带有多个连接或多重索引的大型查询而言更是如此。

然而,由于基于成本的优化程序很大程度上依赖于对象的统计信息,因此定期使用 ANALYZE 命令刷新统计信息非常重要。

以下功能只能使用基于成本的优化:

vtc00090000.gif已分区的表

vtc00090000.gif分区视图

vtc00090000.gif按索引组织的表

vtc00090000.gif逆序关键字索引

vtc00090000.gif位图索引

vtc00090000.gif并行查询和并行 DML

vtc00090000.gif星型转换

vtc00090000.gif星型连接

基于成本的优化必须用于有效的星型查询操作。同样,它还必须与散列连接和直方图配合使用。

基于成本的优化通常与并行查询和已分区的表一起使用。