Oracle数据库性能优化杂谈

数据库系统出现性能问题，其根本原因是系统资源出现瓶颈，这里的资源瓶 颈包括硬件瓶颈和软件瓶颈。内存、CPU、I/O 是硬件资源，如果这些资源出现瓶 颈，则系统出现硬件瓶颈。软件瓶颈则是由于软件设计的数据处理不合理导致的 性能问题。很多情况下，硬件瓶颈是由于应用软件的不合理导致的，我们这里说 的性能调优，其目标是在硬件资源有限的情况下，通过调整应用和配置，使得应 用软件配合硬件资源协调运行。

本文地址：http://blog.sunansheng.com/2016/04/22/oracle-sql-performance-pre/

内存问题

现代计算机系统的内存操作要比磁盘操作快得多，因此一般认为 I/O 瓶颈是 计算机系统常见的性能瓶颈之一。数据库系统性能调优的目标之一是将所有的数 据处理置于内存中完成，因此一般认为增加内存能改善数据库系统的性能。但如 果认为若所有的数据处理都能在内存中完成，数据库系统就不存在性能问题，也 是不妥当的，因为数据库系统的性能问题涉及多种计算机资源的“争用（contention）” 与“耗尽（exhausted）”问题。

充足的内存资源是保障数据库系统正常运行的必要条件，而非充分条件。数据 库系统性能调优的目标是确保应用软件和数据库之间协调运行，提高性能的关键是 数据处理在内存中如何运行，如果运行在内存中的数据处理出现大量等待、如果数 据处理导致CPU利用率过高、如果数据处理在内存中大量无效地扫描数据，遭遇这 些情形一定会在某种程度上导致性能问题，此时再充足的内存也往往无济于事。

最终用户关心的是数据处理的结果，而非数据处理的过程。尽可能地提高数 据处理过程的效率，从源头上减少不必要的数据访问，是提高性能的关键，这样 既可降低对内存的需求，又可提高数据处理的性能。

CPU 利用率

宏观上，CPU 利用率是数据库系统运行状况的表现之一，现代分时多任务操 作系统对 CPU 都是分时间片使用的，如 a 进程占用 10ms，然后 b 进程占用 30ms， 然后空闲 60ms，再又是 c 进程占 20ms，d 进程占 20ms，空闲 60ms；如果在一段 时间内都是如此，那么这段时间内的 CPU 利用率就是 40%。

过高的 CPU 利用率通常会导致数据库系统性能下降，但 CPU 利用率低不见 得没有性能问题，所以并不是 CPU 利用率越低越好。CPU 利用率低只是表示 CPU 无所事事，所有的数据处理必须要借助于 CPU 才能完成。如果数据库系统出现性 能问题，而与此同时 CPU 利用率又很低，这说明数据处理的方法或效率出现问题。 要提高数据库系统的性能，一般需要有必要的 CPU 利用率做配合，否则数据库系 统不可能有足够的吞吐量（throughput）。

对于 OLTP 系统，存在大量的频繁的短时事务处理，正常情况下 CPU 利用率 不应太高；而对于 OLAP 系统，往往存在长时间的批处理操作，此时应该充分利 用 CPU 资源，高的 CPU 利用率是提高数据库系统处理效率的保障。

I/O 问题

磁盘的读/写是 I/O，通过网络的输入与输出也是 I/O，数据库系统同时存在这 两个方面的 I/O：一方面数据库是存储于磁盘介质，系统在运行过程中要读/写数 据库的物理存储；另一方面，应用系统（包括应用服务器）是通过网络访问数据 库系统，两者的交互是通过网络 I/O 完成的。这两个方面的 I/O 都有可能成为数据 库系统性能上的瓶颈。

ORACLE 数据库系统中，如果出现 I/O 瓶颈，大多数情况下是磁盘 I/O 瓶颈， 或者说磁盘 I/O 是数据库性能调整的永恒话题，因为较慢的磁盘 I/O 或多或少是数 据库系统性能问题的影响因素。如果系统出现明显的磁盘 I/O 瓶颈，在不改变磁 盘和磁盘配置的情况下，主要有两个方向：一是分散数据库的物理存储和数据访 问，以缓解磁盘 I/O 瓶颈；二是通过优化数据库系统配置和应用系统，减少磁盘I/O 的访问量。

至于网络 I/O 是数据库系统之外的话题，在服务器的范围内，网络 I/O 通常存 在于数据库服务器与应用服务器之间，或数据库服务器与 Web 服务器之间，一般 情况下，这些服务器之间都是采用高速网络联接，不大可能出现网络 I/O 瓶颈， 但也有例外。曾经发生过这样一个案例，由于网络故障导致双向网络传输的速率 明显不同，其中一个方向的传输速率过低，导致应用服务器在批量处理数据时出 现严重的性能问题，此时 ORACLE 数据库端出现大量 SQL*net more data from client 等待事件，原本仅需几分钟完成的任务延时到需要几个小时才能完成，影响 后续业务处理，用户无法接受。这在检查网络 I/O 瓶颈时需要引起注意。

引发性能瓶颈的应用问题

数据库系统的性能优化是一项复杂的系统工程。定性地说，一个高质量的数 据库应用系统涉及系统硬件、网络、存储、平台软件（操作系统、dbms、App Server）、 系统架构、应用软件等各个层面，涵盖系统需求分析、设计、开发、产品上线测 试等各个环节，其综合目标是满足用户业务的响应速度和并发吞吐量、满足系统 管理的高可用性、数据安全性、数据容灾性、可扩展性、易管理性等综合目标。

然而，当一个应用系统投入运行后，系统运行的绝大部分因素和环节都已相 对固定下来，我们需要面对的是一个在特性环境下的应用功能发挥问题，面对性 能问题，需要有下面几个基本的认识：

不要将系统性能的优化与提升全部寄托在系统硬件升级和系统层面。事实上 国内大多数 ORACLE 系统的硬件部署和系统平台软件已经和国际先进水平同步， 整个系统能否发挥应有的效能，更多地取决于应用层面。就像蹩脚的演出剧目， 华丽的舞台掩盖不了苍白的内容。

再好的硬件环境解决不了数据库应用软件上存在的问题。在数据库应用软件 开发过程中，开发人员往往注重功能问题，而不注意效能问题。系统投运后，效 能问题成为主要矛盾。面对业务数据量的扩大、并发性的增加，效能问题往往呈 现几何级数的恶变，这种情形下任何硬件的提升和扩容都很难满足效能的提升， 只能是杯水车薪，收效甚微。

当出现性能问题时，我们需要注意区分性能问题的表象和根源。ORACLE 数 据库系统出现性能问题时，往往伴随着 CPU 利用率高、内存吃紧、I/O 负荷重、 网络拥堵、大量等待事件（wait event）、latch 和缓存命中率等统计指标偏离正常 范围等，这些都仅仅是现象，究其根源大多存在于应用层面，如果应用层面的问 题解决了，这些现象随之得到缓解甚至消失，效果立竿见影。

经济领域有一个大家公认的 20/80 现象，譬如 20%的富人占有 80%的社会财 富，80%的利润往往来源于 20%的投资等，而且这种现象几乎无处不在，被称为 20/80 法则。研究数据库系统性能问题，这一规则也同样适用，主要表现在如下两 个方面：80%的性能问题由 20%的应用导致；20%的优化技术能够解决 80%的性 能问题等。

导致 ORACLE 数据性能瓶颈的应用 层问题主要来自于如下四个方面：

（1）查询的选择性（selectivity of query）：全表扫描与索引访问。避免对大表 的全表扫描是防止 SQL 语句执行效率低下的主要措施之一，不仅包括查询，也包 括 DML（如 update、merge 等）。很多原因导致 ORACLE 会执行全表扫描，如缺 乏索引或索引失效、索引被抑制、不正确的索引类型与结构、不合理的多表联接 顺序与联接方法、索引与联接的配合问题、不当的子查询（sub-query）、事务设计 与事务逻辑问题、优化器问题等。高效的数据选择性和有效的索引访问是提高 SQL 语句执行效率的关键。

（2）游标的共享性（sharing of cursor）：解析（hard parse/soft parse）与绑定变 量（bind variable）。在 OLTP 系统中，存在大量即时而频繁的查询和 DML 操作，如果用户游标没有很好的共享性，会导致频繁的 SQL 硬解析，消耗大量的共享内 存（shared pool）和 CPU 资源，这种情形与系统性能和可扩展性紧密相关。如果 系统存在大量 SQL 语句的重复解析，随着用户访问量的增加和应用规模的扩大， 应用性能会急剧下降，特别是 CPU 资源将很快枯竭。避免 SQL 频繁硬解析的主 要手段是使用绑定变量。

（3）数据的排序性（sorting of rows）：排序操作是一项典型的消耗计算机资源 的操作，特别需要足够的内存空间。排序算法的复杂度介于 O(n)到 O(n2 )之间， ORACLE 对记录的排序也不例外，特别是应尽可能减少对数据的排序，特别是要 避免对于大数据量的排序、基于磁盘的排序，另外还要注意一些隐性的排序操作， 如 distinct、union、group by、merge join 等。

大多数情况下，union 可以考虑改为 union all；ORACLE 10g 版本之前的 group by结果是自动排序，从 ORACLE 10g 开始已经取消了。当排序变得不可避免时， 要提高排序性能，一是要保障足够的 pga 内存空间，二是可以充分利用索引对索 引键值的排序性。

（4）多表的联接性（join of tables）：有效地处理多表之间的联接关系是现代 dbms 的精髓之一。ORACLE 提供多种处理多表联接的方法，不同的方法都有其适 用范围，不适当地使用表与表之间的联接方法是导致 SQL 效率低下的常见原因之 一。这里影响执行效率的主要因素有：驱动表的选择（将限制性最强的表作为驱 动表，必要时可以使用优化器提示）、多表联接的顺序（两两联接的顺序）、联接 方法与索引的配合等。高效地处理多表联接是提高复合查询效率的关键。