基于Linux平台Oracle 10g数据库性能调优

陈正举

摘 要： 笔者单位对外业务网站为宣传、承揽主营业务的重要窗口。随着业务范围的逐步扩大，网站数据库的数据量迅速增长。系统性能设计不足的问题逐步暴漏出来，如页面加载速度慢，执行数据库操作等待时间过长等。针对这一问题，笔者提出了对系统所采用的数据库的优化设计，最终实现了网页数据加载速度加快及数据库操作时间等待减少的调优目标。本文旨在结合此项实践分享基于Linux操作系统平台的Oracle数据库服务器的调优实践，进而归纳几个调整和优化基于Linux操作系统平台的Oracle数据库服务器的方法。

关键词：Linux Oracle 数据库 性能调整优化

中图分类号：TP392 文献标识码：A 文章编号：1003-9082（2016）06-0001-03

引言

笔者单位的对外业务网站为宣传、承揽主营业务的重要窗口。随着各项业务范围的逐步扩大，网站数据库的数据量迅速增长。系统性能设计不足的问题逐步暴露出来，出现了页面加载速度慢，执行数据库操作等待时间过长等问题。系统使用Oracle 10g数据库，笔者根据多年数据库管理和开发经验，以满足業务性能需求为出发点，对业务数据库进行了调整、优化。取得了预期的成效。

数据库性能优化技术主要包含以下几个方面[1]：查询优化；优化数据库内存配置；优化操作系统及网络配置。笔者将从各个不同角度考虑、分析数据库问题及解决之道。

本文将结合此项工作实践分享构建在Linux操作系统之上的Oracle 10g数据库的调优心得，总结调整、优化基于Linux操作系统平台的Oracle数据库服务器的几种方法。

一、Linux操作系统层面优化

1.Linux常用的调优工具

Linux系统性能监控命令可用于监控Oracle数据库性能，进而根据这些监控数据决定数据库的调优需求。这些命令可查看进程信息、服务器CPU使用信息以及操作系统内存交换情况。概述如下：

vmstat 查看Linux的进程、虚拟内存等情况。例如：vmstat 2 5可以查看系统每隔2秒钟共5次的系统负载采样情况；

iostat 显示磁盘活动。在平衡I/O负载时很有用；

free 查看内存及交换空间使用情况。如：free -m；

top 反映CPU运行情况。用来分析进程占用CPU情况；

2.CPU对于系统的影响

系统负荷高峰期CPU的使用率接近80%是比较好的情况。使用上述top命令查看CPU使用率，找到耗用服务器CPU比较多的会话。通过查看Oracle的V$SYSSTAT视图可以得到数据库CPU时间（CPU used by this session），操作系统的用户CPU时间（OS User level CPU time），以及Linux系统CPU时间（OS System call CPU time）。

通过如上视图发现耗用CPU较大的Oracle进程（包括占用CPU用户时间比例以及持续时长），这些进程很可能反映了数据库存在性能问题。通过这些进程的id反查到数据库中相应的服务器进程及会话。查看活动SQL情况，进而定位SQL问题（如：重解析、锁冲突等）或其他可能的问题（如：数据库内存不足、连续数据块读取等）。

同时还要考虑在多个系统共用一个实例的情况下不同应用交叉使用CPU带来的影响。本例业务网站曾遇到信息发布慢的问题，检查并且分析awr报告，发现数据库DB Time略高，且有明显row lock等待。

进而比对alert告警日志，发现警告日志里近期关于ORA-60错误，每个错误下面对应的trace文件。可以看到2个用户进程发生了死锁。在里面可以看到sql_id和sql脚本，发现3ptv952k9747，c5k5cpdnvjuy9这2条sql是数据库产生死锁的sql，对应的脚本可以通过下面的路径进去看见“当前的sql”，

ORA-00060： Deadlock detected. More info in file /oracle/diag/rdbms/zsdb/zsdb/trace/zsdb_ora_19387.trc.

Wed Jul 22 17：06：51 2015

通过查看trace文件，确定是上面2条sql脚本里包含如下两条类似的sql语句，且没有及时commit，造成死锁。通过ALTER SYSTEM KILL SESSION结束相应会话，在应用里添加commit语句解决。

UPDATE SP_EXTRACTION_RECORD

SET EXTRACTION_ID = ：1，

EXTRACTION_APPLICATION_CODE = ：2，

EXPERT_ID = ：3，

EXTRACTION_COUNT = ：4，

AFFIRM_FLAG = ：5，

ABANDON_REASON = ：6，

ABANDON_REASON_DETAIL = ：7，

GROUP_NUMBER = ：8，

CHOOSE_MODE = ：9，

OCCUPATION_BEGIN_DATE = ：10，

OCCUPATION_END_DATE = ：11，

EXTRACTION_SPECIALITY_ID = ：12，

BUSINESS_DOCUMENT_NO = ：13，

ABANDON_TYPE = ：14

WHERE ID = ：15

3.对于基于虚拟化建立的数据库

笔者也从事虚拟化方面的工作，鉴于虚拟机在硬件管理、资源调配、远程运维等方面相较传统物理机的巨大便利性，笔者尝试了将数据库服务器虚拟化的相关工作。为了弥补虚拟化磁盘I/O性能的下降，虚拟化数据库服务器应选择使用SAS盘组成的磁盘阵列。同时，通过一些措施尽量减少磁盘I/O，如：灵活增加虚拟服务器内存大小；给虚拟服务器分配更多的预留内存空间等。将数据库服务器虚拟化带来的另一大优势就是可用性的增加。传统的ORACLE数据库高可用通过物理服务器RAC集群来实现，以避免单节点硬件故障导致数据库不可用。在虚拟化集群中，借助虚拟软件的HA功能和虚机漂移技术，可实现对单节点数据库虚拟服务器的硬件保护，从而规避ORACLE RAC管理的复杂性及其他技术风险。笔者单位的一些业务系统数据库就从物理双节点RAC转变为了单节点虚拟服务器，业务性能未有明显影响，管理复杂性大幅度减少，数据库监控指标也大幅减少，提升了可运维程度及可控程度。

4.磁盘分区优化设计

4.1磁盘硬件考虑

随着Oracle数据库使用规模和数据量的不断增大，如何实时高效地处理存储实时数据，以及如何快速响应用户应用请求，对数据库系统的存储和设计提出了更高的要求[5]。

硬件I/O瓶颈的性能问题是比较容易识别的。在没有RAID设备的情况下，最好采用分条（strip）形式，容许数据文件分布在所有可用的磁盘，将磁盘I/O操作平均到不同的磁盘上去[3]，利用多个磁盘提供并发访问能力。这样可非常有效地减少磁盘I/O的等待时间。

如果具备RAID设备，应尽量考虑采用RAID0+1或RAID5的方式组织磁盘。优选RAID 0+1，且根据笔者的工作经验，8块SAS盘的RAID0+1是实现数据库应用比较理想的配置。且在存储设备支持的情况下，应对多组RAID0+1磁盘组的再进行条带化。这样既保证了数据的安全可用有最大程度的提升了磁盘存储的I/O能力。这种磁盘组的再条带化功能已为大多数主流存储产品所支持。实际就是LUN（逻辑存储单元）的捆绑扩容功能，只不过要选择以条带化方式进行扩容。RAID 5对于有大量读操作的应用程序可以获得最大的性能，这是一种低成本的解决方案[4]，适用于性价比要求较高的中小型应用。

4.2使用IOSTAT查找大的分区请求事务

ostat -t命令可以查看磁盘、分区的I/O数，TPS指标说明了单位时间内特定磁盘或分区设备接收请求的事务量。正常情况下，磁盘正常的TPS应不大于200[2]，如果这个值高于推荐值，说明磁盘的读写太频繁，应及时检查相应设备上Oracle数据库文件是否存在热点。进而对使用相关数据库文件的SQL查询进行比较分析，找到设备被频繁读写的真正原因。对SQL语句进行优化，减少数据库I/O等待时间。

笔者在对文中业务网站系统数据库优化过程中就曾借助iostat方法快速对问题进行了定位及解决。通过对巡检过程中TPS超过200的分区进行检索，发现某业务表空间的全部数据文件都位于一个分区内，且这一表空间内的大表较多。联想到业务响应较之前变慢，初步定位问题。通过调整表空间内数据文件的分布，优化大表检索SQL等方法成功降低了相应设备的TPS，其值从之前的251下降到57，实际上也大大加快了业务响应时间。

二、数据库性能制约因素及调整

1.调整数据库缓冲区高速缓存大小

Oracle数据块在被从内存写到磁盘前，都是保存在Oracle缓冲区高速缓存中的。Oracle 10g后，Oracle支持自动共享内存管理（ASMM），允许Oracle根据数据库实际运行情况自动调整SGA中的各个组件大小。要启用ASMM，必须设定sga_target参数，该参数指定了所有共享内存预期的总大小。足够的内存分配给Oracle系统缓冲区就显得很重要，sga_target参数可以来规划内存给Oracle的分配[5]。通常情况下，这个值应不高于服务器物理内存的70%。Oracle将根据高速缓冲区缓存顾问、共享池和其他类似的顾问的输出动态地调整各种SGA组件的大小。

ASMM在波动不大的工作负载中运行是比较好的，不适合在变动频繁的工作负载中使用，输出可能不准确。因为，ASMM基本上在持续监控各项顾问数据，并且有规律地根据顾问的建议调整内存。但是，如果工作负载变动频繁，ASMM可能会用刚调整的内存来适应刚出现的情况，而不是为当前总体的工作负载服务，这样就容易产生内存拉动。如果出现内存拉动，则需要禁用ASMM，因为这样可以然系统处于更好的工作状态。这时，一些特定参数的配置是必要的，如db_cache_size，shared_pool_size，db_keep_pool_size等。系统将会根据这些资源池配置大小进行工作。

2.充分利用Oracle自动调优工具

Oracle提供了丰富的数据库监控视图及手段。如，V$SESSION和V$SQLAREA用来找出正在发生的性能最差的SQL语句；ASH报告用来查看一个小时以内发生的事件；AWR報告用来查看1小时以上或几天前的事件。如果遇到数据库性能问题，建议采用Oracle数据库自带的工具ADDM来对数据库的运行状况进行分析[8]。ADDM的报告就是基于AWR库生成的，默认可以保存30天的ADDM报告。当然，数据库管理员也可根据实际需求调整这个保存期限。

本文中业务网站的数据库的性能统计分析信息是2014年2月收集的，统计分析析过旧，导致数据库优化器无法选择正确的执行计划，致使原来使用索引扫描的SQL语句使用全表扫描。

optimizer_danymic_sampling参数定义了数据库动态自动采集统计信息的间隔。及时采集数据库统计信息，有利于提高数据库自动调优的效率及准确程度[5]，可使数据库提供更准确的低成本执行计划给用户，提高数据库执行效率。因此，建议开启Oracle自动收集数据库统计分析信息功能。也可执行如下命令手动收集一次数据库统计信息：exec

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

3.SQL優化

众所周知，应用系统对数据库的访问无非就是借助SQL语句来实现。所以数据库调优工作的出发点和落脚点也都将落在SQL的实际执行效果上。因此，SQL语句的性能优劣就直接影响着整个信息系统的可用性。而SQL语句的优化其实是进行查询等价转换，主要是从查询语义的角度将用户的SQL查询语句转换为效率更高的形式SQL语句[6]。

Oracle提供了基于成本（CBO）和基于规则（RBO）两种优化器，用于确定查询操作的执行计划。实际工作中主要精力应集中在优化SQL解析和优化CBO上[4]。Oracle10g采用基于代价的优化器CBO，其作用是查看语句的代价，主要指CPU和内存消耗[8]。本文也将采用基于CBO的SQL优化。

在上述优化基础上，笔者连续几天对业务系统网站数据库TOP5的SQL活动进行了观察。对头条的SQL语句进行分析、优化。发现此语句执行效率低下，以下是该SQL语句的优化过程。语句内容如下：

select task.guid， task.moduleid， task.jobGUID， task.runlevel

from bi_schd_host host，

bi_schd_module m，

bi_schd_jobqueue job，

bi_schd_taskqueue task

where host.openstatus = 1

and host.hostid = ：1

and （（m.hosts is null） or m.hosts = '' or （m.hosts like ：2））

and （m.loadWeight <= host.loadweight - nvl（host.currloadweight， 0））

and （job.runlevel is null or task.runlevel is null or

job.runlevel = task.runlevel）

and task.runstate = 0

and task.moduleid = m.moduleClzName

and task.jobguid = job.guid

and （task.servername is null or task.servername = '' or

task.servername = ：3）

order by task.servername desc， m.priorty desc， task.runlevel

优化前的执行计划如图1所示，发现全表扫描现象。SQL语句性能不佳大致有两个原因：一是开发人员只关注查询结果的正确性，忽视查询语句的效率；二是开发人员只关注SQL语句本身的效率，对SQL语句的执行原理、影响SQL执行效率的主要因素不清楚[7]。

定位全表扫描对象，对其进行优化。在选择性高的列上创建索引及进行行的重新排序。优化后的执行计划：

优化之前，数据库执行全表扫描，整体cost 6454。优化后，数据库使用索引范围扫描，整体cost 12。数据库执行效率有明显提升，事实上数据库反应速度也明显加快。网站中涉及此表的应用模块的运行速度显著提升，且这种改善能够传递给终端用户，达到了优化的目的。

三、结论

通过此次优化工作，业务网站的访问速度得到了显著提高，相关数据库操作等待时间明显减少。达到了预期的优化效果。可见Oracle数据库的性能可伸缩性比较强，需要规律性地维护，以便充分发挥其性能优势。充分利用以上命令和方法，就可以较好地调整与优化Oracle 10g数据库，足以应对大多数业务需求。

同时，规律性地对数据库服务器进行系统性能调整优化还可极大提高数据库运行稳定性及效率，提前预防数据库性能问题，降低数据库瓶颈出现的概率，以满足用户对系统更高的性能需求。

参考文献

[1]Richard，J.Niemiec.Oacle9i性能调整[M].清华大学出版社.2004：46-67.

[2]Edward Whalen，Mitchell Schroeter.Oracle性能调整与优化[M].高艳春，周兆确，唐艳军译.北京：北京人民邮电出版社.2002，12.

[3]刘春林.Oracle数据库技术使用教程[M].北京：北京鑫智鸿鹏科技发展有限公司组编，2010-04：166.

[4]Oracle数据库性能优化分析与配置[J].电脑知识与技术.2013，11，18（9）：4146.

[5]袁佰顺，朱拥军，李晓鹤，汪鸿滨，陈 薇.基于ORACLE的陇东南区域自动气象站数据库设计及应用[J].干旱气象，2014，32 （ 3 ）：475-480.

[6]张辉，赵郁亮，徐江，孙伟华.基于Oracle数据库海量数据的查询优化研究[J].计算机技术与发展，2012，2（ 22 ）：165.

[7]Christopher Allen.Oracle PL/SQL程序设计基础教程[M].钟鸣，文卫东等译.北京机械工业出版社.2001，5：131.

[8]杨文清，吴昊，胡荣，谢浩安，孙昌爱.一个工业产品发布系统查询优化技术研究[J].中国科技论文在线，2014，11：15.http：//www.paper.edu.cn.