数据库优化技术研究

柳培林

[摘    要] Oracle数据库是目前油田使用最为广泛的数据库。Oracle数据库性能直接影响到应用系统的运行效率，其性能受到多方面因素的影响。对Oracle数据库性能优化是一项连续复杂的系统工作。文章首先简要介绍了影响Oracle数据库性能的主要因素，接着从4个方面介绍了Oracle数据库优化调整思路和方法。

[关键词] Oracle;性能优化;SGA

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2019. 03. 056

[中图分类号] TP311    [文献标识码]  A      [文章编号]  1673 - 0194（2019）03- 0140- 02

1      问题的提出

随着油田信息化应用系统数量不断增加、数据库整合进程的不断推进，大庆油田第二采油厂目前在运行Oracle数据库110个，分布在两台Sun M4000服务器和一台虚拟服务器上。随着信息化应用的不断发展，用户数量也不断增加。但厂内数据库服务器的硬件水平尚停滞多年前水平，数据库运行压力较大。因此对数据库进行优化变得重要起来。通过对其优化，可以缓解数据库系统运行过程中性能的渐进性下降或性能突降等问题，以保证信息系统良好的运行状态。下面将针对采油厂数据库服务器分别阐述影响Oracle性能的主要因素和相应的优化手段。

2      影响数据库性能的主要因素

2.1   阶段性数据处理压力大

数据库备份、存储过程执行、数据回迁、物化视图同步等数据库事务占用服务器较大资源，会引起I/O冲突，降低整个系统的速度。

2.2   CPU占用过高

CPU是数据库服务器中一个重要的资源，CPU资源被某个应用占用或被某个数据库事务占用，会导致其他数据库事务运行停滞，导致数据库响应迟钝。低效率的SQL语句、锁冲突、SQL语句的重解析等原因都会引起CPU资源不足。

2.3   内存分配不合理

内存分配不合理将会减少Oracle用于存放最近访问过的数据的缓冲区控件，并导致操作系统频繁进行页面或内存交换，从而导致系统I/O压力大。

2.4   Oracle配置不合理

每一个Oracle实例都是由一组Oracle后台进程和系统全局区的一个内存区所组成，正确调整Oracle配置将会对系统性能产生重大的影响。

2.5   网络速度低

网络的带宽会在一定程度上影响系统的整体性能，网速过低会增加网络I/O的负载量，从而降低数据库系统的吞吐量并延长用户相应时间。

3      性能优化与调整技术

3.1   调整数据处理事务时间

针对数据库备份、存储过程执行、数据回迁、物化视图同步等占用服务器较大资源的事务合理分配时间，利用任务计划对备份、存储过程进行定时运行，物化视图可利用Alter命令对启动时间进行调整，让这些事务在夜间无人应用数据库时启动执行，不影响白天的数据库访问。

3.2   充分利用系统全局区域SGA

SGA区是由Oralce分配的共享内存结构，包含一个数据库实例共享的数据和控制信息，是Oracle数据库中进行内存管理的关键模块。SGA区由高速缓冲区、共享池、重做日志缓冲区、大型池和其他结构组成。根据Oracle官方指导文件说明，SGA管理是Oracle数据库性能优化的主要方面。

Oracle的信息存储在内存和磁盘上，由于访问内存比访问磁盘快得多，在大量并发用户下，如果Oracle内存不够会降低程序的处理效率，延缓数据库的响应时间，提高数据库性能需要设置合适的内存尺寸。

根据数据库运行状况可重新调整SGA的大小，对单节点修改SGA大小的方法如下：

SQL>alter system set sga_target=600m scope=both sid='ora815'

3.2.1   数据高速缓冲区

存储的是最近从数据文件中检索出来的数据，供所有用户共享。当用户要操作数据时，先由服务器进程将数据从磁盘的数据文件中读取到数据高速缓冲区，然后在缓冲区中进行处理。为减少系统磁盘I/O开销，可以调整数据缓冲区的尺寸，使服务器进程尽量在缓冲区中找到所需的数据，尽量减少等待数据块或空闲缓冲区的时间。

（1）加大buffer cache的大小：可以通過调整DB_CACHE_SIZ

E参数的值增大Buffer cache。

（2）使用多个缓冲池提高buffer cache 的命中率：通过修改参数_kghdsidx_count值增减子池数量。

3.2.2   重做日志缓冲区

用于缓存用户对数据库进行修改操作时生成的重做记录。重做日志缓冲区是一个循环缓冲区，从顶端向低端写入数据，然后再返回到缓冲区的顶端循环写入。重做日志缓冲区的大小对数据库性能有较大影响。较大的重做日志缓冲区，可以减少对重做日志文件写的次数，适合长时间运行的、产生大量重做记录的事务。日志缓冲区的大小 log_buffer，默认值为512 k或128*cpu_count ，取较大的一个值。通过修改log_buffer参数可设置日志缓冲区大小。

3.2.3共享池

用于缓存最近执行过的Sql语句、PL/SQL程序和数据字典信息，是对Sql语句、PL/SQL程序进行语法分析、编译、执行的区域。合適的共享池大小可使编译过的程序代码常驻内存，大大降低重复执行相同的Sql语句、PL/SQL程序的系统开销，从而可以提高数据库的性能。

通过调整参数SHARED_POOL_SIZE的值，可以根据实际情况对共享池的大小进行调整。

在共享池空间管理中，并不是简单地把共享池设置得非常“大。共享池过大或过小都会降低性能，严重情况下还会导致数据库服务器夯机。此外，在共享池管理中经常会遇到ORA-4031报错，表示在共享池中分配内存失败。此时，可以查询V￥SGASTAT视图，查看shared pool的free空间是否接近于0。查询语句是：select * from v$sgainfo where pool=′shared pool′and name=′free memory′。如果结果接近于0，表明分配给共享池的内存不足，需要增加内存。但是很多时候结果显示有很多free空间，而仍然会报ORA-4031错误。主要原因是共享池空闲空间碎片过多。有太多的碎片，当申请大空间存储块时，每个碎片都不能满足应用请求空间分配的大小。解决办法是：修改初始化参数SHARED_POOL_RESERVED_SIZE，此参数默认为5%，可以适当增大。

3.2.4   大型池

大型池是一个可选的内存配置项，主要为Oracle多线程服务器、服务器I/O进程、数据库备份与恢复操作、执行具有大量排序操作的SQL语句等需要大量缓存的操作提供内存空间。

对这些内存缓冲区的合理设置，可以大大加快数据库查询速度，一个足够大的内存区可以把绝大多数数据存储在内存中，只有那些不怎么频繁使用的数据，采用磁盘读取，这样就可以大大提高内存区的命中率。

3.3   物理结构的优化

Oracle数据库物理存储结构的优化对于提高数据库的性能也非常重要，物理结构的优化就是依靠磁盘的读写的并行度、减少存储的动态扩展来实现数据库系统的性能优化。

3.3.1   磁盘读写的并行优化

磁盘I/O是数据库访问开销的重要指标之一，数据库物理结构的优化虽然无法减少从磁盘中读写数据的次数，但是可以通过并行化的方式，减少对单节点磁盘读写的竞争，主流的用法是：（1）将系统的system、temp、rollback、rode log表空间尽量与数据表空间存放在不同的物理磁盘中，能够减少系统进程与用户进程对I/O的竞争。（2）同理，Oracle的数据文件和操作系统的其他文件也可存放在不同磁盘，这样也可减少I/O争用的概率。

3.3.2   减少存储的动态扩展

当在数据库建立表空间时，如果没有分配足够的存储表空间以及合理的增幅长度，则存储结构在运行阶段会进行动态扩展。在动态扩展的过程中，需要读取系统参数文件，实现对空闲空间的利用，在这个过程中需要消耗较长的时间，并会导致数据库运行效率突然大幅下降，因此，在建立表空间时，就要合理计算并设置存储空间大小、增幅长度、最大值参数。并要定期检查表空间使用情况，对占用率高的表空间进行管理。

3.4   SQL语句优化

SQL 语句本身的执行效率直接影响Oracle数据库执行效率，它消耗了数据库系统70%～90%的资源，对SQL语句进行合理设计可以使其更高效地执行，以提高系统对资源的利用率，好的SQL语句可以加快执行速度，减少网络传输，从而最大限度地发挥数据库的性能。

（1）尽量减少对数据库的查询次数，对几个表查询时FROM子句的顺序，按照由内及外的访问顺序应把可筛选出较少记录的表放在前面，执行时最先查找出这个表的几个记录，再和其他表的记录相连接。

（2）为了充分利用库缓冲区的SQL解析信息，对于经常运行条件子句变量值不同的SQL 语句，应将这些变量改为统一的绑定变量。

（3）调整SQL 的关键是使数据库寻找数据的路径最简化， 限制动态SQL 的使用， 优化操作符， 如in 或not in，is null 或is not null，like ，union等操作符，应尽量少用。

（4）避免不带任何where 条件的SQL 语句的执行，使用order by、group by、union 等条件的SQL 语句会对查询完的数据进行排序，增大了PGA 或TEMP 的负担，优化这些语句时可在使用这些条件的列上加上有序索引。

（5）对SQL 语句的索引进行优化，如：在索引列使用is null 和is not null，或进行了显式或隐式的运算时索引不被使用，采用函数处理的字段也不能利用索引等。

（6）避免相关子查询，查询嵌套层次越多，效率越低，为了加速查询速度，可以使用临时表。

4      结    论

Oracle数据库的优化管理是一个长期不间断的工作，随着数据库规模的扩大、用户数据量的增加，oracle数据库性能问题将会越来越突出，Oracle数据库的性能优化涉及的方面很广，优化和调整是一个需要不断摸索、总结的过程。在实践中，必须先了解影响数据库系统性能的原因，针对不同的因素选择合理的优化调整策略予以调整，同时也需要采取更加先进的技术来对数据库进行调优，以保证厂数据库安全高效稳定运行。

主要参考文献

[1]路川，胡欣杰，等. Oracle11g宝典[M].北京：电子工业出版社，2014.