某单位Oracle数据库性能优化方案参考-EW帮帮网

XX市XX单位中心数据库负责存储全市的XX检测及相关信息，采用两台数据库服务器运行RAC架构的Oracle数据库软件，数据库版本号为9.2.0.8，操作系统环境为IBM AIX Unix系统，系统版本号为5.3。根据中心数据库管理员口述，截止到目前数据库的表空间大约为100GB。目前有XX家检测点通过联通2MB的DDN线路连接到中心数据库。在每家检测点中，均使用基于Oracle数据库的MIS系统。在每日工作结束后，每家检测点将当日变化的数据采用C/S架构的数据上报模块同步更新到中心数据库。

虽然本次系统优化的范围仅限于中心数据库，检测点数据库系统优化不在此范围内，但考虑到该单位将要根据建设规范，重新开发新的XX检测信息管理系统（下简称新系统），检测点在中心新系统上线后，将陆续切换到新版本的MIS系统，因此本方案的数据库物理设计规划的实施要点同样适用于各个检测点的数据库系统建设实施工作。

通过与数据库管理员的交流，我们了解到本信息系统业务逻辑与流程复杂。结合以前对不同业务系统进行数据库优化的经验，本系统优化工作不可能在短时间内面面俱到、一蹴而就。优化工作要在系统稳定性、可靠性和高效性方面取得平衡，将是一个相对长期的循序渐进的调优过程。

二、常用优化策略

信息系统优化以及效率的提高往往遵循以下规律：随着系统建设工作的开展，优化及性能调整工作的开销将会越来越大。即如下图所示：

因此强烈建议用户在新系统的需求整理及设计与规划阶段进行性能方面的考虑。下面是对基于ORACLE应用的优化的推荐方法，它分为10个步骤。按照投资回报减少的顺序给出优化过程步骤，对性能影响最大就越靠前：

如上图所示，在中心已有信息系统的基础上进行系统优化，只能从分配内存或优化I/O方面加以入手。但考虑中心将建设新系统，因此对标准优化流程在本文中也要进行一下说明：

1、优化基础设施配置

为获得最佳的系统性能，用户有时需要考虑调整有关基础设施资源配置问题。比如所申请的线路带宽；所购买的网络设备交换能力等。即如果在实际使用时已经发现超出资源的负载能力，则该单位中心的领导就应该追加另外的硬件资源，如采用多层、分布式配置方案等。当然本系统已经采用RAC数据库结构，结合其他一些原因，本次方案的调整主要是考虑网络带宽等因素。

2、优化数据设计

数据库设计阶段通常要经历规范化阶段，此时需要对数据进行分析，以降低无效数据冗余（一些“以空间换时间”的有益冗余不在此范畴内）。除了主键外，任何数据元素都应当在数据库中只能出现一次。有时又需打破这种规范形式，用户还需要保证数据库通过汇总值经常性地进行记忆。例如，在每次进行访问的时候，不应当强迫应用程序重新计算历史同期检验的统计信息。另外，为了更快地访问信息，用户应当建立主关键字和外部关键字索引。

数据设计阶段的另一个考虑是避免数据争用。也就是说，把对数据的访问进行定位，以将任何请求特定数据范围的进程可以局限到特定的物理范围。例如每个检测点仅能访问自己的数据，可以考虑针对检测点编号建立索引或根据产生的年份月份进行数据分区。另一方面，对于高频访问的热数据区，在硬件资源配置上要给与一定的I/O分流考虑。

3、优化应用程序设计

对于某些业务逻辑处理的设计，可以考虑使用缓存数据技术。例如对于字典表等数据项目可以考虑在程序加载时即存储在客户端内存中，这样用户只在每天登录时加载代码表的数据，不必要在每次查询或者录入时访问中心数据库或本地数据库，减少了系统的I/O开销和网络开销，对于权限信息也是如此。

4、优化数据库结构

在设计完应用系统应用程序之后，就需要对数据库的逻辑结构进行规划，这一步主要是对索引的设计进行调整，以保证数据被正确索引。

5、优化数据库操作

优化ORACLE服务器之前，应当确保用户应用程序充分利用了SQL语言的特性，以及Oracle为增强应用程序处理能力的相关特性。例如减少SQL语句中的IN和NOT IN操作，考虑使用exists和minus。根据用户应用程序的要求，可以运用下述特性技术：数组处理、ORACLE 优化程序、行级锁管理器、PL/SQL动态SQL语句等。

6、优化访问路径

为了确保数据库访问的效率，需要考虑使用簇、哈希簇、B*树索引、BITMAP索引、以及优化程序提示。例如常见的办法是对于某字段变化值极少（非0即1）的情况可以考虑建议BITMAP索引。

注：有效访问可能意味着增加索引，或增加特定应用程序的索引，随后再其撤消，因为也有可能的是：较低利用率的特定索引的增加导致系统效率低下。

7、优化内存分配

在ORACLE中系统共享内存被动态地分配如下结构：

数据字典缓存

库缓存

上下文区域（如果运行多线程服务器）

用户可以设置下面内存结构：

缓冲区缓存

日志缓冲区

序列缓冲区

内存资源的适当分配可以提高缓存的性能，降低SQL语句的解析，同时可以减少分页（Paging）和交换(Swapping)。

8、优化I/O和物理结构

磁盘I/O 操作会降低软件应用程序的性能。优化I/O涉及到：

调度数据，以使I/O分配时避免磁盘争用问题

最佳访问方式是将数据存储在数据块中：将自由列表设定为合适的大小，以及恰当的PCTFREE和PCTUSED

为用户创建足够大的盘区，以避免表的动态扩展，它的负面影响到高容量OLTP应用程序的性能。

评测原设备（raw device）的使用情况。

9、降低资源争用

对于多个ORACLE 并发请求，会产生对ORACLE资源的争应。应避免下面的争用发生：

块争用

共享池争用

锁争用

10、优化操作系统

UNIX 缓冲区的大小

内存使用及进程的大小

综上所述，Oracle优化流程是一个循环进行的过程，只有不断地优化、监测、再优化、再监测才能使系统性能趋于最优。

三、结合现状的优化建议

1、数据库存储空间估算

根据数据库管理员口述，目前每日数据增长量为100MB，按照每个月23个工作日，全年276个工作日计算，每年产生数据量为100×276=27600MB。根据行业经验，信息系统建设规划要考虑每年120%的数据增长量，并且新系统预计生命周期预计为五年，因此预计中心数据库中新增数据存储开销约为27600×5×(1.25)=343388.16MB，约合344GB。同样根据行业经验，数据库索引开销约为数据开销的50%，因此预计中心数据库中新增数据索引存储开销约为344GB×50%=172GB；由于用户数据库备份环境的要求，Oracle工作于数据库归档模式。用户计划每个月作一次数据库完整备份，归档日志预计仅保留一个月，按照保留一个半月计算：45×100MB×2，约合9GB；因此预计数据库在未来生命周期内的总存储开销约为344GB +172GB+9GB，约合525GB；目前数据库已有存储开销为100GB，因此未来新系统生命周期内总的数据库存储开销预计约为625GB。注：这里计算的结果没有考虑Oracle数据存储时受PCTFREE参数影响、系统运行产生的存储碎片、管理开销和新系统数据结构的变化等因素，因此实际情况可能有所增长，尤其新系统数据结构变化和新系统设置的PCTFREE参数设置将会给存储估算工作带来一定影响，因此建议中心新系统上线，全部检测点切换新系统并稳定运行后，根据新监测的每日数据库增长量重新计算总的存储开销，并进行适当扩容。

2、数据库物理存储规划

在新系统上线之前，强烈建议重新安装Oracle及其RAC架构环境，对操作系统的相关参数也需要进行调整（详见操作系统优化建议），其中Oracle比较敏感的是交换区大小的设置。交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。一般交换区大小的要求是该服务器内存的2倍至4倍之间。过小的交换区可能导致Oracle系统安装的失败，所以建议交换区最好是内存的4倍。

表的存储与索引的存储要分离到不同的表空间，这样做的目的是减少Oracle在被访问时产生存储碎片，并降低磁盘的I/O访问。且索引表空间的数据文件和数据表空间的数据文件要分别部署到不同的物理磁盘，降低访问索引的查询请求与访问数据的物理写库请求对磁盘I/O的争用。在Oracle系统中控制文件和数据库系统表空间非常重要，建议将用户表空间（含数据表空间和索引表空间）使用的数据文件、Oracle控制文件及系统表空间、日志表空间、归档目录分别部署到不同物理磁盘下或者至少是不同的路径下。其中控制文件除了存放数据文件信息和日志文件信息外，还存放恢复要使用的信息等。所以控制文件所在目录应该有足够的扩展空间。一般建议在该目录应该有200MB 以上。至于回滚表空间、日志表空间和临时表空间的大小要等新系统数据库逻辑设计完成后确定。综上所述建议规划：

磁盘0\swap\存储操作系统交换文件

磁盘0\xxx存储AIX操作系统文件

磁盘1\<sid>data\database存储控制文件、系统表空间等

磁盘1\<sid>\存放Oracle系统文件

磁盘1\<sid>log\存放日志表空间

磁盘2\<sid>data\datafiles\存放数据表空间的数据文件

磁盘3\<sid>data\idxfiles\存放索引表空间的数据文件

磁盘4\<sid>arch\存放归档日志

磁盘4\<sid>bak\存放数据库逻辑备份文件

磁盘4\<sid>tempfiles\存放临时表空间的数据文件

注：除非采用裸设备，否则每个数据文件的大小不应该按照实际表空间大小设置，文件太大将导致系统I/O性能下降。在AIX下，一般建议采用的数据文件大小为4GB。

数据库块大小在Oracle9i中是安装建立数据库时必须设定的，且一旦设定后不能修改，该参数设置的合理，会在相同数据缓冲区大小的前提下提高I/O吞吐性能。设置的过小会导致I/O访问的增加，过大会造成不必要的磁盘存储空间浪费。根据中心数据库管理员口述，主业务表及其子表的关联查询的各个字段宽度之和每条记录约合20KB大小，考虑到Oracle的管理开销和PCTFREE及PCTUSED参数等，建议在建立数据库时将块大小设置为40960Bytes。

其它数据库物理规划需要考虑的方面还有新系统数据库字符集的规划应该按照现有系统进行设置，不宜进行修改，否则将会给数据升迁与新旧系统并轨运行带来不便。在数据库完成安装以后，数据库管理员要建立分别对应中心与各个检测点的数据库系统的维护手册，并修改系统用户和应用系统用户的帐号密码及相应初始化参数，并将修改结果在维护手册中加以说明。

3、索引管理与SQL语句调优

无论用户在编写新的SQL 语句、新系统开发时使用的SQL语句，或是对现有车检系统应用程序中存在的疑问的语句进行优化，对数据库操作的优化本质上都是关心CPU,磁盘I/O等资源情况。

3.1 查找最消耗资源的语句

利用诸如 TKPROF、SQL TRACE、SQL Analyze、Oracle trace 和Enterprise Manager Tuning Pack 等工具。可以查出存在问题的语句和存储过程。此外，用户还可以通过V$SORT_USAGE视图来查看与临时段关联的会话和SQL语句。

在优化工作中，最有可能提高性能的语句包括：

整体消耗资源最多的语句

执行频率高的语句

每行消耗资源最多的语句

在V$SQLAREA视图中，用户可以发现仍然驻留在缓存的语句，这些语句进行了大量的磁盘I/O和缓存获取操作。

3.2 语句优化

应用程序的设计情况是性能好坏的基础。对于低效的应用程序设计方案，不能通过SQL语句的优化来弥补它的不足。如果用户遭遇到SQL 语句的优化问题，那么也许就需要改应用程序设计方案。下面方法可以减少特定语句所消耗的资源：

优化语句使用更少的资源

降低使用语句的频率

由于语句执行大量的事务处理，或者其工作效率低下，或者两者兼而有之，就可能消耗大量的资源。用户可以不改程序，而是更改索引结构；或只需改变SQL 语句自身的编写逻辑，进而改变其执行计划（不改环境逻辑），就可以完成任务。

一般我们对既有应用系统采用的优化方式是根据用户描述按照耗时由大到小罗列出性能低下的操作。然后逐一进行分析SQL语句，建立不同的优化方案：修改SQL语句使用既有索引；规划建立新的索引；使用不同的HITS对SQL语句使用不同的优化策略等。

4、初始化参数调整

4.1 确定合适的pctfree

由于pctfree值控制了一个数据库块中存储记录的数量，所以该值设置是否合适是至关重要，要了解pctfree设置是否合适，可用：analyze table table_name compute statistics；命令来对表进行分析，然后查询user_tables数据字典中的记录：

select  Num_Rows ,   /*number  of  rows*/

blocks  , /*mumber  of  block*/

Num_Rows/blocks   /*  number  of  blocks  per  block  */

From  user_tables

Where  table_name= UPPER('table_name');

由Num_Rows/Blocks可以知道每一个块中行的数量；模仿用户的实际应用，用update更新表中的记录；用analyze对该表再次分析，查询块中行的数量；如果块的行数量没有变化，一些行没有被移到新的块中去，则说明pctfree值是合适的。如果Avg_Space值在updade 后变大，则说明pctfree值还可以减少一些。

4.2 设置SGA相关参数

通常我们根据经验，对SGA的调整采用如下公式：

系统总内存×70%>操作系统使用内存+SGA+并发执行进程数*(sort_area_size+hash_ara_size+2M（）) ，目前根据TOAD导出的中心数据库设置得知sort_area_size+hash_ara_size=1.5MB；并发执行进程数最大值为500（一般很少达到，这里按照200计算）：并发执行进程数*(sort_area_size+hash_ara_size+2M)约为700MB，操作系统按照500MB计算，实际可分配的SGA最大值为8GB×70%-0.5GB-0.7GB，约合4.4GB，从目前中心数据库设置来看实际值为3.6GB，因此可以继续增加SGA扩充800MB内存。

large_pool_size目前设置为80MB，而系统目前并没有启用MTS，一般40MB以内足够了。但考虑到用户对大表采用分区存储，并启用了并行查询，因此这个值可以暂时不予修改。此外java_pool_size设置为32MB也已足够，暂时不用调整。

shared_pool_size包括光标、存储过程、控制结构及其它结构。主要用于缓存已经被解析过的SQL，而使其能被重用，不再解析。目前中心数据库共享池设置为1.2GB，由于目前系统中的存储过程、函数、包等对象并不多，且长期以来系统命中率均在99%左右，因此我个人认为此值偏高，建议修改为500MB，今后新系统上线时可以考虑将此值增设为800MB。如果此参数修改后通过操作系统监控导致CPU问题或者命中率下降，则可能是由于现有系统并没有充分使用变量绑定技术，导致执行SQL时需要Oracle大量进行硬解析，对于这种情况可考虑调整为800MB。

接下来调整Data buffers，该值在Oracle9i中是通过设置db_cache_size来调整的。

Oracle建议SGA中除了large_pool_size、java_pool_size、shared_pool_size以外，剩余内存均可作为Data buffers，因此db_cache_size可以调整为：（4.4GB-0.5GB-0.12GB），约合4058744094Bytes。如果新系统规划时将照片表空间采用裸设备存储，并且单独设置该表空间使用16KB的db_block_size，其他数据表空间及索引表空间均设置4KB的db_block_size，那么可以根据应用计算每一个完整的事务操作中照片数据量占总数据量的百分比来设置db_16k_cache_size，该值占db_cache_size的百分比应小于每一个完整的事务或者查询操作中照片数据量占总数据量的百分比。

注：数据库其他参数可保持不变。

5、数据库备份及归档日志管理

这部分内容和数据库性能优化关系不大，主要是出于数据安全性考虑要在系统建设前期规划备份方式、采用哪些备份手段、采购哪些备份设备和软件；在系统运行期监控系统数据吞吐量，酌情修改备份策略；每次修改备份策略后，建立模拟环境对备份策略进行数据恢复演练，监控中心数据库恢复时间是否符合业务环境要求并制定数据恢复步骤备忘录，尽量降低数据灾难带来的影响。

为了保护用户投资，方便用户数据库管理员的维护工作，建议用户仍然采用现有IBM数据备份工具，该工具采用RMAN作为底层备份机制，这实际上和Oracle建议采用的备份手段是一致的。目前主要考虑备份策略的调整。

每次做完数据库完全备份后，该时间点的以前的归档日志即可删除，以释放存储空间。根据业务系统的忙碌程度和数据量大小，建议采用每周完全备份，每日增量备份的策略。然后在AIX操作系统级别，编写备份脚本进行全库数据的逻辑导出，进行“双保险”。

三、日常数据库维护建议

1、周期性日志清理

在后台进程（如LGWR, DBWn 等）产生的跟踪文件的路径中，警告文件也记录在该路径下。警告文件的默认名为ALERT_sid.LOG。这些文件增长比较慢，但要求用户要周期性删除这些文件。

2、周期性日志分析及知识库建立

目前中心每日查看ALERT_sid.LOG，这很有必要，可以及时发现问题。建议将其问题的部分摘录至数据中心Oracle运维手册中，并建立问题追踪机制，对于已经解决的问题记录解决方法，建立数据库问题解决知识库。

3、周期性备份恢复的模拟演练

备份策略修改后要及时建立模拟环境进行数据恢复演练，制定数据库恢复步骤和备忘录。使得数据库灾难后的恢复时间降低到最少。

4、周期性检查空间开销

建议周期性检查数据库表空间使用情况，估算本周期内每日数据库增长量，并估算在下一个检查周期到来前相应表空间的空闲空间是否满足使用要求，对于空间将要不足的表空间及时增加数据文件，并做好维护手册的记录。

5、建立应用系统每日问题维护表

建立每日问题维护表及时登记问题的发现时间、问题描述及解决办法，建立问题解决方案的知识库。并为各个检测点建立维护手册，除了记录该监测站数据库系统用户帐号密码、数据库参数、文件路径、备份策略等内容外，还要对发现的问题及时记录并跟踪解决情况。对于在各个检测点发现的普遍性问题与开发单位研讨解决办法并做好备忘录和解决办法的风险预估。

6、周期性检查关键指标

建议将周期性检查数据库命中率等值的操作周期延长，可以考虑在每次应用系统更新升级或者数据库参数发生改变后进行，根据中心业务系统情况，一个月检查一次即可。

7、其他建议

建议在数据库中安装LOGMNG包，并设置UTL_DIR参数。一旦数据发生异常改变后，可以通过当时的归档日志使用该工具进行分析，查找问题原因。

建议增加周期性表分析任务，使得Oracle基于成本的优化能有更好的性能。

analyze  table  table_name  compute  statistics；

某单位Oracle数据库性能优化方案参考

一、系统运行现状描述