Oracle SQL性能优化全解析：理论与实践指南

需积分: 10 3 浏览量更新于2024-07-28 收藏 984KB PDF 举报

Oracle SQL性能优化原理是一篇深入探讨SQL优化策略的文章，作者徐玉金在2005年12月12日发表。文章旨在帮助读者理解SQL语句执行过程、ORACLE优化器的工作机制以及如何生成和分析执行计划。它详细讲解了以下几个关键部分： 1. 性能调整综述：强调Oracle数据库的高度可调性，并明确了调整的必要性和涉及的角色，包括应用设计人员、应用开发人员、数据库管理人员和硬件/软件管理人员。调整不仅限于特定时间，而是在设计、开发和运行过程中持续进行，以确保系统的最佳性能。 2. SQL语句处理过程：讨论了SQL语句的执行流程，从用户查询到数据库如何解析、优化并执行，了解每个步骤对于性能的影响至关重要。 3. ORACLE优化器：这是优化的关键环节，文章介绍了优化器如何根据SQL的语法和上下文选择最高效的执行方案。优化器考虑的因素包括表连接、索引、统计信息等。 4. 执行计划：讲解了如何获取SQL的执行计划，这直接影响到查询的效率。执行计划展示了数据是如何在表之间移动的，以及使用的操作和成本估计。 5. 干预执行计划：作者提到了使用SQL hints（提示）来直接告诉优化器如何执行查询，这对于解决特定问题或者在某些场景下强制特定执行路径非常有用。 6. 案例分析：通过实际案例展示如何应用所学理论进行性能优化，帮助读者理解和掌握这些概念。 7. 其他注意事项：这部分可能涵盖了SQL优化中的常见陷阱、最佳实践和其他实用建议，以避免不必要的性能瓶颈。 8. 附录：提供更深入的技术细节和支持材料，供读者进一步学习和参考。这篇文档旨在为IT专业人员提供一个全面且实用的指南，帮助他们在日常工作中有效地优化Oracle SQL性能。读者可以通过学习和实践，逐渐掌握SQL调整的技巧，提高数据库系统的整体效能。

DDL 语句的执行不同与 DML 语句和查询语句的执行，这是因为 DDL 语句执行成功后

需要对数据字典数据进行修改。对于 DDL 语句，语句的分析阶段实际上包括分析、查找数

据字典信息和执行。

事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语

句，会重新分析与执行该语句。

事务控制(Control of Transactions)

一般来说，只有使用 ORACLE 编程接口的应用设计人员才关心操作的类型，并把相关

的操作组织在一起，形成一个事务。一般来说，我门必须定义事务，这样在一个逻辑单元中

的所有工作可以同时被提交或回滚，保证了数据的一致性。一个事务应该由逻辑单元中的所

有必须部分组成，不应该多一个，也不应该少一个。

• 在事务开始和结束的这段时间内，所有被引用表中的数据都应该在一致的状态(或可以

被回溯到一致的状态)

• 事务应该只包含可以对数据进行一致更改(one consistent change to the data)的 SQL

语句

例如，在两个帐号之间的转帐(这是一个事务或逻辑工作单元)，应该包含从一个帐号中

借钱(由一个 SQL 完成)，然后将借的钱存入另一个帐号(由另一个 SQL 完成)。这 2 个操作

作为一个逻辑单元，应该同时成功或同时失败。其它不相关的操作，如向一个帐户中存钱，

不应该包含在这个转帐事务中。

在设计应用时，除了需要决定哪种类型的操作组成一个事务外，还需要决定使用

BEGIN_DISCRETE_TRANSACTIO 存储过程是否对提高小的、非分布式的事务的性能有作

用。

第 4 章 ORACLE 的优化器

优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不

要以为只有 SELECT 语句是查询。实际上，带有任何 WHERE 条件的 DML(INSERT、

UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只

是指 SELECT 语句，也有可能指 DML 语句中的查询部分。优化器是所有关系数据库引擎中

的最神秘、最富挑战性的部件之一，从性能的角度看也是最重要的部分，它性能的高低直接

关系到数据库性能的好坏。

我们知道，SQL 语句同其它语言(如 C 语言)的语句不一样，它是非过程化

(non-procedural)的语句，即当你要取数据时，不需要告诉数据库通过何种途径去取数据，

如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方

式取数据(即全表扫描)，这是由数据库的优化器决定的，这就是非过程化的含义，也就是说，

如何取数据是由优化器决定，而不是应用开发者通过编程决定。在处理 SQL 的 SELECT、

UPDATE、INSERT 或 DELETE 语句时，Oracle 必须访问语句所涉及的数据，Oracle 的优

化器部分用来决定访问数据的有效路径，使得语句执行所需的 I/O 和处理时间最小。

为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数

据生成一个执行计划(execution plan)。典型的，对于同一个查询，可能有几个执行计划都

符合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这

取决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优

化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0 次数、CPU 等)，这些资

源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划

的代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正

执行该查询的执行计划，并抛弃其它的执行计划。

在 ORACLE 的发展过程中，一共开发过 2 种类型的优化器：基于规则的优化器和基于

代价的优化器。这 2 种优化器的不同之处关键在于：取得代价的方法与衡量代价的大小不

同。现对每种优化器做一下简单的介绍：

基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称 RBO)：

在 ORACLE7 之前，主要是使用基于规则的优化器。ORACLE 在基于规则的优化器中

采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查

询的 where 条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”,

“>”, ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而

不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库

中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不

考虑实例参数，如 multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优

化的计划作为真正的执行计划，导致系统性能不高。

如，对于

select * from emp where deptno = 10;

这个查询来说，如果是使用基于规则的优化器，而且 deptno 列上有有效的索引，则

会通过 deptno 列上的索引来访问 emp 表。在绝大多数情况下，这是比较高效的，但是在一

些特殊情况下，使用索引访问也有比较低效的时候，现举例说明：

1) emp 表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引

访问 emp 表反而要好。因为表比较小，极有可能数据全在内存中，所以此时做全表扫描是

最快的。而如果使用索引扫描，需要先从索引中找到符合条件记录的 rowid，然后再一一根

据这些 rowid 从 emp 中将数据取出来，在这种条件下，效率就会比全表扫描的效率要差一

些。

2) emp 表比较大时，而且 deptno = 10 条件能查询出表中大部分的数据如(50%)。如

该表共有 4000 万行数据，共放在有 500000 个数据块中，每个数据块为 8k，则该表共有约

4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通

过索引查询，则是你梦魇的开始。db_file_multiblock_read_count 参数的值 200。如果采用

全表扫描，则需要 500000/db_file_multiblock_read_count=500000/200=2500 次 I/O。但是

如果采用索引扫描，假设 deptno 列上的索引都已经 cache 到内存中，所以可以将访问索引

的开销忽略不计。因为要读出 4000 万 x 50% = 2000 万数据，假设在读这 2000 万数据时，

有 99.9%的命中率，则还是需要 20000 次 I/O,比上面的全表扫描需要的 2500 次多多了，所

以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固

定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。

上面是枯燥的假设数据，现在以具体的实例给予验证：

环境: oracle 817 + linux + 阵列柜，表 SWD_BILLDETAIL 有 3200 多万数据；

表的 id 列、cn 列上都有索引

经查看执行计划，发现执行 select count(id) from SWD_BILLDETAIL;使用全表扫描，

执行完用了大约 1.50 分钟(4 次执行取平均，每次分别为 1.45 1.51 2.00 1.46)。而执行 select

count(id) from SWD_BILLDETAIL where cn <'6';却用了 2 个小时还没有执行完，经分析该

语句使用了 cn 列上的索引，然后利用查询出的 rowid 再从表中查询数据。我为什么不使用

select count(cn) from SWD_BILLDETAIL where cn <'6';呢？后面在分析执行路径的索引扫

描时时会给出说明。

下面就是基于规则的优化器使用的执行路径与各个路径对应的等级：

RBO Path 1: Single Row by Rowid(等级最高)

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan(等级最低)

上面的执行路径中，RBO 认为越往下执行的代价越大，即等级越低。在 RBO 生成执

行计划时，如果它发现有等级高的执行路径可用，则肯定会使用等级高的路径，而不管任何

其它影响性能的元素，即 RBO 通过上面的路径的等级决定执行路径的代价，执行路径的等

级越高，则使用该执行路径的代价越小。如上面 2 个例子所述，如果使用 RBO，则肯定使

剩余68页未读，继续阅读

gyj10938

粉丝: 0
资源: 4

Oracle SQL性能优化全解析：理论与实践指南

ORACLESQL性能优化.pptx

《oracle SQL性能优化》中文电子书

ORACLE-SQL性能优化大全.pdf

oracle sql 性能优化

OracleSQL性能优化

ORACLE SQL性能优化

Oracle Sql 性能优化

Oracle sql 性能优化调整

OracleSQL性能优化技巧

ORACLE SQL性能优化：共享原理与实践

最新资源