Oracle数据库性能杂谈

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

内存问题

现代计算机系统的内存操作要比磁盘操作快得多,因此一般认为 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 效率低下的常见原因之 一。这里影响执行效率的主要因素有:驱动表的选择(将限制性最强的表作为驱 动表,必要时可以使用优化器提示)、多表联接的顺序(两两联接的顺序)、联接 方法与索引的配合等。高效地处理多表联接是提高复合查询效率的关键。